0

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.

0

2 Answers 2

0

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.

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .