T-SQL Script to copy a database

Question

I would like to find a T-SQL script-based way to copy my "PROD" database (which is read-only and consists of several filegroups) to a read/writable "EDIT" database, which I can use and edit data in.

I know about the Copy Database Wizard, and am using it right now - however, I'd prefer to automate this so that a single script could restore the PROD database from a backup and copy it to the EDIT database as well.

It seems there's no "COPY DATABASE" command in T-SQL, nor can I find any other way to achieve this - short of restoring the backup twice. Really? Is there no better way to do this??

What I'd like to do is this:

1. start the restore from disk, into "MyPROD_Database"
2. after I'm done, I'd like to copy that DB I just restored to "MyEDIT_Database"
3. as a last step, I'd like to set the filegroups of my PROD database to read-only

Preferably all in one T-SQL script which I can run from SSMS and/or sqlcmd.

System is SQL Server 2005 (from where the backup come) and SQL Server 2008 (where I restore to).

The gauntlet is out there - who'll pick it up? :-)

Marc

Answer 1

SSIS has a copy database function, so you would have to execute your T-SQL via SSIS, and then you could have a copy database as a step in that process.

Answer 2

The SQL Server Publishing Wizard can be automated with command line arguments.

From the help page

The following command will script the FooDB database from the default instance on a machine named MYSERVER using SQL Server authentication with the username "Alice" and the password "7h92-v6k3" to the file C:\FooDB.sql:

sqlpubwiz script -d FooDB -S MYSERVER -U Alice -P 7h92-v6k3 C:\FooDB.sql

So once you have that script you will be able to run it on your other database instance using SQLCMD.

sqlcmd -S MYSERVER -U Alice -P 7h92-v6k3 -i C:\FooDB.sql -o 

Answer 3

If the login running the script has sysadmin rights, you could do this:

• restore the PROD database
• detach the PROD database
• copy the PROD database's files using xp_cmdshell
• attach the PROD database using the original files
• attach the EDIT database using the copied files

If you can use either SQLCMD or Management Studio's SQLCMD mode to run the script, you could also take advantage of SQLCMD's !! syntax to execute an OS command to copy the files.

In any case, though, this (and the copy database wizard, for that matter) involves taking the PROD database offline to do the copy. If that's not an option, multiple restores may be your only choice.

Answer 4

Can this help: http://www.codeproject.com/KB/database/ScriptDatabase.aspx

Script out your entire database including SQL Insert statements for importing data into the database.

Answer 5

Check out some of these resources.

http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/66372/

http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/62380/

Answer 6

This might be useful:

SQL Multiscript

Answer 7

Far faster and simpler to do a backup / restore:

SqlCmd -E -S localhost -Q "BACKUP DATABASE mydb TO DISK='c:\temp\mydb.bak'"
REM Make Sure connections are closed
SqlCmd -E -S localhost -Q "ALTER DATABASE PIDataCopy SET SINGLE_USER WITH ROLLBACK AFTER 60"
SqlCmd -E -S localhost -Q "RESTORE DATABASE PIDataCopy FROM DISK='c:\temp\mydb.bak WITH MOVE  'mydb_Data' TO 'C:\SQLData\PIDataCopy_Data', MOVE 'mydb_Log' TO 'C:\SQLData\PIDataCopy_Log', RECOVERY,REPLACE"
SqlCmd -E -S localhost -Q "ALTER DATABASE PIDataCopy SET MULTI_USER"