Monday, May 21, 2007

Restoring MS SQL Database From .mdf File

Imagine this scenario, you are planning to reformat your SQL server which has 50 databases and all data and log files stored in SAN system. What is the faster way to restore all the databases once you had finish installed the OS and SQL server? I used to backup one by one using the backup and restore database tool. But recently I found a faster way which could save lot of time.

The method is simple enough that I feel stupid not to know this. All you have to do is attach the .mdf file to the SQL Server. How? Open the SQL Server Enterprise Manager, open the tree (Console Root > Microsoft SQL Servers > SQL Server Group > (Your SQL Server) > Database) right-click on the database and select All Tasks > Attach Database. And then, just select the .mdf file, click OK and you are done. The process is almost instantaneous. So for 50 databases you could finish it less than 2 minutes.

If you're not sure what I'm talking about, you could always start with the default database ship with SQL Server. Right cilck on the Northwind database and select All Task > Detach Database
. After that follow the step above.

Good luck.

1 comment:

Alexis said...

I know nice tool-repair suspect mdf file,it works with sql files and more...Once utility helped me in a minute without payment.Moreover tool showed how save the data as a set of scripts (Choose the Save script to disk option) or export the content to remote database (Choose the Execute script on database option).