Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 347
Jeremy Cade Avatar asked Aug 26 '10 11:08

Jeremy Cade


People also ask

What type of backup is fastest type to restore a database from scratch?

Full Backup But it's also the quickest to restore from because all the files you need are contained in the same backup set. Full backups on a regular schedule require the most storage out of each method.


1 Answers

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....

like image 51
Andomar Avatar answered Sep 23 '22 02:09

Andomar