Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

6 Answers 6

up vote 2 down vote accepted

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.

share|improve this answer
    
+1 - interesting info! I am not aware enough of what SSIS offers, obviously - time to brush up on SSIS ! –  marc_s Jun 26 '09 at 15:58

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
share|improve this answer
    
I hope these are fake passwords ;-) –  splattne Jun 26 '09 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 '09 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 '09 at 15:19
    
You should make a batch file to run all of your sql scripts. –  Bob Jun 26 '09 at 15:29
    
@Bob - yes, that might be a possibility - thanks for the tip. –  marc_s Jun 26 '09 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.

share|improve this answer
    
+1 interesting thought - I'll have to check that out. –  marc_s Jun 26 '09 at 15:59
    
Would this solution work? –  Germstorm Mar 17 '11 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.

share|improve this answer
    
I doubt this will be really feasible and faster than restoring 5.5 GB of data twice...... –  marc_s Jun 26 '09 at 15:20

Check out some of these resources.

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

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

share|improve this answer
    
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 '09 at 15:20

This might be useful:

SQL Multiscript

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.