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? :-)


7 Answers 7


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.

  • +1 - interesting info! I am not aware enough of what SSIS offers, obviously - time to brush up on SSIS !
    – marc_s
    Jun 26, 2009 at 15:58
  • Amazon doesn't allow SSIS in their RDS sql server FWIW :( May 19, 2015 at 14:52

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 
  • I hope these are fake passwords ;-)
    – splattne
    Jun 26, 2009 at 13:41
  • 1
    They are from the sql server publishing wizard example page, so I guess they are, but who knows for sure.
    – Bob
    Jun 26, 2009 at 13:43
  • Can I call the Sql Server Publishing Wizard in the middle of a T-SQL script?? I need to (a) restore the PROD database, then (b) copy it to EDIT, and then (c) set the PROD to read-only, all in one script, if ever possible
    – marc_s
    Jun 26, 2009 at 15:19
  • You should make a batch file to run all of your sql scripts.
    – Bob
    Jun 26, 2009 at 15:29
  • @Bob - yes, that might be a possibility - thanks for the tip.
    – marc_s
    Jun 26, 2009 at 15:58

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.

  • +1 interesting thought - I'll have to check that out.
    – marc_s
    Jun 26, 2009 at 15:59
  • Would this solution work?
    – Germstorm
    Mar 17, 2011 at 7:19

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.

  • I doubt this will be really feasible and faster than restoring 5.5 GB of data twice......
    – marc_s
    Jun 26, 2009 at 15:20

Check out some of these resources.



  • Thanks, I do know how to restore databases and such - that's what I'm doing with the T-SQL script right now. What I'd like to do is copy the just restored database to another one in the middle of my T-SQL restore script.
    – marc_s
    Jun 26, 2009 at 15:20

This might be useful:

SQL Multiscript


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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .