Easiest Way to Move 100 Databases

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

• SQL Server 2008 Web Edition

• 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?

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.

StackExchange Database Administrators Q#31395, answer score: 3