Copy a database between servers with one command

  • roland.hangg (9/23/2014)

    Thank you for your tips. I use the script since many years and it always worked fine for me. O.K. I dont use blanks in my path and filenames (old school:-)) and I dont have domain admin permissions. Only DB-server wide (local admin and sysadmin role).

    Regards, Roland

    Please don't get me wrong, maybe my lack of knowledge, but I don't understand how can local admin/sysadmin account on one server connect to another server ( I presume local admin/sysadmin on one of the servers is not the same). On my dev sql instance SQL 2008R2, my account is sysadmin and local admin also, so I expected that would work. Maybe I'm missing something.

    So could you explain me this a bit, please?



    P.S. the reason I wrote domain admin account (which is almost never the case) is that I also tried to make copy of small DB from another server to my local server, and was not able to do it, becase paths to FS were not correct. And usually people are using instance name, but in my test scenarios it happened to be that I used default instance.

  • I got the backup(default) copy mode to work for me, but I get an error when I try and do the offline mode. I am using the same DB for testing that I was able to successfully copy in the backup mode. I get the error "2 was unexpected at this time."

  • O.K. sorry, some more details:

    - the user has to be a domain key

    - the user has to be granted to access the \\Server\x$ shares

    - the user needs write permissions on the current Directory (e.g. c:\temp)

    - the user needs read, write and delete permissions on the source and the target paths

    - the user has to be granted to take backups/dbs offline on the source server

    - the user has to be granted to restore and attach databases on the target server

    using default instances don't make problems in my tests.

    @mprokop04: is your source database still online?

    Regards, Roland

  • Yeah apparently I jumped the gun and put it into offline mode myself before running the cmd file. Looks like it needs to be online at the start of running the script.

    Thanks! I am really liking this.

  • Works perfectly now with this version (good trick: ComputerNamePhysicalNetBIOS...).

    I have some permission issue to write to the target directory (permission denied), but working fine if I run my dos shell "as administrator".

    Thx lot!



  • Found 2 additional bugs:

    A: wrong info about existing target db


    echo Û Attention! The database %DB% still exists on %SQLServerT%!


    echo Û Attention! The database %TargetDBName% still exists on %SQLServerT%!

    B. target log files is located in wrong directory


    set CMDLogDevices=%CMDLogDevices%MOVE N'%LogicalName%' TO N'%PathTData%\%TargetDBName%_Log%FileNoExt%.%FileExt%',


    set CMDLogDevices=%CMDLogDevices%MOVE N'%LogicalName%' TO N'%PathTLog%\%TargetDBName%_Log%FileNoExt%.%FileExt%',



  • Can this script be used to copy from a newer version of SQL Server to an older version (assuming there are no newer T-SQL features in the source)? I just had an instance where I had to downgrade a SQL Server 2014 database to SQL Server 2012 and it was a pain in the neck.

  • The script errors for me. Whatever "~13,127" is, it is being interpreted as a literal.

    "The physical filename '~13,127\foo.mdf' may be incorrect."

    And the errors continue for any time an attempt to use '~13,127' occurs.

    Such as line 82.

    set ServerS=%ServerS:~13,127%

    what is that ~13,127 anyway?

  • Nice script. I did something similar for SSAS databases using powershell. One thing I added eventually was a simple windows app front end. You can populate drop downs with instances/databases and then hit a button to move or copy with a few option selections.

    Aigle de Guerre!

  • running on localhost, cmd as admin

    copyDB.cmd localhost databaseName databaseNameCopy

    im getting:

    Files\Microsoft was unexpected at this time.

    same when i give the name of the server and not localhost.

  • Hello,

    @Christian: Thnks for the hints. I've fixed the bugs. (

    @JunkIt: No! As I know it is not possible to copy a database from a newer to an older Version.

    @amccollough-1110430: please check:

    - do you have write permissions on C:- do you have admin permissions on both Servers

    - try to use the servername,port for the target machine e.g. CopyDB.cmd server1 "server2,port" PriceDownload

    - try to start the script from the target machine

    @ofirgeller22: This is not the MSDOS copy command. source server, target server and database name is NOT optional.



  • I'm DBA in China.When I used this CMD,I saw many unknown characters.It doesn's work an I don't know what happened.Then I transfer this CMD file to an unicode file.But it still doesn't work.

    Could you help me?

  • Hi,

    sorry, I don't have experience with Chinese OS. Maybe this will help:

    Try to save the file directly to your HD. Do not open it in an editor.


    Replace the frame characters in the command file with "-"

    The Problem may also be that server names, directory names or database names contain Unicode characters. But for that I don't have experience, sorry.



  • I understand that they are not optional parameters. i tried the name of the server/instance and not localhost as well. i get

    127.==.. was unexpected at this time.


    SQL was unexpected at this time.

    are you saying this is not a valid cmd?

    CopyDB.cmd myServer\myInstance myServer\myInstance theDatabaseName theTargetDatabaseName

    what am i missing?

  • Hi ofirgeller22,

    your syntax is correct in this way. Are there blanks in your paths or filenames or database names?

    Maybe it will help if you run the statement on the source or the target server.

    Supported by Cláudio Silva (thank you) we made some modifications so that blanks in path is now running well. So feel tree to try with this version:



Viewing 15 posts - 16 through 30 (of 53 total)

You must be logged in to reply to this topic. Login to reply