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

Question

I am engaged in large-scale server migration, so I need to move 50+ SQL 2005 databases to the new SQL 2008 server installation.

The guys from DB only provided me with a backup copy of each database, so I have a directory with 50 .bak files that are in the directory (i.e. c: \ db) that I need to restore.

I need to restore each database to a new server.

I can do this individually in Management Studio, but it will take a lot of time. Is there a more efficient way to solve this problem.

So my question is:

What is the most efficient way to recover all these databases.

Machine Background: Win 2k8 Server, with SQL 2008 Workgroup Edition, .net 4 installed with Powershell 2.

Thanks in advance.

Answer 1

Edited after comment: you can restore the script, for example:

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 files to a location on the new server. Usually the names of the names DatabaseName and DatabaseName_log, but they may vary.

With recovery means: bring the database online, without waiting for the recovery of additional logs.

To generate a script, click the script button (top left) in the database recovery wizard window and click Script action to...

Answer 2

Write a custom application / script? You can extend SSMS or use SQL Server tools to write an application that simply reads these files and restores them in the database. I know this is possible in .net, possibly using powershell scripts.

This is effective if this task is to be completed in a short period of time during a production migration, otherwise the overhead of recording an application exceeds 50 manual recoveries! But if you are a developer, and you choose manually, then you are ashamed!:.)