Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 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.

share|improve this answer
    
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 '13 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 '13 at 1:02

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!

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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