Clone SQL Database

HISTORY:

We have a database with about 30 tables on a SQL 2000 server that needs to be cloned, then modified on-demand. The way we do this now is to export the entire database to an Access file, manually make the needed changes to the file, then publish it for use. The reason we do this is so that we can have a "what if" database. Using the production data, several scenarios are run that modify the data. Obviously, we cannot run those scenarios on a production database, and that's why we copy and convert to Access first. As you can imagine, this is a painful process that takes upward of 30 minutes.

QUESTION:

Can we duplicate / clone our production database to a new name on the same server and instance of SQL? I will try to give as much information here as possible so that I can get a straight answer. First, as I mentioned above, we have one SQL 2000 server with only one database to duplicate / clone. The requirement of this "clone" is that it MUST be an EXACT duplicate. And by exact, I mean it needs to clone not only the data, but the stored procedures, the triggers, the indexes, and the constrains. In other words, the application that uses this database shouldn't even be aware that it was cloned, except for the database new name, of course.

The reason, I keep reiterating the word exact is because I have seen a similar question posted on forums where the response is INSERT INTO <clone> * FROM <database>. The obvious reason this fails in my requirement is that it doesn't copy anything but the data, which is useless because the application that uses the database won't even let you log into it unless the triggers, procedures, constraints, and indexes match the script that was used to create the database.

The other requirement is that it must be scriptable. In other words, I will not use a 3rd party application, and I will not use Enterprise Manager to do this. Because the clone process will be on-demand, it can't involve giving the user who demands it administrative access to the database. So, if need be, I will write a C# application that does it all at the click of a button, I just need to know what SQL code (not C# code) to put in that button to clone the database.

Sorry this question is long and drawn out, but hopefully all this has given you a good idea of what my needs are. Thank you in advance,

Ross
LVL 1
BNLINDAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
muzzy2003Connect With a Mentor Commented:
You could backup and restore a copy:

backup database original to disk = 'filenameandpath' with init
restore database clone from disk = 'filenameandpath' with replace, move 'original_data' to 'newdatafilepath', move 'original_log' to 'newlogfilepath'

Obviously you need to replace relevant parts of this with what you need. To see the names of the data and log files, and their current locations, run this:

select * from sysfiles

Let me know if you need any more.
0
 
NightmanConnect With a Mentor CTOCommented:
Backup Mydatabase to disk="C:\MyDatabase.bak"                  

RESTORE DATABASE MyCloneFROM DISK = 'C:\MyDatabase.bak' WITH REPLACE , MOVE MyDatabase_data to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyClone.mdf', MOVE MyDatabase_log to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyClone.ldf'


With REPLACE will overwrite the old MyClone database (assuming that there are no active connections to the database).
You need to use the 'WITH MOVE' to ensure different physical files on disk (and therefore not conflict with your existing one)

MyDatabase_data is the logical name for the data file
MyDatabase_log is the logical name for the log file.

To determine your logical names (I would imagine that you would only have to check this once.):

RESTORE FILELISTONLY FROM DISK = 'C:\MyDatabase.bak'
This will list the logical filenames from the database that has been backed up along with the physical path names of the files on the disk for the server that has been backed up.
Sample output:
logical file name             physical file name
MyDatabase_data         C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabase.mdf
MyDatabase_log             C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabase.ldf
0
 
MacNuttinCommented:
detach the database
copy the mdf and ldf
paste them into a different folder example DATA2
re-attach the database
then in the new instance of SQL -attach database ...browse to DATA2 and select the clone
Choose Save as NewDatabaseName and choose the same user as owner as for the original.


also you can go to Databases --> all Tasks..--> Copy Database Wizard...

some use this for migration or replication
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
muzzy2003Commented:
Problem with detach and attach is that it brings the database down. He said it's a production database.
0
 
MacNuttinCommented:
>painful process that takes upward of 30 minutes.

MS Access maybe making this take so long. Your clone will perform better
but it will be on the same server so your should throttle it so it doesn't compete with production server for memory resourses
0
 
BNLINDAuthor Commented:
MacNuttin: Yes that would work, except, as muzzy2003 said, it's a production database and cannot be taken offline...ever. This method would also not work because it is not scriptable via SQL. You would have to physically be at the machine to do it. And yes, Access sucks :)

muzzy2003 and Nightman: Thanks for that info I have already been able to backup and verify the database using the method you gave me, but have not tried the restore yet. That is today's project. So far, so good, and I will be sure to post my results when I get them. Thanks again.
0
 
muzzy2003Commented:
Good luck. Shout if you need a hand.
0
 
BNLINDAuthor Commented:
Thanks guys for you help. It works perfectly now, and though it doesn't run as fast as I'd hoped, I was able to verify the cloned database structure with the application's included program, which passes all tests :) So again, thanks for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.