Copy a database between servers with one command

  • Comments posted to this topic are about the item Copy a database between servers with one command

  • This is quite a long and powerful script.

    It will take some effort to make sure it is working correctly.

    Are their parts of it that would not work in a traditional batch file?

  • Any reason this wasn't written in PowerShell?

  • Hi Robert,

    thanks for your post. The only external (non DOS) command that is used in the script is SQLCMD.exe provided by SQL-Server 2005 Client Tools and higher. So it is running on WondowsXP just as well as on Windows Server 2012R2.

    I'm using the script since several years for my daily work. Since this time I'm developing on it. Last change was to support databases with more than one data or log file. Feel free to put it to the acid test :-).

    @sharon-2: I'm not so familiar with .ps1 and it's more compatible with older OS Versions.

    Kind regards,

    Roland

  • It's good, quite long though, but then you are working at Cmd prompt level. Powershell would be my preference, you can invoke sql cmds just as easily via PS.

    If i'm critical it's due to the references to old system catalogs and procs.

    Sys.master_files and sys.database_files replace the old database catalog info, also get used to using ALTER USER WITH LOGIN rather than sp_change_users_login.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hi,

    Really useful script!! THX!

    Do you plan to make a version compatible with clustered instances ?

    Only the copy part is failing because you need to get the physical servername of the instance in order to copy the backup from one machine to the other..

    Rgds

    /Christian

  • Hi Christian,

    in our environment it still runs also with cluster instances. Just use the cluster virtual name in your parameters. The network path with the virtual name should be abailable.

    For example:

    copyDB VirtualClustername1\Instance "VirtualClustername2,9876" MyDB

    If you use SQL-Server 2012 with AlwaysOn the script gets the active node itself and uses the node name for in the network path.

    Kind regards,

    Roland

  • Hi! Thanks for the script. However it does not work:

    c:\>CopyDB.cmd server1 server2\dev PriceDownload

    ¦¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¦

    ¦ Setting environmet variables... ¦ ¦

    ¦______________________________________________________________________¦ ¦

    _______________________________________________________________________¦

    ¦¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

    ¦ Source-Instance: server1

    ¦ Source-Server: server1

    ¦ Source-Backup Path: PriceDownload

    ¦ Source-Data Path: D:\Backup

    ¦ Source-Log Path: D:\DATA

    ¦ D:\DATA

    ¦ Target-Instance:

    ¦ Target-Server: server2\dev

    ¦ Target-Backup Path: ~13,127

    ¦ Target-Data Path: ~13,127

    ¦ Target-Log Path: ~13,127

    ¦ Ziel-Log Pfad: ~13,127

    ¦

    ¦ TransferDB: PriceDownload

    ¦_______________________________________________________________________

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

    After 2nd run with the same parameters it shows:

    ¦¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¦

    ¦ ¦ ¦

    ¦ Error getting the pathes!!! ¦ ¦

    ¦ ¦ ¦

    ¦______________________________________________________________________¦ ¦

    _______________________________________________________________________¦

    ¦¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¦

    ¦ End of batch. Thanks for using rolX CopyDB 😉 ¦ ¦

    ¦______________________________________________________________________¦ ¦

    _______________________________________________________________________¦

  • Hi Dmitriy,

    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

    Regards,

    Roland

  • Many thx for your reply.

    Does not work for me.

    I try to copy from one clustered instance to another clustered instance.

    One instance has path for backup destination in T: drive letter, the other S:

    Backup destination for both instance is on a clustered disk.

    The script try the following:

    [font="Courier New"]move the backup...

    Source-Path: \\"vip1"\T$\Backup

    Target-Path: \\"vip2"\S$\Backup

    [/font]

    Im definitively not able to reach this path \\"vip1"\T$\Backup..

    but this one will work:

    \\"physicalnetbiosname"\T$\Backup

    Rgds

    /Christian

  • Hi Christian,

    please try this...

    http://rolx.de/download/CopyDB.cmd

    Regards,

    Roland

  • Hi,

    thx for the effort, but...

    I tried the second script also but it desn't work either.

    Found out that there are some quotes missing (especially in windows enviroment folders/files with spaces).

    Another thing is that script is not working with default server instance either.

    eg. copydb serverName serverName dbsource dbsourcecopy

    In the second script (i didn't look into it, also got errors) I noticed that my account was denied

    (I'm in domain admins group, on local dev machine, sql developer edition, default local sql installation), and in this case I would expect I would need all rights I needed. Basically tried to make a copy of DB on my machine. That try didn't pass either. Also noticed all paths are not correctly build.

    When i fixed "" for files, backup finally worked, restore didn't, I did get this in echo:

    RESTORE DATABASE [dbNamecopy] FROM DISK = N'D:\BACKUP\LOCAL\dbName.bak' WITH FILE = 1,

    MOVE N'dbName' TO N'\dbNamecopy_data.mdf', MOVE N'dbName_log.ldf' TO N'\dbNamecopy_Log.ldf', NOUNLOAD, REPLACE, STATS = 10

    and this doesn't work as you can see. Here is a bug with target path also.

    Hope this will help with script updates.

    Cheers,

    K.

    P.S. asumption that you can use domain account to acomplish this backup, copy, restore might be wrong imo, of course it depends on the network layout, but possibility you can define other usr/pwd would be a good thing

  • a daft question, maybe i am just blind, where is the script in the article?

    thanks!

  • 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

  • Look at the bottom of the article:

    http://www.sqlservercentral.com/articles/Copy+Database+Script/107377/

    direct link:

    http://www.sqlservercentral.com/Files/CopyDB.cmd/23588.cmd

    Regards, Roland

Viewing 15 posts - 1 through 15 (of 52 total)

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