Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Clone SQL Database

Posted on 2006-11-06
8
Medium Priority
?
3,561 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 1200 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 800 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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
 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

916 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