Clone SQL Database
Posted on 2006-11-06
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.
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,