September 25, 2014 at 7:51 pm
Hi Roland,
I've been using your script just this morning and found all the issues above to be related to either of the following:
- use of the period character (.) instead of double quote (") in bracing the batch IF statements (a method I hope was intended to prevent null comparison errors).
- the assumption of well formed directory\file paths and omission of enclosing quotes ("").
I believe the attached resolves these issues for at least simple use i.e. CopyDB.cmd SRCserver DSTserver SRCdatabase DSTdatabase
I also added the examples to the help menu and fixed the "summary server information" section alignment as best I could reckon (is "Ziel-Log Pfad" in reference to the Log file?)
Cheers,
Mick
_________________________________
Accidental DBA (Intentional ScriptKiddie)
October 1, 2014 at 6:35 am
Hi Mick,
thanks for your modifications. I did some more modifications and after they are tested I will provide the command file here again.
Regards Roland
October 1, 2014 at 3:51 pm
ofirgeller22 (9/23/2014)
running on localhost, cmd as admincopyDB.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.
Having a similar error when I try to run...
copyDB.cmd sourceServerName targetServerName databaseName
October 9, 2014 at 9:11 am
Anybody try putting this into a batch file or something of that nature to try and move a lot of DBs in an automated fashion?
If so care to share an example?
October 10, 2014 at 1:12 am
Hi mprokop04,
do you mean something like this:
1.) Open a query on the source server
2.) Run: select 'call c:\temp\copydb ' + @@servername + ' TargetServer\TargetInstance ' + Name + ' offline' from sys.databases where database_id > 4
3.) Copy the result in a new commandfile e.g. C:\temp\CopyAllDBsOffline.cmd
4.) Run C:\temp\CopyAllDBsOffline.cmd
Important! Do not use the Start command instead of Call because there will be a conflict with temporary files while executing copyDB.cmd
Regards Roland
October 30, 2014 at 8:35 am
Hi,
Have you ever tried to call CopyDB.cmd from an SQL Job ?
I've created a job with a job step of cmdExec type and the command is like :
cmd.exe /c "D:\TEMP\CopyDB sourceInst destInst sourceDB destDB"
Note : The SQL agent account is windows admin and sysadmin on both server.
The job history output gives access error
I think there's a problem with the access path.
November 4, 2014 at 3:45 am
Hello all,
as promised I've now tested the new version (20141104) and you will find the download here:
http://rolx.de/download/CopyDB.cmd
This version now supports blanks in path and filenames. Nevertheless I recommend not to use blanks in path, filenames, devicenames or database names.
I've also added a section for post processing. Some databases have to be copied several times and after the copy there are some special actions to do. This special actions can be applied in a post processing script. For example it may look like this:
@echo off
rem post processing
if /I .%SQLServerT%.==.MyTargetServer. goto PostProcessing1
if /I .%DB%.==.MySourceDB. goto PostProcessing2
if /I .%DB:~0,4%.==.ABC_. goto PostProcessing3
goto end
:PostProcessing1
%SQLCommand% -E -S %SQLServerT% -d %TargetDBName% -Q"sp_adduser [MyUsername]"
goto end
:PostProcessing2
%SQLCommand% -E -S %SQLServerT% -d %TargetDBName% -Q"sp_addrolemember 'db_owner', [MyUsername]"
goto end
:PostProcessing3
%SQLCommand% -E -S %SQLServerT% -d %TargetDBName% -Q"ALTER LOGIN [MyUsername] WITH DEFAULT_DATABASE=[MyDB], DEFAULT_LANGUAGE=[us_english]"
goto end
:end
Regards Roland
December 4, 2015 at 3:37 am
Any plans to modify this to support Azure SQL DB?
December 4, 2015 at 3:58 am
Hi psheperia,
no, there are no plans because I don't use Azure.
Best regards, Roland
December 4, 2015 at 4:05 am
Ok thanks Roland. Shame, it's so similar to SQL Server but copying between there and SQL Server is a pain. I may put a tool together myself, just thought it was worth asking first.
Kind regards
Paul
December 4, 2015 at 8:15 am
Hello,
The typical backup and restore methodology between 2 servers at different versions (ex. SQL 2012 db backed up and restored to SQL 2008) does not work\ is not supported by SQL Server.
Will the DBCopy script allow a SQL 2012 database to be copied to a SQL 2008?
many thanks,
DM
December 4, 2015 at 8:25 am
Hello Metcalfe,
sure it is not possible to downgrade databases. To do so you have to create a new database on the old version SQL-Server and transfer the objects of the new database to the new created database.
Have a nice Weekend, Roland
December 4, 2015 at 8:27 am
Just ran across this. Good, good stuff.
-- Itzik Ben-Gan 2001
December 4, 2015 at 8:46 am
Am I missing something? It looks like to me that the source parameter is missing the database name. How does the script know which database to copy?
December 4, 2015 at 11:20 am
Nevermind....
Viewing 15 posts - 31 through 45 (of 53 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy