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

sql sql-server-2008 migration data-migration
Jeremy Cade picture Jeremy Cade · Aug 26, 2010 · Viewed 8.3k times · Source

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

Andomar picture Andomar · Aug 26, 2010

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

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
Rename column SQL Server 2008

I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL. ALTER TABLE table_name RENAME COLUMN old_name to new_name; This statement doesn't work.

Read More