6

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.

Jeremy Cade
  • 1,351
  • 2
  • 17
  • 28
  • I am trying to get an answer here - https://stackoverflow.com/questions/22207519/need-help-to-restore-multiple-databases-bak-files-from-one-folder – Steam Mar 05 '14 at 19:38

2 Answers2

6

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.

To generate a script like this, click the Script button (top left) in the Restore Database wizard window, and click Script action to....

Community
  • 1
  • 1
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • I've just updated the question to be a little bit more clear. – Jeremy Cade Aug 26 '10 at 12:39
  • I tried your code like this and it did not work - `DECLARE @path varchar(3000) DECLARE @file varchar(1000) DECLARE @loc varchar(7000) SET @path = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\' SET @file = 'AdventureWorksDW.bak' SET @loc = @path + @file RESTORE FILELISTONLY FROM DISK = @loc GO` – Steam Mar 05 '14 at 20:10
0

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! :).

Roopesh Shenoy
  • 3,389
  • 1
  • 33
  • 50