SQL Server 2005: How To Restore a Database from a Backup

Your database is down, your users are in a panic, what do you do?. If you have been performing routine backups of your database, you can restore it to the point of the most recent backup. To restore your database, follow these steps:


1. Open SQL Server 2005 Management Studio.

2. Connect to the appropriate server.

3. Expand Databases.

4. Right-click the desired database, mouseover Tasks, mouseover Restore and select Database.

5. The Restore Database window will appear.

6. Ensure that the correct database name appears in the To database field. If not, select it from the dropdown.

7. Under Source for restore, select the From device radio button.

8. Click the button next to the textbox to select the device to restore from.

9. The Specify Backup window will appear.

10. Click Add and locate and select the backup file from the Locate Backup File Window. Click OK.

11. Under Select the backup sets to restore, select all three backups (full, transaction log, and differential).

12. In the left pane, select Options.

13. Under Recovery state, ensure that the Restore with Recovery radio button is selected.

14. Click OK to begin restoring the database.

15. Once the restore is complete, a notification box will appear. Click OK to close the box. You have now restored your database to the most recent state.

 

About Rob Rogers

Once a prolific author here on Tech-Recipes, Rob has moved on to greener pastures.
View more articles by Rob Rogers

The Conversation

Follow the reactions below and share your own thoughts.

49 Responses to “SQL Server 2005: How To Restore a Database from a Backup”

  1. November 15, 2008 at 6:28 am, umesh said:

    I taken backup of database from Sql server 2005 from other PC and try to Restore it on my PC. Both having Windows authentication. But i am facing following error

    System.Data.Sqlclient.sqlError:The media set has 2 media families but only 1 are provided.All member must be provided.(Microsoft.Sqlserver.Smo).

    IS it problem of Ownar . because owanr of .bak file is different from where i try to restore it.

    Reply

    • January 28, 2009 at 8:23 am, Himanshu said:

      Hi,
      This is not the problem of owner, it is just because while selecting the restoring device there must be two paths (1. Default by Microsoft, 2. Given by you).
      So delete the default path given by SQL Server itself, so it remains only one path that is given by you.
      Do let me know in case this doesn’t work also
      Regards,
      Himanshu

      Reply

  2. November 24, 2008 at 12:08 pm, sundar said:

    hello sir/madam,

    i have stored mdf file and ldf file in dotnet solution file. i have copied my project from another system. i have to restore the database inorder to run my dotnet project.
    i have followed your procedure, but when i click the ok button, it shows error.

    please help me….

    Reply

    • March 13, 2009 at 5:57 am, How To attach database sql said:

      mdf and ldf files are not sql2005 based.. its sql 2000.. so install that and not need to restore.. you right click the database in enterprise manager in sql2000..attach the MDF file from location.. then run the program

      Reply

  3. April 17, 2009 at 8:53 am, Shamsul said:

    Great step by step guidance. Worked a treat for me who is a complete novice

    Reply

  4. June 05, 2009 at 2:10 pm, Osmar Mateus said:

    I need to restore my database to a new location (we are moving to another server).

    What do I have to do in this case?

    Thanks

    Reply

  5. June 19, 2009 at 4:57 pm, Sunil said:

    Really This was very helpfull to me.
    Thank You!!
    Thank You very much

    Reply

  6. July 30, 2009 at 8:41 am, R Biswas said:

    I have tried the above steps but didnt work out. I have 2 database servers, one with SQL Server ver 7.0 and other with SQL Server 2005. Can anyone help me out how to restore the backup file (.BAK) from the SQL Server 7.0 to SQL Server 2005 database other than using the above steps, as that didnt work and showed a versoion conflict error.

    Reply

  7. September 25, 2009 at 3:50 pm, David Collins said:

    What if the Specify Backup window remains blank after you have already selected the .bak file in the previous screen. With it being blank, you are unable to restore.
    Any ideas?

    Reply

  8. October 05, 2009 at 7:19 am, Jeff said:

    Hello!
    How to restore with old name of PC and instances?

    Reply

  9. December 08, 2009 at 9:09 am, Anonymous said:

    How about restoring the back up created in SQL server 2005 and then restoring it in SQL Server 2008?

    Can some one post the steps for performing the above task.

    Thank you in advance.

    Reply

  10. February 03, 2010 at 4:48 pm, Anonymous said:

    Are the steps the same if the backup was done in SQL Server 2000?

    Reply

  11. February 15, 2010 at 4:16 pm, Anonymous said:

    hi,iam having a .bak file with size 2323KB.when i try to restore it
    the error msg comes like “to many backup devices specified for backup or restore;
    only 64 are allowed.restore database is terminating abnormally.”
    how can i restore it in sql2005.
    thank u in advance.

    Reply

  12. March 08, 2010 at 2:29 pm, Anonymous said:

    i need to restore my database in sql server 2005 and as i follow the procedure above there still an error. it shows this:

    “system.data.sqlclient.sqlerror:directory lookup the file C:program filesmicrosoft sql serverMSSQLDATASTUDENTS INFOREMATION.MDF failed with the operating system error 3(the system cannot findthe path specified.).(microsoft.sqlserver.express.smo)”

    im using windows 7 and visual studio 2008

    can someone help me regards this matter.

    tnx..

    Reply

    • February 25, 2011 at 5:37 am, Priya pawse said:

      hi nhiel,
      i m facing same problem….do u find any solution..
      plz help me out…
      thanx..in advance…
      regards,
      priya

      Reply

  13. April 07, 2010 at 10:32 am, Anonymous said:

    i do the same but one eror s came like
    “the database s in use”

    Reply

  14. June 08, 2010 at 9:52 am, Thanks said:

    Thank you very much it works

    Reply

  15. June 16, 2010 at 10:03 am, Grinathela 1983 said:

    Hi i have restored database using backup file(.bak) .But it shows me error as ” Restore failure at server(Microsoft.sqlserver.smo) .How to avoid that error and restore my database .

    Reply

  16. July 13, 2010 at 7:50 am, Mithun said:

    Hi
    i have a problem in restoring database in sql server managnebt studio 2005 when i restoring….. the window wil automatical close…… plz help me to restore….

    Reply

  17. July 16, 2010 at 6:51 am, priya jose said:

    while i am restoring my database backup i got the following message
    “System.Data.SqlError: the media set has 2 media families but only 1 are provided.All members must be provided.”
    can you please help me??

    Reply

    • July 28, 2010 at 7:12 am, Sohaib said:

      hi,iam having a .bak file with size 2323KB.when i try to restore it
      the error msg comes like “to many backup devices specified for backup or restore;
      only 64 are allowed.restore database is terminating abnormally.”
      how can i restore it in sql2000.
      thank u in advance.
      Regards
      Sohaib

      Reply

      • July 30, 2010 at 11:29 am, 33ranjith said:

        hai,
        i used sql server 2005 in windows 7 to take backup.. Now i want to restore the database backup file in sql server 2005 in windows xp sp2…

        Plz some body help to me
        with regards
        Ranjith Daniel

        Reply

      • September 21, 2012 at 6:52 am, VIKI said:

        > Hi Sohaib….
        > Have you got any idea for your regards, if you have na share it… I’m waitting

        Reply

    • August 04, 2010 at 12:02 am, ntn said:

      Me too!
      I brought my bak file (file A) to other PC, error the same while other bak file (file B) using to restore nomal. Now, i want using database from file A, but restore error.
      Please help me!

      Reply

    • January 06, 2013 at 6:48 pm, Student said:

      > I am getting the same problem pls help me

      Reply

  18. August 30, 2010 at 1:12 pm, Kundanravi said:

    i want to restore more than one backup can u help me

    Reply

  19. October 11, 2010 at 10:19 am, Amit said:

    Hi all
    i have a query regarding the restoring a back and transactional log. I have a every 1 hour full back and if any crash between 1 hour,i want transactional log between last back up and failure time. How i can get that log. and how it will be restore again…

    Thanks in Advance
    Amit

    Reply

  20. November 12, 2010 at 8:47 pm, eggman said:

    Hi,

    What do I need to do when the “Restore” option is grayed out? What does this mean?

    Thanks much!!

    Reply

  21. November 13, 2010 at 10:43 am, Kamlesh said:

    hi, this is nice restoration technique through query wizard

    kamlesh bijarniya

    Reply

  22. December 14, 2010 at 4:58 am, Venkatreddy said:

    please mail me for above question to venkatreddy.jist@gmail.com

    Reply

  23. February 22, 2011 at 12:51 pm, Rajesh said:

    Hi, Is there anyway we can do retsore using a script/batch file? Thanks.

    Reply

  24. February 25, 2011 at 5:42 am, Priyapawase said:

    i need to restore my database in sql server 2005 and as i follow the procedure above there still an error. it shows this:

    “system.data.sqlclient.sqlerror:directory lookup the file C:program filesmicrosoft sql serverMSSQLDATASTUDENTS INFOREMATION.MDF failed with the operating system error 3(the system cannot findthe path specified.).(microsoft.sqlserver.express.smo)”

    im using windows 7 and visual studio 2008

    Reply

  25. April 30, 2011 at 2:11 am, ashpsky said:

    while restoring my bakup file in MS SQL 2005 it gives -

    System.Data.SqlClient.SqlError: Directory lookup for the file “c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAtest_infoguideDatabase.mdf” failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)

    this error

    Reply

    • July 26, 2012 at 1:43 pm, Sneha said:

      Me too findind this error while restoration…what does it means…plz suggest any suitable way to remove this

      Reply

  26. December 20, 2011 at 11:55 pm, krish said:

    hi
    i restore my database in sqlserver after restore default it display some path(D:\OTHERS\PROJECTS\TENDERMANAGEMENTSYSTEM\APP_DATA\TMSDATABASE.MDF) but i store my db in some other location..

    plz help me how to clear this error

    Reply

  27. December 29, 2011 at 4:08 am, Vinnu said:

    Hello

    sql server by default provides some path… u can restore ur db in ur own location jst click on that path and specify ur own path.

    Reply

  28. January 31, 2012 at 6:38 pm, unine said:

    Hi,

    How to move or change the default path of restore file?

    thank you,

    Reply

  29. February 01, 2012 at 4:48 am, nataraj said:

    while creating back up file in and restoring in same system is working

    But i want restore the file in other system

    so

    please help me ……………………..

    Reply

  30. March 01, 2012 at 6:00 am, Tony said:

    Hello,
    I backup my database from sql 2000 and restored it in sql 2005, everything was successful during the restore process. I then decided to open my software to continue my work, but anytime i put in the user name and password I get an error message like this “invalid object name dbo.userSubordinate”; it then says click continue. But after clicking continue, its abort operation.
    What do I do to get my software working? Please help.

    Reply

  31. July 26, 2012 at 1:41 pm, Sneha said:

    What this error means
    System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘Online Entertainment World’ database. (Microsoft.SqlServer.Smo)

    Reply

  32. August 21, 2012 at 6:40 pm, Triven said:

    Nicely done..

    Thanks

    Reply

  33. October 11, 2012 at 7:05 pm, Richard said:

    Hello!
    I have a probrem with step 11. When i click on add, there’s no .bak files. I made a new database, than i wanted to restore it, the bak files were missing. What should I do? I started a search in my machine but no results. What can i do to aear those files? Thank you!

    Reply

    • October 12, 2012 at 12:23 pm, David Kirk said:

      If you don’t have backup files, you can’t restore.

      Reply

  34. October 17, 2012 at 10:53 am, Adam said:

    I have found the article is very informative. I still remember when my database also got corrupted and I faced so many troubles to recover it. At that time my friend suggested me SQL Database Recovery Software. The software recovered my corrupted database & also gave me report of full recovery process. I can also recommend to all those users who don’t have backup of their database, they should use Stellar Phoenix SQL Recovery Software to get back corrupt database.

    Reply

  35. November 03, 2012 at 12:01 pm, yossi said:

    I followed the instructions and at the end of the process I received an error that says:
    “Restore failed for server “home”. (Microsoft. SqlServer.Smo
    Additional information
    System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All
    members must be provided. (Microsoft.SqlServer.Smo)
    The server where I made the backup is other then the one I wnat to restore the database and had a different instance name. The backup file is a one fila with “bak” extension.
    Any reason why it is not working for me?
    Thanks

    Reply

  36. January 13, 2013 at 7:34 am, nadeem said:

    thanx, with these option i am able to restore sqlsever 2005 database back inti sqlserver express 2008..

    Reply

  37. July 16, 2013 at 2:59 pm, Christian said:

    thank you!

    Reply

  38. August 20, 2013 at 4:07 am, salman said:

    System.Data.SqlClient.SqlError: Directory lookup for the file “C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\Online_Emailer_WithDataStorage.mdf” failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo
    how can i solve this

    Reply

  39. August 20, 2013 at 4:08 am, salman said:

    System.Data.SqlClient.SqlError: Directory lookup for the file “C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\Online_Emailer_WithDataStorage.mdf” failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo

    Reply

Leave a Reply