T-SQL Script to copy a database
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.
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
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.
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.
Check out some of these resources.
http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/66372/
http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/62380/