Disaster Recovery (RESOLVED) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Disaster Recovery (RESOLVED)

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-06-30 : 13:41:15

A clients SQL box has gone a bit weird. There was a power cut last night and the registry was scrambled. They are shaping up to admit there was a hole in their backup procedure :-(

The machine has come upright with an old copy of the registry. All the files are there (probably undamaged), so there are MDF/LDF or BAK files of master, msdb, model and the database itself.

I've never had to restore "jobs", "logins" and the like before - so assuming the machine gets reinstalled what's my best route to getting it back to how it was?

Thanks

Kristen

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-06-30 : 16:24:36

What's the order again? Restore Master, then MSDB, then Model... Then it kind of depends on the state of your user databases.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me though the forum.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-30 : 16:31:34

If you have the MDFs and LDFs and hopefully these files were closed when they were grabbed, then after the install is done with the exact same data paths, stop the mssqlserver service, replace the existing database files with your copy, start up the service.

I've done this method several times.

Tara

Kristen
Test

22859 Posts

Posted - 2005-07-01 : 01:16:11

I copied the MDF/LDFs once the machine rebooted. SQL had failed to start, so the files were "closed" at the time, but may have been in "undcertain state" when the power cut occurred - but I figure that's no different to SQL resolving a mess like that after a power cut that didn't trash the registry!

"I've done this method several times"

Beats the hell out of backups, eh?

I'll make sure to check out exactly what the file paths are if we reinstall, thanks for that.

So, if that doesn't work and a RESTORE is needed I'll go with Sean's sequence.

Thanks

Kristen

Kristen
Test

22859 Posts

Posted - 2005-07-01 : 06:47:10

OK, the registry is repaired, I'm running DBCC CHECKDB on all DBs,

first up is this one:

DBCC CHECKDB('msdb')
WITH ALL_ERRORMSGS

Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:1098) with latch type SH. sysindexes failed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What action do you suggest I take on this one pls?

Kristen

Kristen
Test

22859 Posts

Posted - 2005-07-01 : 08:15:00

I restored the most recent backup, looks like the error has been there a while:


RESTORE DATABASE KBM_TEMP
	FROM DISK = 'D:\MSSQL\BACKUP\msdb_db_200506290200.BAK'
	WITH
		REPLACE,
		RECOVERY,
		STATS = 10,
	MOVE 'MSDBData' TO 'D:\MSSQL\DATA\KBM_TEMP.mdf', 
	MOVE 'MSDBLog' TO 'D:\MSSQL\DATA\KBM_TEMP.ldf'
GO

gives:
...
90 percent restored.
100 percent restored.
Processed 11992 pages for database 'KBM_TEMP', file 'MSDBData' on file 1.
Processed 4 pages for database 'KBM_TEMP', file 'MSDBLog' on file 1.
Server: Msg 823, Level 24, State 2, Line 3
I/O error (torn page) detected during read at offset 0x00000000894000
in file 'D:\MSSQL\DATA\msdb.mdf'.

Connection Broken

Same thing for oldest backup (2nd June). The database I've restored into is NOT marked as SUSPECT, so I can see the tables etc ... and DBCC CHECKDB shows no errors ...

Kristen

Kristen
Test

22859 Posts

Posted - 2005-07-01 : 08:16:12

Aggghhhhhhhhhhh .... just re-read the error - so its trying to store the details of the RESTORE into the existing MSDB database, and that's giving an error ... so perhaps I should just go ahead and restore MSDB ??

Kristen

Kristen
Test

22859 Posts

Posted - 2005-07-01 : 09:08:42

Can I just stop SQL Agent and restore MSDB?

I will lose the logging info of any Backups / Restores since the last backup, which means they won't be deleted automatically (Maintenance Plan) I presume.

Are there any other side effects of doing this I should be aware of?

Thanks

Kristen

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-07-01 : 09:26:32

Step back from the computer, close your eyes and take a deep breath and tell me what's going on.

Have you tried to restore the MSDB already? I thought I advised that you restore master and msdb before restoring the user databases. What happened?

You will lose all job history data since the msdb was backed up and any alerts, DTS packages stored in SQL server, operators (I think) defined since that backup.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me though the forum.

Kristen
Test

22859 Posts

Posted - 2005-07-01 : 09:52:41

The only database that had errors was MSDB, so I restored MSDB to a temporary database to see if the integrity of the backup was good. It is (although I was confused by an error message at the end of teh restore - but that was just SQL trying to log the restore in the, broken, MSDB.

So now I will restore MSDB - no jobs/alerts/DTS packages have been added/modified, so I'm OK on that, but thanks for pointing out that they were "at risk".

The Job History I guess the client can do without ....

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-01 : 12:44:36

quote:
Originally posted by Kristen

"I've done this method several times"

Beats the hell out of backups, eh?

I use this method for moving databases from one server to another. As long as the file paths are the same, it's a simple xcopy. I've also used this method when we've needed to expand a non-dynamic SAN drive.

Tara

Kristen
Test

22859 Posts

Posted - 2005-07-01 : 14:00:38

I can see the benefit. A new server rollout for us is a rare event, otherwise sounds we could use that trick for getting a complete system upright quickly.

I've restored MSDB and the jobs are all running OK, best as I can tell, so looks like we're cooking on gas again.

Thanks for your help folk.

Kristen

Kristen
Test

22859 Posts

Posted - 2005-07-01 : 14:02:53

Actually, sorry most post-padding, how can the MSDB have got corrupted?

I thought SQL would re-play transactions as necessary as part of a power-fail-recovery.

Should I worry that the RAID controller is caching some stuff and SQL doesn't actually know when stuff has physically made it to the disk?

Or is SQL not as smart as I think, and this sort of thing "normal"?

Thanks

Kristen

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-07-01 : 15:10:18

A lot of raid controllers these days come with a battery backup, so they have a minute or two to write those last-minute changes.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-01 : 15:31:53

SQL Server does rollback and rollforward transactions when it starts up. You'll see this in the Error Log. The most typical cause of a corrupt database is disk issues. Run whatever diagnostic tests you can on them and check for any errors in the Event Log.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources