Copy a database between servers with one command

Question

Copy a database between servers with one command

Viewing 15 posts - 31 through 45 (of 52 total)

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

mick.regan SSC Rookie Points: 47 More actions · Answer 1

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)

roland.hangg Old Hand Points: 328 More actions · Answer 2

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

alkfjoppprpowe Newbie Points: 5 More actions · Answer 3

ofirgeller22 (9/23/2014)
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.

Having a similar error when I try to run...

copyDB.cmd sourceServerName targetServerName databaseName

mprokop04 Grasshopper Points: 15 More actions · Answer 4

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?

roland.hangg Old Hand Points: 328 More actions · Answer 5

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

Pete Softown Say Hey Kid Points: 674 More actions · Answer 6

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.

roland.hangg Old Hand Points: 328 More actions · Answer 7

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

that_clare SSC Rookie Points: 47 More actions · Answer 8

that_clare

SSC Rookie

Points: 47

December 4, 2015 at 3:37 am

#1844023

Any plans to modify this to support Azure SQL DB?

roland.hangg Old Hand Points: 328 More actions · Answer 9

Hi psheperia,

no, there are no plans because I don't use Azure.

Best regards, Roland

that_clare SSC Rookie Points: 47 More actions · Answer 10

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

Dawn Metcalfe Newbie Points: 5 More actions · Answer 11

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

roland.hangg Old Hand Points: 328 More actions · Answer 12

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 13

Just ran across this. Good, good stuff.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

j.peavey Valued Member Points: 51 More actions · Answer 14

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?

j.peavey Valued Member Points: 51 More actions · Answer 15

j.peavey

Valued Member

Points: 51

December 4, 2015 at 11:20 am

#1844124

Nevermind....