September 22, 2014 at 9:33 pm
Comments posted to this topic are about the item Copy a database between servers with one command
September 22, 2014 at 11:02 pm
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?
September 22, 2014 at 11:39 pm
Any reason this wasn't written in PowerShell?
September 22, 2014 at 11:50 pm
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
September 23, 2014 at 12:33 am
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" 😉
September 23, 2014 at 1:28 am
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
September 23, 2014 at 1:56 am
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
September 23, 2014 at 2:10 am
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 😉 ¦ ¦
¦______________________________________________________________________¦ ¦
_______________________________________________________________________¦
September 23, 2014 at 2:22 am
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
September 23, 2014 at 2:34 am
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
September 23, 2014 at 3:26 am
September 23, 2014 at 5:45 am
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
September 23, 2014 at 6:05 am
a daft question, maybe i am just blind, where is the script in the article?
thanks!
September 23, 2014 at 6:10 am
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
September 23, 2014 at 6:13 am
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