CopyDB.cmd is a DOS-command script that allows you to copy a SQL-Server database from one SQL-Server to another having several options. It's also possible to create a copy of a database on the same server. The complete copy process will be done with just one single command from command prompt.

How CopyDB.CMD works

You can choose between 2 ways to copy a database.

• Backup the database on the source server, copy the backup set to the target and restore the backup set.

• Set the database on the source server offline, copy the database files to the target server and attach them on the target Server

The backup and restore method is the standard method. The script collects all information by executing some SQL commands on the source and the target SQL-Server using "SQLCMD". The rest is done with MSDOS Batch commands.

After the transfer of the database the script sets the compatibility level to the version of the target server and fixes the orphant users if they are available on the target server.

Preparation

First save the command file (CopyDB.cmd) to a local folder, e.g. C:\TEMP.

Next, open a commandline ([WIN]+[R] -> CMD -> [ENTER])

Lastly, change the current directory (type: C: [ENTER] then "CD C:\TEMP [ENTER])

Usage:

CopyDB PARAMETERS

Source-DB_Server[\Instance]

Target-DB_Server[\Instance]

Database Name

optional: New Database Name

optional [datestamp|offline] 

Examples:

1.) copy the database TestDB from server1\Inst1 to server2:

C:\Temp> CopyDB Server1\Ins1 Server2 TestDB

2.) copy the database TestDB from server1 Port 1234 to server2 port 4567:

C:\Temp> CopyDB "Server1,1234" "Server2,4567 TestDB

3.) like Example 1 but the name of the target DB changes to TestDB2

C:\Temp> CopyDB Server1\Ins1 Server2 TestDB TestDB2

4.) like Example 1 but using the offline mode

C:\Temp> CopyDB Server1\Ins1 Server2 TestDB TestDB2 offline

5.) like Example 1 but there will be added a timesatmp to the new name of the target DB (TestDB_YYYYMMDD)

C:\Temp> CopyDB Server1\Ins1 Server2 TestDB datestamp

Requirements:

The user running the script must be member of the local admin group on the source and the target server. The user running the script must also be member of the sysadmin serverrole of the source and the target SQL-Server.

When you run the script, it will create temporary files and a log file of the database transfer. This files will be placed  in the folder where the script file is run from. This can be your PC or one of the servers (source or target server). For that reason you need write permissions for the directory where you call the script, e.g. C:\Temp. Feel free to change the path for the logfile in the script at line 48.

Notes/Features:

The script is successfully tested on SQL-Server 9 (2005), 10 (2008), 10.5 (2008R2) and 11 (2012).

If the database to be copied still exists in the target the script asks to drop it. In this point you are able to break the further execution. If the database to be copied still exists and it is running in an Availibility Group (SQL-Server 2012 Always On feature), you first have to remove it from this Availibility Group.

The script uses the default data, log and backup path defined in source and target SQL-Server for the transfer.

The script automatically determines the active node of an Always On server (SQL-Server 2012 and higher) if you use the virtual name as source or target server name.

If the users of the database exist on target SQL-Server the script automatically fixes the orphaned users.