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:
- start the restore from disk, into "MyPROD_Database"
- after I'm done, I'd like to copy that DB I just restored to "MyEDIT_Database"
- 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
Best Answer
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.