System Database - MODEL corrupted

Question

Post reply

System Database - MODEL corrupted

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

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.

---------------------------------------------------------------------

Ewan Hampson SSCrazy Points: 2780 More actions · Answer 2

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.

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 3

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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

george sibbald SSC Guru Points: 104210 More actions · Answer 4

.........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 🙂

---------------------------------------------------------------------

Ewan Hampson SSCrazy Points: 2780 More actions · Answer 5

Brandie Tarvin (6/20/2011)

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.

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.