Creating a Mirror Image of Database

[RohanP]

For handling huge data every year, we r creating diffrerent databases for each year (DB0203) on New Financial Year which will reside on the same server.

I wanted to know abt Sql Syntax or some other method which i could handle from VB, which will Copy the Table structres & Relationships of DB0102 to DB0203. I want to copy just the structure of tables & relationships & not the data in it?

I am using Sql Servr 7


__________________
______________________________
- Regards Rohan (India,Mumbai)

 

[rsai]

You can generate the script for creating the database and all the objects in it via Enterprise Manager (highlight database, right click, all tasks, generate sql script). This will offer you a dialogue box in which you can select the items you want to script, click the "show all" button and either select script all objects or the bits you want to do.
Click OK and enter a file name to save it to.
Create a new database "db0203", this will need to have the same set of Data/log files as the original else you will need to adapt the sql script to create the new database tables on the file structure.
Open the sql file in query analyser( connected to the new database), and execute. This will create all the tables etc as per the old database.

 

[Sabnac]

Sometimes i got errors using this autogenerated script.
It was about views on views, the second's script was after the first so i had an unkwnown object, but nothing difficult to solve.
Just dont panic on errors

 

[tlbroadbent]

If you want to code the generation of the database in VB, you may want to consider using SQLDMO. SQLDMO is documented in SQL BOL. faq183-1637 explains "How can I script my SQL Server databases from Visual Basic?" The VB code in the FAQ utilizes SQLDMO.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[RohanP]

Hello tlbroadbent,
SQLDMO will help but the multiple files its creating with ext. i.e. DP1,2, DR1,2, ide,rul

i am pretty confuse abt it... cause my actual need is to create a New DB & Objects in it. how should I go abt it from VB using these diff created files. ______________________________
- Regards Rohan (India,Mumbai)

 

[tlbroadbent]

Rohan,

The reference to the FAQ was only given as an example. SQLDMO is documented in SQL BOL. Look in SQL BOL for the topic "Transfer Method." The Transfer method copies database schema and/or data from one Microsoft SQL Server 2000 database to another.

http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_m_t_2un8.asp

You can also load the SQL-DMO samples from the SQL Server installation CD. There are several examples including a "Create Database" program in VB. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.