Copy a database between servers with one command

  • roland.hangg

    SSC Veteran

    Points: 255

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

  • Robert Sterbal-482516

    SSCrazy

    Points: 2784

    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?

  • Sharon-318531

    SSC Enthusiast

    Points: 143

    Any reason this wasn't written in PowerShell?

  • roland.hangg

    SSC Veteran

    Points: 255

    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: I'm not so familiar with .ps1 and it's more compatible with older OS Versions.

    Kind regards,

    Roland

  • Perry Whittle

    SSC Guru

    Points: 233772

    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.

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

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • christian.renevey

    SSC Rookie

    Points: 34

    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

  • roland.hangg

    SSC Veteran

    Points: 255

    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

  • dmitriy 68472

    SSC Rookie

    Points: 26

    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 😉 ¦ ¦

    ¦______________________________________________________________________¦ ¦

    _______________________________________________________________________¦

  • roland.hangg

    SSC Veteran

    Points: 255

    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

  • christian.renevey

    SSC Rookie

    Points: 34

    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

  • roland.hangg

    SSC Veteran

    Points: 255

    Hi Christian,

    please try this...

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

    Regards,

    Roland

  • KeiserSoze

    SSC Rookie

    Points: 34

    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

  • jagsalog

    Valued Member

    Points: 51

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

    thanks!

  • roland.hangg

    SSC Veteran

    Points: 255

    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

  • roland.hangg

    SSC Veteran

    Points: 255

    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 53 total)

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