Clone DB

Helloo

Can I backup DB programatically, and then restore it with different name also programatically?

meaning:
I have db2006, can I backup this db then restore it as db2007 programatically?using a stored procedure for example?

So by doing this the user will be using db2007 @. the begininng of the year and so on each year

I'm using sql2000

Please I need your help
Thank you

hi,

you can use sp_attach and sp_detach.

notice you can attach it with different db_name

Examples

EXEC sp_detach_db 'pubs', 'true'

copy the files and attach it with different dbname

EXEC sp_attach_db @.dbname = N'pubs1', 
 @.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', 
 @.filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

|||

Thank you for your reply

but I had explained in a wrong way

I need to create a dublicate for this database programatically
meaning if i have db2004, I need to create db2005 programatically same as db2004(tables and procedures, only structure)

Is that possible?

thank you

|||

you can right click the database in the Enterprise manager or management studio

and clcik on task>generate scripts

|||

Look into the smo objects in books online. You could probably build a program to do this pretty easily to script out the database, create a clone database, and apply the script.

If this is a production database, if you have maintain scripts of the database, that is probably the best way to go, just by applying the scripts (but that takes a good amount of discipline on all developers, which can be hard to maintain depending on corporate culture)