4

I need to move about 150 databases from one server to another server.

  1. SQL Server 2008 Web Edition
  2. SQL Server 2012 Preview (in a different datacenter - East Coast Azure)

I was planning on moving them one at a time using RedGate Packager, however this will take a while.

Is there a faster and easier way?

Improve this question
1
  • I am inclined to think that the azure-sql-database tag should remain, based on the datacenter name of East Coast Azure.
    – RLF
    Sep 1, 2015 at 23:42

3 Answers 3

Reset to default
3

The best way to do this effectively I think is to backup all the databases and transfer via Site to Site VPN (setup between your location and East Coast Azure Datacenter).

To backup all databases, setup a SQL agent job with below step. Remember it's a CmdExec type and not a T-SQL statement.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'ALL_DATABASES', @Directory = N'C:\SQL Backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime =312, @CheckSum = 'Y',@Compress = 'Y'" -b

And simple enough, copy all the .bak files from C:\SQL Backups to the remote Azure location. and use a restore script to restore all databases at once.

I recently found a tool (Teracopy) that does the copy/paste much faster than traditional Windows server copy functionality.

Hope this helps.

Improve this answer
2
  • DatabaseBackup isn't a standard SQL proc. It's part of a free package from Ola Hallengren. If you don't want to use that you'll need to roll your own script to back them all up at once.
    – cfradenburg
    Jan 4, 2013 at 21:10
  • You are right. I forgot to reference that. More details about the SP are here: ola.hallengren.com/sql-server-backup.html
    – avakharia
    Jan 5, 2013 at 1:02
2

Do you have network connectivity between the two data centers? If so, this works for me:

Paste the output of "select name from sys.databases where name not in ('master', 'msdb', 'model', 'tempdb')" into a file named control.txt.

Make a script called migrate.cmd that goes something like this (you'll need to add the values at the top, of course):

@ECHO ON

set controlfile=control.txt
set oldserver=
set oldmdfpath=
set newmdfpath=
set newserver=
set oldlogpath=
set newlogpath=
set movedmdfpath=
set movedldfpath=

SETLOCAL DISABLEDELAYEDEXPANSION
FOR /F %%L IN (%controlfile%%) DO (
  SET "line=%%L"
  SETLOCAL ENABLEDELAYEDEXPANSION
  ECHO !line!
  sqlcmd -E -S!oldserver! -Q"EXEC master.dbo.sp_detach_db @dbname = N'!line!'"
  copy "!oldmdfpath!\!line!.mdf" !newmdfpath!
  copy "!oldlogpath!\!line!_log.ldf" !newlogpath!
  sqlcmd -E -S!newserver! -Q"CREATE DATABASE [!line!] ON ( FILENAME = N'!newmdfpath!\!line!.mdf' ),( FILENAME = N'!newlogpath!\!line!_log.ldf' ) FOR ATTACH"
  move "!oldmdfpath!\!line!.mdf" !movedmdfpath!
  move "!oldlogpath!\!line!_log.ldf" !movedldfpath!
  ENDLOCAL
)
ENDLOCAL

Good luck!

Improve this answer
2

I would suggest you to use POWERSHELL as it is very flexible and the code can be resuable and is best supported on windows & SQL Server 2008 R2 and up.

Note: there are tons of scripts that are available on internet, so no need to reinvent the wheel !

Scripts can be found :

For the copying backups from one site to another, it depends on your network bandwidth and the size of the backups. Highly recommend to use robocopy as it is n/w resilient - has retry option along with logging and many more.

As you are using web edition, you wont be able to use backup compression unless you use custom compression techniques or redgate's sql backup.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.