On any instance you have at least 5 system databases. Master, model, msdb, tempdb and one other. First 5 points for this homework. What is the other system database that is required on any instance?

I’m assuming that when you take backups you are making a point of backing up your system databases. If not, then you’d best start. Here’s what I’d like you to spend some time doing this month.

• Create a test instance that you won’t be upset if it gets fried.
• Back up your system databases.
• Recover a system database (15 pts each) (x5, only the required system databases)
  • Stop your instance
  • Delete the files for one of the system databases.
  • Get your instance back up and running.
• Move a system database (5 pts each) (x4, only the required, visible system databases)
  • This could be a different directory, or a different drive. The only rule is that when you are done none of your system databases are in the original location.
  • It’s highly recommended that you don’t move that other database. Why not? (5 bonus points)

Note: This is one of those homeworks that’s really important. Make sure that you know how to do this. In case it wasn’t obvious, what we are doing is practicing in case of a catastrophic failure of one of the system databases, or the drive where the system databases exists.

The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'. 

But let’s face it, luck runs out. So you may want to know what on earth this mysterious fifth system database is. As of SQL 2005 the source code for the system stored procedures, functions views etc. is stored in a resource database. If you guessed that the resource database is called mssqlsystemresource you would be correct. So why would Microsoft do this? Well it makes upgrading/patching SQL Server a much simpler task. Now instead of dropping and re-creating each of the updated system objects, which among other things can have some permissions implications that have to be dealt with, Microsoft just has to replace resource database.

Now if you look at my example above you might start thinking “Ok, sysobjects is now in mssqlsystemresource. So how come when I do my select it’s still master.dbo.sysobjects?” Simple enough. The objects within mssqlsystemresource are basically hidden behind the master database, and possibly the other system databases though I’m not certain about them. So in the case of sysobjects any reference to it is done through master not mssqlsystemresource. So to grant permissions on the sysobjects view you would grant it through master.

The next most common question that I see about this database (after where do I grant permissions) is how do I back up and restore it. Well, it can’t be backed or restored up using normal database backups and restores. If you want to back it up then you will need to do a file copy (or backup). This also means that restores are done by copying the file back into place (or do a file restore). If you have to do this make sure that the copy you are putting back in place is the same version (SPs, CUs etc) or you will need to re-patch your instance. So for example when you patch your instance you may want to make a new copy of the mssqlsystemresource files.

So what do you do if you find that your system resource has been deleted, corrupted, or didn’t get restored as part of a DR and you find you don’t have a backup? (Two of the previous have actually happened to me. I’ll let you guess which.) You now have two options. First and easiest, copy the resource database files from another instance that is the same version, service pack, cumulative update etc. Next (and last resort) is similar to what I discussed about the Model database. Use the REBUILDDATABASE option of setup. Here is the BOL link for 2012. It appears to be similar if not the same as the 2008 R2 BOL: http://msdn.microsoft.com/en-us/library/dd207003.aspx. If you use this method be forewarned that it over writes ALL of the system databases. Once you are done you will need to restore your master, msdb and model databases, and re-patch your instance in order to get back to where you need to be.

So see, now that you know more about the resource database, those of you who obsess about DR (most DBAs I’ve met) have one more thing to stay up nights worrying about.

You’re welcome.