Is there a way to copy a database using TSQL?
When we set up a new customer we would like to make a copy of the existing database and set up a new database for them. Is there a way to programmatically do this?
There are different ways you could approach this depending on your needs. If one day of data latency isn't an issue, you could just restore a nightly backup of the database in question as a new database name. You could setup a stored procedure to receive variables for database name, physical file name, and any other specific options. Then parse and execute your restore statement.
http://msdn2.microsoft.com/en-us/library/ms186858(d=ide).aspx
|||I was looking for a more direct approach. I know there is the Copy Database wizard. Are there commands that I can use that use the SMO version of that wizard?|||You can use the "VS2003 setup project" to deploy the database to the client. VS 2005 has better support for these kind of scenario.|||One easy way to set it up (if you have two machines runing SQL Server) is to use the Copy Database Wizard and save either the DTS in the database or save to a script. Then I think you might be able to tweak the script to modify the target database name and filenames.
I haven't done this before so no guarantees. Once you had it setup though as a DTS, you could use Global variables to pass in the databasename and and filenames. You could then execute the DTS from T-Sql and pass in the variables.
Otherwise, I think you can use SMO although it will take looking closely to see the best path. Even using SMO, the easiest way may end up being scripting a backup and restore of the database. It seems to me that it is the cleanest option.
|||We dont' want to "deploy" a database. It need to be a SQL SP that we can run that will copy an existing "base" database in our server to a new database.|||The copy database wizard has no facility to create a script.
This needs to be something that can be run through the application by an administrator.
|||At the end of the wizard, you can set it to run on a schedule instead of immediately. In the schedule, just set it for a previous date so it won't ever run. Then it will create the copy package for you.|||That was a great idea....BUT...the script button is disabled. 
1) just back up the original database and make a copy
2) Create a new database
3) Then use attach and dettach command
EXEC sp_attach_db @.dbname = N'AdventureWorks',
@.filename1 = N'c:\Program Files\Microsoft SQL Server
\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL Server
\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf' ;
just set the parameters..all these you can do in one SP.
