SQL Server 2005: How To Restore a Database from a Backup
Posted February 7, 2007 by Rob Rogers in 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.
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.
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.
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
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 .
How To attach database sql
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
Great step by step guidance. Worked a treat for me who is a complete novice
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
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)
Me too findind this error while restoration what does it means plz suggest any suitable way to remove this
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
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.