Restore of SQL 2K
-
Hi there,
we are attempting to design a restore procedure for our enterprise server and are running into trouble with the restore of the database (SQL 2K). I would be interested to know the methods others use to restore their SQL database. We have tried a TIVOLI backup of the database and then a restore which is failing.
In the past I would have reinstalled SQL and then overwritten it with a backup, or maybe reinstalled SQL and attached files from a flatfile backup of the sql files. There seems to be lots of information about restoring a single dbase out there, but what about restoring a complete SQL server? Anyone with the answers?
Thanks
Gordon
-
What we did in a DR environment was take our server backup and restored everything but the data files (we operate in a 24/7 environment which means there isn't a time period when the db files aren't open). We then used rebuildm.exe. from the command line (need a SQL Server CD) to rebuild default master and msdb databases. We restored those two databases from backups, then restored the backups of the user databases. It cut quite a bit of time off because SQL Server with respective service packs didn't need to be reinstalled.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
I've done a few restores at IBM's location and we install the servers and then restore from the backup file. This has worked well and in the event of a disaster, IBM has the SQL config so they can preinstall the server.
Steve Jones
-
We took the same approach as Brian with our DR tests and I must say it was plain sailing all the way.
We also did a couple of backups with the services down to compare the recovery time, and I have to say that the saved time was not worth the moans and groans from the users about not having the system 24/7.
-
bkelly,
they are currently doing what you suggested I think, but when they run rebuildm they get "Error -1". They are taking the complete backup tape and applying it to a server without OS or anything. Everything comes from the tape. I think that the rebuildm error is a result of the new server not being on the network and so authentication is an issue. But there has to be a better way to do it than this. The OS is W2K Adv Server and SQL Server 2000.
-
Gordon,
Check Q273572 re: that 'error -1' using rebuildm. I ran into the same thing testing our DR plan. It's a read-only issue, since the rebuild data files on CD-ROM are flagged RO. Copy the \x86\Data folder somewhere and attrib *.* -R in that folder, and point rebuildm at that instead of the CD.
I'm interested to know what sort of restore script you folks are using (for those who aren't using a third-party backup agent to backup databases directly to tape, that is) for disaster recovery. I'm using Maintenance Plans to backup my dbs to disk; the resulting files are backed up to tape. Works great, but the filenames include a timestamp, and there's enough variation in database size to guarantee that some of the files will have a different timestamp from day to day. That means my restore script requires manual editing before it can be applied--not the kind of thing I want to be doing at 3AM, worst case scenario.
I can't find a way to suppress the timestamp, nor can I find a command-line utility that could be scheduled to truncate that portion of the filename. Any thoughts?
Thanks,
David
-
In most cases we've built our own backup jobs and scripted names based on hour or on weekday using ActiveX (VBScript) Scripting, so when we go to a restore situation, we've been faced with, "Restore it from day before yesterday and we'll suck up the losses." As a result, we're having to manually pick and choose the right backup in any case. We keep several days' worth on disk to prevent having to get a tape from off-site. So for us it's still a pretty manual process on the restore.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Hi all.
Is there any Disaster Recovery document that describes the best ways to recover a database or a server?
Thanks
Any Help Appreciated
-
I searched for articles and FAQ's on disaster recovery, but found very little info. Microsoft has a collection of articles in Q307775, which are all potentially useful, but it lacks a start-to-finish overview of the process. Lacking any such info, I forged ahead and tried to figure it out myself.
I ended up with roughly what Brian describes. Full system backup, including registry (using BackupExec 8.x), but excluding the actual .mdf/.ldf files in SQL. Those I backup in SQL, and the resulting backup files are backed up to tape by the nightly full backup. In addition to backing up all of the current databases, the backup script I use also creates/saves a script that will restore all of the databases it just backed up. That makes a full restore much simpler; once the master database is restored, I can load the restore_<day>.sql script and run it. My goal there was to enable coworkers less familiar with SQL to perform a full recovery.
To restore: restore the tape backup, run rebuildm.exe (since there are no database files yet, and SQL won't even start), start SQL in single user mode, restore master, restart SQL and restore all other dbs.
In theory it's simple 🙂 In practice, it's not--especially using BackupExec. As far as I can tell, practice is really what disaster recovery is all about. I found a nifty, cheap piece of hardware that really helped in my test lab. It's called a TRIOS (from Romtec) and it allows you to switch between multiple bootable IDE drives. I used it to simulate a disk failure, without actually losing the sample system I'd configured. After installing a fully functional system on disk 1, back it up to tape, then switch the Trios to disk 2, which is blank, and attempt a restore. When it fails (like it did so many times) there's no need to rebuild the system from scratch, just switch back to disk 1, tweak the backups, make another one and repeat. You could do the same thing by manually swapping drive cables, certainly.
--David
-
David thanks for the response.
I'll check out the Q307775 articles and
The restore script that you've mentioned sounds like a good idea.
Practicing is a bit scary for me since i don't really have anyway of testing a disk or power, network failure disasters.
But i'll defiently be ready.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply