What is the most efficient way to restore multiple databases in SQL 2008

Question

Hi all,

I'm in the process of doing a large scale server migration, as such I have to move 50+ SQL 2005 databases to a new SQL 2008 server installation.

The DB guys have only given me a backup of each database, so I have a directory with some 50 .bak files sitting in a directory (ie c:\db) that I need to restore.

I need to restore each database to the new server.

I can do this individually in Management Studio, but that would be time consuming. Is there a more efficient way of solving this problem.

So my question is:

What is the most efficient way of restoring all of these databases.

Machine background: The server is Win 2k8, with SQL 2008 Workgroup Edition, .net 4 is installed along with Powershell 2.

Thanks in advance.

Answer 1

Edited after comment: you can script restores, like:

restore database DatabaseName
from disk = N'c:\dir\BackupFileName.bak'
with file = 1,  
move N'DatabaseName' to N'c:\dir\DatabaseName.mdf',  
move N'DatabaseName_log' to N'c:\dir\DatabaseName.ldf',  
stats = 10, 
recovery 

The two move lines move the files to a location on the new server. Usually the names are DatabaseName and DatabaseName_log, but they can vary.

With recovery means: bring database online without waiting for additional log restores.

You can generate a script like that from the restore wizard. Click the icon left above and select script ... to new window.

Answer 2

Write a custom application/script? You could extend SSMS or use SQL server tools to write an application that just reads these files and restores them to the database. I know it is possible in .net, might be possible using powershell scripts as well.

This is efficient if this task is to be done in a short period of time during the production migration, otherwise the overhead of writing the app is more than doing 50 restores manually! But if you are a developer and you choose manually, then shame on you! :).