SQL Server 2005: How To Restore a Database from a Backup
Contributed by Rob Rogers 
Tagged: Database 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.
You may also like -
SQL Server 2005: How To Perform a Full Backup of a Database
SQL Server 2005: How To Set Up Database Mail
SQL Server 2005: How to Attach (or Reattach) a Database Missing the LDF
SQL Server 2005: How To Enable xp_cmdshell
Symfony: Fix propel-insert-sql error: Can't connect to local MySQL server
Creating Databases the Quick, Easy Way With OpenOffice.org
SQL Server 2005: Easily Grant Select to All Tables and/or Views in a Database
SQL Server 2000 - Find Database Language
umesh said on November 15, 2008
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.
sundar said on November 24, 2008
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….
Himanshu said on January 28, 2009
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
How To attach database sql said on March 13, 2009
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
Shamsul said on April 17, 2009
Great step by step guidance. Worked a treat for me who is a complete novice
Osmar Mateus said on June 5, 2009
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
Sunil said on June 19, 2009
Really This was very helpfull to me.
Thank You!!
Thank You very much
R Biswas said on July 30, 2009
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.
David Collins said on September 25, 2009
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?
Jeff said on October 5, 2009
Hello!
How to restore with old name of PC and instances?
Anonymous said on December 8, 2009
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.
Anonymous said on February 3, 2010
Are the steps the same if the backup was done in SQL Server 2000?
Anonymous said on February 15, 2010
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.
Anonymous said on March 8, 2010
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..
Anonymous said on April 7, 2010
i do the same but one eror s came like
“the database s in use”
Thanks said on June 8, 2010
Thank you very much it works
Grinathela 1983 said on June 16, 2010
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 .
Mithun said on July 13, 2010
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….
priya jose said on July 16, 2010
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??
Sohaib said on July 28, 2010
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
33ranjith said on July 30, 2010
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
ntn said on August 4, 2010
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!
Kundanravi said on August 30, 2010
i want to restore more than one backup can u help me
Amit said on October 11, 2010
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
eggman said on November 12, 2010
Hi,
What do I need to do when the “Restore” option is grayed out? What does this mean?
Thanks much!!
Kamlesh said on November 13, 2010
hi, this is nice restoration technique through query wizard
kamlesh bijarniya
Venkatreddy said on December 14, 2010
please mail me for above question to venkatreddy.jist@gmail.com
Rajesh said on February 22, 2011
Hi, Is there anyway we can do retsore using a script/batch file? Thanks.
Priya pawse said on February 25, 2011
hi nhiel,
i m facing same problem….do u find any solution..
plz help me out…
thanx..in advance…
regards,
priya
Priyapawase said on February 25, 2011
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
ashpsky said on April 30, 2011
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
krish said on December 20, 2011
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
Vinnu said on December 29, 2011
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.
unine said on January 31, 2012
Hi,
How to move or change the default path of restore file?
thank you,
nataraj said on February 1, 2012
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 ……………………..