November 25, 2002 at 7:15 am
Using MS SQL Server 2000
I had a huge disaster last Wednesday. When we recovered the hardware and I tried to restore from tape, I found that Veritas Backup Exec 8.6 doesn't work with SQL Server 2000 UNLESS you have a specific upgrade and a hotfix applied. Even if you applied those, any tapes made before that time can't be used. BUMMER!. Luckily, I had started to try a new method. Backed up files to another drive using SQL Server's backup method and then copied those to tape. I tried to do a restore using SQL Server's method and apparently did something wrong.
Is there an order that databases need to be restored?
I restored the master first, thinking that since it has the schema, etc. that's the one I needed to get done first. But when it was finished, it 'broke' single user mode and gave errors that the other databases could not be found.
Also, should I be using WITH RECOVERY after each database is restored? Or should I only use it on the very last database?
6 days down - 41 hours overtime and counting.....
-SQLBill
November 25, 2002 at 7:41 am
when i understand correctly
first put server in single user mode
From a command prompt, enter:
sqlservr.exe -c -m
then restore the master database.
after the master is restored you can restore the other databases.
only when you have also transaction logs that needs to be restored with an database you use with norecovery. For example if you have backup1 and 2 transaction logs for db1 then you use with norecovery for the backup1 and the fisrt transaction log and with recovery for the last transactionlog.
As far as i know the order for the resore of userdatabases is not important.
November 25, 2002 at 7:56 am
Okay, so the order isn't necessary. That's what I've been hearing on other sites also. However, I put Master in single user mode (I knew I needed to use the command that you suggested). When I restored the Master, it went back to multi-user mode and couldn't find any of my other databases (I hadn't restored them yet) and locked everything up. I couldn't start services so that I could restore the rest of the databases.
Also, let's say my database tree is like this:
Master
Model
MSDB
Northwind
Pubs
MyDatabase
TEMPDB
When I reinstall SQL Server I have:
Master
Model
MSDB
Northwind
Pubs
TEMPDB
Does it matter that MyDatabase doesn't exist yet in name? I don't think it should matter, that would defeat the purpose of doing restores.
-Bill
November 25, 2002 at 8:02 am
Restoring master also restores the location and names of the other databases, if they do not exists then it should just set the to suspect. And from there you restored the backups and logs.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply