posted 9/1/2010 by Hajan Selmani
In the following blog post, I will try to explain step by step about how to restore your database from BAK (backup) file using SQL Server Management Studio 2005 (or 2008).
This is a STEP BY STEP explanation on how I did restore (the shortest way).
Note: You don't need to have your new database (to which we will do restore) created in advance.
1. Open Microsoft SQL Server Management Studio 2005
2. Connect to the Server
3. You will have your databases
I have created backup from my testDatabase. So, It won't matter if the testDatabase will be there or will be dropped, I have the backup file in C:\backup\myBackup.BAK.
4. Right cilck on Databases (1) and click the Restore Database... (2)
5. Write the database name of your new database (1) that you would like the SQL Server Management Studio to create for you (be careful, the name should not exist), then chose the option From device (2) and click the ... button (3) - (see pic bellow)
6. In the new window that will appear, click ADD
7. Then, find the BAK file from your file system, in my case it's in C:\backup\myBackup.BAK (1) and click OK (2)
8. The path to the file will be shown in the next window (1), then click OK (2)
9. Then, thick the checkbox as in the following image under 1 (1) and go to Options (2)
10. Then you will see the following screen:
Double click in the first row on the Restore As column
and change the testDatabase to myNewDbName
NOTE: In SQL Server Management Studio 2005, you will need to change the name manually, while in SQL Server Management Studio 2008, it will change the name automatically depending of the name we have previously written as 'myNewDbName' - This is the main place where developers make mistake when trying to restore and errors are generated.
than, make the same with the second row, testDatabase_log to myNewDbName_log
11. At the end, just click OK (1). You will see the Progress (2) and the message (3) like in the following screen should pop up.
And, thats it!
This complete STEP BY STEP explanation can be also made with the following SQL Script, which is equal to what we did using the SSMS Wizards.
RESTORE DATABASE [myNewDbName] FROM DISK = N'C:\backup\myBackup.BAK' WITH FILE = 1, MOVE N'testDatabase' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\myNewDbName.mdf', MOVE N'testDatabase_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\myNewDbName_log.LDF', NOUNLOAD, STATS = 10 GO
Worked great. Thank you very much.
This comment is awaiting moderation.
What kind of email newsletter would you prefer to receive from CodeAsp.Net?18