Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I just want to know the steps on how to restore our SQL server 2005 in case its fail. I perform a backup of our database on our SQL server 2005 using SQL server management studio.

My concern is if the computer crashes what are the steps to restore our SQL database. Sorry because I'm not vert familiar with SQL server restores, as my previous work was as a LAN administrator.

If you could guide me or tell me all the steps starting on installing the SQL server 2005 connecting/configuring to our database name and using my back up on restoring our database. and please tell me what are the precaution on restoring our database.

Thank you so much.

share|improve this question

migrated from stackoverflow.com Nov 16 '10 at 14:19

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers

You are lucky because I just finished writing 2 articles on this specific issues. Check this post.

There will a part 3 for Log file removal as well these days.

share|improve this answer
add comment

Well for a start I hope the backup is going to a separate drive so that in the case of a failure it is still accessible and I will also assume that it is a single backup file with one database held within it.

First i would suggest running the following sql

RESTORE verifyonly FROM DISK = 'c:\MyBackup.bak'

This will just confirm that the backup is a valid backup. This step isnt necessary but its nice to know if your working with a valid backupfile in the first instance before spending time on the rest of it

then perform the next sql

RESTORE filelistonly FROM DISK = 'c:\MyBackup.bak'

This will give a list of the logical files used within the database. It will return at least two rows one for the mdf (data file) and another for the ldf (log file)

Finally run the following

RESTORE DATABASE MyDatabase FROM DISK = 'c:\MyBackup.bak' WITH RECOVERY, REPLACE, MOVE 'DATAFILE' TO 'C:\Directory\Data.mdf', MOVE 'LOGFILE' TO 'C:\Directory\Log.ldf';

Where c:\MyBackup.back is the location of your backup file MyDatabase is replaced with the actual database name and ofcourse you will need to change the DATAFILE and LOGFILE to be the appropriate names from the "restore filelistonly" script

Alternatively you can use the Wizard in SQL but my preference is to use the above method.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.