Solved

Clone SQL Database

Posted on 2006-11-06
8
3,542 Views
Last Modified: 2012-10-27
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
0
Comment
Question by:BNLIND
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 16

Accepted Solution

by:
muzzy2003 earned 300 total points
ID: 17883997
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
 
LVL 29

Assisted Solution

by:Nightman
Nightman earned 200 total points
ID: 17884046
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
 
LVL 11

Expert Comment

by:MacNuttin
ID: 17884786
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
 
LVL 16

Expert Comment

by:muzzy2003
ID: 17884812
Problem with detach and attach is that it brings the database down. He said it's a production database.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 11

Expert Comment

by:MacNuttin
ID: 17884827
>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
 
LVL 1

Author Comment

by:BNLIND
ID: 17897647
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
 
LVL 16

Expert Comment

by:muzzy2003
ID: 17897683
Good luck. Shout if you need a hand.
0
 
LVL 1

Author Comment

by:BNLIND
ID: 17901684
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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now