Copy a database between servers with one command

Question

Copy a database between servers with one command

Viewing 8 posts - 46 through 52 (of 52 total)

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

Gary A Davis SSC Journeyman Points: 80 More actions · Answer 1

I just used this command to copy a couple of databases from Sql Server 2012 to 2016 (CTP). I did have a few issues.

1. There were old /backup/xxxxx.bak files I had to delete or the backup command got an error message.

2. The command I entered was CopyDB ".\WebSql,55800" "192.168.0.118\WebSql,55800" WebData. This got an error since the target server was MEDIA. I created a Host entry for this name with the IP and changed the command to use "Media\WebSql,55800" for the target.

3. I had a problem with Access Denied on the C$ Administrative Share on the target (Windows 10) server. The fix was to create a registry entry with value of 1 on the target:

Key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System
Value: LocalAccountTokenFilterPolicy
Data: 1 (to disable, 0 enables filtering)
Type: REG_DWORD (32-bit)

4. I got a syntax error with the statement if "%SQLServerS%"=="%SQLServerT%" goto RestoreDB since the variables already had the quotes in it so I changed the statement to be if %SQLServerS%==%SQLServerT% goto RestoreDB.

Thanks,

Gary Davis

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

Thanks for the script. This will take some time to go through.

dormelchen 52646 SSC Veteran Points: 272 More actions · Answer 3

hello,

i got some problems with the script - and i hope that you can help me:

Source-Log Path: ~13,127 is not correct i think 🙂

Target-Log Path: ~13,127 is not correct i think 🙂

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¦

Setting environmet variables... ¦ ¦

______________________________________________________________________¦ ¦

_______________________________________________________________________¦

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

Source-Instance: DBSTORE

Source-Server: DBSTORE

Source-Backup Path: Database

Source-Data Path: E:\MSSQL10.MSSQLSERVER\MSSQL\Backup

Source-Log Path: ~13,127

Target-Instance:

Target-Server: DBSTORE1\DBSTORE1

Target-Backup Path: DBSTORE1

Target-Data Path: J:\MSSQL11.DBSTORE1\MSSQL\Backup

Target-Log Path: ~13,127

Ziel-Log Pfad: L:\MSSQL11.DBSTORE1\MSSQL\Data

TransferDB: Database

_______________________________________________________________________

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

Source path: E:\MSSQL10.MSSQLSERVER\MSSQL\Backup

Target Path: J:\MSSQL11.DBSTORE1\MSSQL\Backup

Log Path: L:\MSSQL11.DBSTORE1\MSSQL\Data

_______________________________________________________________________

The Target Log Path is ????

In my SQL Server the Path is ok and set

and so i got the errors:

A file activation error occurred. The physical file name '~13,127\Database_data.mdf' may be incorrect. Diagnose and correct additional errors, and retry th

e operation.

Any idea ?

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

Hi dormelchen,

It reads as though the variable declaration for PathSLog (among others) has been edited incorrectly.

My declaration is:

findstr /C:"Log Path: " "%TempFile2%" > "%TempFile1%"

for /F "tokens=*" %%i in (%TempFile1%) do @set PathSLog=%%i

set PathSLog=%PathSLog:~13,127%

Which means:

get a value from a file

store it

return only the characters starting at 13 for 127 characters.

Take a look at your file again,

- Mick

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

Thank you mick.regan for answering. I will try to complement your answer a little bit:

The script gets the default log (and data) path with the following statement:

SELECT CAST(SERVERPROPERTY('instancedefaultlogpath') AS NVARCHAR(512))

the result is written in a temporary textfile. For example:

Log Path: C:\SQL\DatabaseLogFiles

The string "C:\SQL..." starts at position 13. So the command takes the content of the variable %PathSLog% starting at position 13 for 127 characters (should take the whole rest of the line)

If there was a problem with getting the string with the SQL-statement, maybe you do not have permissions or the server name was wrong, etc., the variable %PathSLog% contains "~13,127" and you will get the error.

Solution:

Try to start the SQL-command from commandline (using SQLCMD) and look for the error message. Then resolve the Problem.

In your SQL-Server settings of your source and target server check the default pathes (rightclick SQL-Server in SSMS -> Properties -> Database Settings -> Database default locations).

Sometimes it helps to run the command on the other server (source/target)

Hope this helps.

Best regards, Roland

pdherde 75051 Valued Member Points: 68 More actions · Answer 6

This is a great little script. This can save me a lot of time during my upgrades. I had to tweak this a little to accommodate spaces in the directory names. Also I am running into a similar issue that Dimitry ran into where the Target DATA and LOG path is being returned ad ~13,127. I determined if your target SQL has an instance name is what is causing the issue. Has anyone come up with a resolution for that? I have tried a few things but have not been successful.

pdherde 75051 Valued Member Points: 68 More actions · Answer 7

Never Mind I found the link to the updated script. Thank you for sharing.

josh.kewley Newbie Points: 3 More actions · Answer 8

Would be nice if the original article provided a link to the fixed version that Roland made.

For those of you who are looking, here it is: http://rolx.de/download/CopyDB.cmd