Copying a database 2

[StormbringerX]

Hi everyone.

I'm looking for some input as to the best way to create a copy of an entire database. Basically, I need to take an entire database and copy it to a NEW name, while retaining all permissions, stored procedures, etc...

The reason for this is the user needs to be able to perform a lot of statistical analysis on the data that is contained in the original database. And the results of these computations need to be refelected in the database so that OTHER analysis can be preformed. We don't want to modify the original database as that is our 'control' data.

I have tried to use the Backup DATABASE and RESTORE DATABASE, however I run into the problem that RESTOREing it to another name causes problems.

I need to be able to run this in a stored procedure that will be called from a VB program.

I hope this is enough information. Thanks for your help.

Dave

 

[Catadmin]

You can detach the DB, copy the .mdf and .ldf files, reattach the DB and, after moving your copied files to another machine/share, attach them to a new instance or machine.

Or you can do a DTS job that does a complete Export of ALL tables in the database (see DTS Import / Export Wizard). You might want to script all the database objects first (right click the db and go to All Tasks - Generate SQL Script) and run it via Query Analyzer in your newly created DB THEN do your DTS export.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[StormbringerX]

Thanks so much for the advice! I'll let you know how it turns out....

Dave

 

[gmmastros]

I just got this working for one of my databases. It's not in a stored procedure, but it is being called from VB.

The first thing you need to do is to add a 'dump device'

sp_addumpdevice 'disk', 'Mastros_Backup', 'C:\PathToBackupFolder\Mastros_Backup.dat'

Then backup the database.

Backup Database [Mastros] to [Mastros_Backup]

The, restore the database. Here's the tricky part. You need to know the logical name for the 2 files (the data and the log). You also need to know the location of the MDF and LDF that will be created.

Restore Database NewDatabaseName 
From Mastros_Backup 
With Move 'Mastros_Data' To 'C:\Folder\NewFileName.mdf',
Move 'Mastros_Log' To 'C:\Folder\NewFileName_log.ldf'

In VB, i create the ADO connection object. Set the CommandTimeout = 0, and then ran the 3 SQL strings using the connection objects execute method. I think this method is slightly better than catadmin's method (no offense) because... it is likely to be faster, and you don't need to worry about scripting the database.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

Glad we could help. Let us know if you have any more issues with this.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[StormbringerX]

Gmmastros that looks like what I was looking for. Thanks so much! A star...