June 20, 2011 at 5:52 am
Brandie Tarvin (6/20/2011)
george sibbald (6/15/2011)
To avoid this problem in the future, next time your instance is down take a flat file copy of the system database files to another directory (including the resource database). do this after every SQL upgrade.Then if the system dbs get corrupted you just need to slide the copies into place.
I wish it worked that easily, George. But the last time I checked, Master, at the very least, requires more hoops than that.
Have you actually tested this approach?
Yep, a number of times. It gets you started to do system db restores. Note I say to take another copy after every upgrade.
Try it.
---------------------------------------------------------------------
June 20, 2011 at 6:13 am
Another yes from me. When I lost the log drive (well, Ops mis-partitioned the drives), I hunted out a running Dev copy at the same Build, stopped it and copied the system database files over, and fixed one at a time. SQL Server should have a health warning about system databases and backups: intuitively a backup should be enough, but while it is necessary it is not sufficient for system databases. Rebuildm or (now) run setup and re-patch are over-complicated when you can fix things.
Can't speak for 2008 up - not yet used in anger.
June 20, 2011 at 6:16 am
Ewan Hampson (6/20/2011)
SQL Server should have a health warning about system databases and backups
That's what the DBCC commands and the Alert system are for. You should only have to worry about system db health if you're not doing regular, proper maintenance.
June 20, 2011 at 6:25 am
.........which is why I have flat file backups of the SAME server, then I don't have to take master DB files from other servers or worry about versions.
and remember the only way to back up the resource database is via a flat file copy.
all part of regular, proper maintenance 🙂
---------------------------------------------------------------------
June 20, 2011 at 7:04 am
Brandie Tarvin (6/20/2011)
Ewan Hampson (6/20/2011)
SQL Server should have a health warning about system databases and backupsThat's what the DBCC commands and the Alert system are for.
No argument with that, but I have been talking here about loss of a drive fulll of files, not corruption. I meant only that the procedure for restoring system databases can easily get more complicated than for User databases, and that an innocent SQL Server admin can see .bak files and not know that - and I'm entirely with george sibbald here - you really need flat file copies too.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply