Restoring System and User DBs

We are using SQL Server 2000, all the System and User Databases and Logs are
on the same RAID-5 disks. I know this might not be recommended but this is a
fairly small system the Database is about 5G. And that's the only disk space
we have.
.
We do a complete backup of the System and User databases every night
We also backup the Logs from the User databases every night on tape.
.
If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
then we would have to restore the System and User databases. The User
databases would be a normal DB restore + the Transaction logs backup, we kno
w
that we could not recovery up to the point of failure since the current logs
would also be lost.
.
But what would be the steps to restore the System Databases, ex: Master ?
I was reading some of the notes and they metionned that we would have to do
a startup in single user mode, then restore the Master datafile.
So as a test I shut down the servive, renamed the Master DB and logs, tried
to restart the service, it failed which is expected. Then tried to start
using
sqlservr.exe -c -m, but this also failed with error opening master.mdf
.
So if the master file and logs are gone, do I have to use rebuildm.exe
first,then resotre the master backup, or did I miss a step ?
.
Also as another test I rename the model datafile and it's log,when I use
sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
and other databases were started except for model, but when I tried to
access SQL Analyzer I kept getting an error SQL server does not exist or
access denied
.
Basically I'm trying to test our recover strategy if we loose the RAID,
which would mean that we loose all datafiles and logs and would have to
recover from the backup done the night before.
.
Any feedback on the proper procedure to restore the System Databases are
welcome
.
Thanks in advanceHere's a good starting point for restoring system DBs:
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
Master remembers where all of the databases are, so after restoring it you
may see user databases show up as Suspect. Don't Panic (Thank you Doug
Adams for that lovely phrase)
"SQL Server newbie" wrote:

> We are using SQL Server 2000, all the System and User Databases and Logs a
re
> on the same RAID-5 disks. I know this might not be recommended but this is
a
> fairly small system the Database is about 5G. And that's the only disk spa
ce
> we have.
> .
> We do a complete backup of the System and User databases every night
> We also backup the Logs from the User databases every night on tape.
> .
> If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> then we would have to restore the System and User databases. The User
> databases would be a normal DB restore + the Transaction logs backup, we k
now
> that we could not recovery up to the point of failure since the current lo
gs
> would also be lost.
> .
> But what would be the steps to restore the System Databases, ex: Master ?
> I was reading some of the notes and they metionned that we would have to d
o
> a startup in single user mode, then restore the Master datafile.
> So as a test I shut down the servive, renamed the Master DB and logs, trie
d
> to restart the service, it failed which is expected. Then tried to start
> using
> sqlservr.exe -c -m, but this also failed with error opening master.mdf
> .
> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup, or did I miss a step ?
> .
> Also as another test I rename the model datafile and it's log,when I use
> sqlservr.exe -c -m to start the sqlserver, the log indicated that the mast
er
> and other databases were started except for model, but when I tried to
> access SQL Analyzer I kept getting an error SQL server does not exist or
> access denied
> .
> Basically I'm trying to test our recover strategy if we loose the RAID,
> which would mean that we loose all datafiles and logs and would have to
> recover from the backup done the night before.
> .
> Any feedback on the proper procedure to restore the System Databases are
> welcome
> .
> Thanks in advance|||Thanks for the info.
.
But my question remains, if the master.mdf and it's log are removed, can we
just restore from a previous backup or we have to rebuilt it first using
rebuilm.exe
and then restore.
Thanks
"James Luetkehoelter" wrote:
[vbcol=seagreen]
> Here's a good starting point for restoring system DBs:
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Master remembers where all of the databases are, so after restoring it you
> may see user databases show up as Suspect. Don't Panic (Thank you Doug
> Adams for that lovely phrase)
> "SQL Server newbie" wrote:
>|||> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup,
Correct. If you can't start SQL Server, it is difficult to have SQL Server p
erform a restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in mes
sage
news:94A87069-D833-4C84-8765-DF0D843A5718@.microsoft.com...
> We are using SQL Server 2000, all the System and User Databases and Logs a
re
> on the same RAID-5 disks. I know this might not be recommended but this is
a
> fairly small system the Database is about 5G. And that's the only disk spa
ce
> we have.
> .
> We do a complete backup of the System and User databases every night
> We also backup the Logs from the User databases every night on tape.
> .
> If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> then we would have to restore the System and User databases. The User
> databases would be a normal DB restore + the Transaction logs backup, we k
now
> that we could not recovery up to the point of failure since the current lo
gs
> would also be lost.
> .
> But what would be the steps to restore the System Databases, ex: Master ?
> I was reading some of the notes and they metionned that we would have to d
o
> a startup in single user mode, then restore the Master datafile.
> So as a test I shut down the servive, renamed the Master DB and logs, trie
d
> to restart the service, it failed which is expected. Then tried to start
> using
> sqlservr.exe -c -m, but this also failed with error opening master.mdf
> .
> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup, or did I miss a step ?
> .
> Also as another test I rename the model datafile and it's log,when I use
> sqlservr.exe -c -m to start the sqlserver, the log indicated that the mast
er
> and other databases were started except for model, but when I tried to
> access SQL Analyzer I kept getting an error SQL server does not exist or
> access denied
> .
> Basically I'm trying to test our recover strategy if we loose the RAID,
> which would mean that we loose all datafiles and logs and would have to
> recover from the backup done the night before.
> .
> Any feedback on the proper procedure to restore the System Databases are
> welcome
> .
> Thanks in advance|||The SQL Server BOL have explicit steps for restoring all system databases,
including after a severe master failure. There are a number of SQL 2000 DBA
books that walk you through it too. I also recommend having an expert
either assist or at least review your disaster recovery plan. Holy Sh-t
time is NOT when you want to find out you don't have something set
up/documented correctly. :-)
TheSQLGuru
President
Indicium Resources, Inc.sql