September 23, 2014 at 6:28 am
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?
Cheers,
K.
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.
September 23, 2014 at 7:11 am
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."
September 23, 2014 at 7:17 am
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
September 23, 2014 at 7:18 am
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.
September 23, 2014 at 7:43 am
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!
Rgds
/Christian
September 23, 2014 at 8:10 am
Found 2 additional bugs:
A: wrong info about existing target db
old:
echo Û Attention! The database %DB% still exists on %SQLServerT%!
new:
echo Û Attention! The database %TargetDBName% still exists on %SQLServerT%!
B. target log files is located in wrong directory
old:
set CMDLogDevices=%CMDLogDevices%MOVE N'%LogicalName%' TO N'%PathTData%\%TargetDBName%_Log%FileNoExt%.%FileExt%',
new:
set CMDLogDevices=%CMDLogDevices%MOVE N'%LogicalName%' TO N'%PathTLog%\%TargetDBName%_Log%FileNoExt%.%FileExt%',
Rgds
/Christian
September 23, 2014 at 10:22 am
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.
September 23, 2014 at 10:29 am
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?
September 23, 2014 at 1:29 pm
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!
September 23, 2014 at 5:12 pm
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.
September 23, 2014 at 11:14 pm
Hello,
@Christian: Thnks for the hints. I've fixed the bugs. (http://rolx.de/download/CopyDB.cmd)
@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.
Regards,
Roland
September 24, 2014 at 1:58 am
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?
September 24, 2014 at 2:17 am
Hi joe.tj,
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.
or
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.
Regards,
Roland
September 24, 2014 at 3:04 am
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.
or
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?
September 24, 2014 at 5:31 am
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:
http://rolx.de/download/CopyDB.cmd
Regards,
Roland
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply