0

I need to move 100 databases from various SQL instances to other identical instances on another box.

I know I can manually detach, copy the file, reattach, but is there an easier/automated way? Preferably a UI tool to select the databases to copy and let it get on with it?

This is similar to a previous question, but that was over 7 years ago, perhaps things have moved on?

Community
  • 1
userSteve
  • 1,503
  • 4
  • 20
  • 32

3 Answers3

1

Using the DBA Tools powershell library, you can do something like this:

Get-DbaBackupHistory -SqlInstance Server1 |
 Restore-DbaDatabase -SqlInstance Server2 -withreplace

This will use your existing backups on Server1 and restore them to Server2. My assumption is that if you take tail log backups of all of your databases on Server1, that the restore will use them and you'll have an effective way of copying the databases to Server2 and also have a decent fallback plan if you need to go back to Server 1 (i.e. run restore [dbName] with recovery on everything on Server1)

Ben Thul
  • 2,969
  • 16
  • 23
0

The detach/attach stored procedures are no longer recommended as they can cause issues with Service Broker and a few other things. Your best bet, without a 3rd party tool you would have to purchase, would be to script out taking all the databases offline, then, manually copying the data and log files to the new destination, then attaching them with the "Create Database" for attach code. I have some code I could share with you if you are interested which automates these statements.

rvsc48
  • 431
  • 2
  • 7
  • yes that would be great. Any scripts you have would be appreciated. Thanks – userSteve Mar 27 '17 at 19:09
  • If you don't go the PowerShell route as Ben suggested - This link you can use for a script to take the databases offline - http://stackoverflow.com/questions/847076/sql-script-to-take-a-microsoft-sql-database-online-or-offline. This link you can use to generate the create database for attach statements - http://www.wisesoft.co.uk/scripts/t-sql_script_databases_for_attach.aspx – rvsc48 Mar 27 '17 at 19:33
0

I'm surprised no one mentioned the built in Copy Database Wizard. Seems to be exactly what I want!

userSteve
  • 1,503
  • 4
  • 20
  • 32