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.
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.
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...
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With