Restoring the system databases

Question

Restoring the system databases

Godin

Old Hand

Points: 331
More actions
May 14, 2007 at 1:55 pm

#118751

We are using SQL server 2000
We currently have all databases (systems and users) on the same RAID-5, acutally the T-logs are also on this RAID-5. We know that it would be better to seperate them but we are limited on disk space. Just wanted to know what might be the steps to recover if we loose the RAID. We do complete backups of systems and user databases + logs every night.
As a test, I removed the master datafile and log, started in single mode using sqlservr.exe -c -m, but I was not able to bring up SQL Analyzer to do the restore of master. When the master datafiles are lost, are we suppose to be able to restore them with a complete backup, or it needs to be rebuild first using rebuildm.exe ?
.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I am going out on a limb here, and say you probably need to rebuild the master database so you can start SQL and restore the backup of the most current master database to replace the rebuilt one.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 2

Execute rebuildm.exe first then sqlservr.exe -c -m. Once the bare bones instance is up you should be able to perform your restore of the master database from ISQL or ISQLw.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

We copy out our data and log files for master, msdb and model to somewhere safe. That way, we can use them to start SQL Server quickly after a file system restore without having to rebuild master. If you do this, don't forget to repeat it every time you apply a service pack or hotfix.

John

Godin Old Hand Points: 331 More actions · Answer 4

We are planning to do a FULL server backup every month or so.

At the same time we will backup the binaries and the data while the services will be down. I assume we can use this master, msdb and model to start the SQL server and then restore on top of the system and user databases, right ?

Thanks

Godin Old Hand Points: 331 More actions · Answer 5

Just to clarify , we are planning to do monthly server backups which would include the O/S, SQL binaries and SQL datafiles. We are also planning to do nigthly backups of DB datafiles and logs. So if we loose everything, can we just restore the O/S, SQL binaries etc .. Then restore the master data and log from the monthly backup, this should allow us to restart SQL Server, then restore the master from the nightly backup. Then restore the msdb, model and user databases from the nightly backup.

Would this be the best procedure if we loose everything

Thanks

Godin Old Hand Points: 331 More actions · Answer 6

Actually one more thing to add, if we restore the master data and log from the monthly backup, would this allow us to restart SQL Server ?

Or will we need to restore the master, msdb and model datafiles and logs (.mdf and .ldf) from the monthly backup to start SQL server and then restore master, msdb and model from the nightly backup and then the user databases from the nightly backup ?

Thanks again

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Yes, that is the way we do it. You can't restore any databases unless SQL Server is running, which is why you either need a copy of the data files to get you started, or you need to rebuild the master database.

So if you will be regularly stopping SQL Server to take a full backup, then you can proceed as follows in the event of a disaster:

(1) Recover your computer from your full file system backup (this will include the mdf and ldf files)

(2) Start SQL Server

(3) Restore the master database from your latest database backups

(4) SQL Server automatically restarts

(5) Restore all your other databases

John

Godin Old Hand Points: 331 More actions · Answer 8

John,

Thanks for the info

Last question on this subject, when we restore the .mdf and .ldf from the FULL monthly backup, should we only restore the master .mdf and .ldf or we also need the model and msdb .mdf and .ldf to start SQL Server ?

And once the System Databases are recovered with the nightly backups, should we remove the user databases .mdf and .ldf (that came from the monthly backup) or just restore on top of them using the nightly backup ?

Thansk again

Godin Old Hand Points: 331 More actions · Answer 9

Any feedback on this last question would be appreciated

Thanks in advance

Anders Pedersen SSChampion Points: 11410 More actions · Answer 10

Just restore on top of them. Generally is faster than recreating the files (I think in Windows 2003 this might no longer make a difference).

If you have a complete filelevel backup of your server I would do a complete restore of it, then restore the database backups over them.

Might want to add in a step to prevent users from getting to the databases until you have completely recovered all databases though.