Solved

Clone SQL Database

Posted on 2006-11-06
8
3,543 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

867 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

15 Experts available now in Live!

Get 1:1 Help Now