MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1544

I'm confused as to why I need to worry about backing up the resource database for DR purposes. In BOL under How to: Restore the master Database (Transact-SQL) I don't see any mention of the resource database. How would a backup be useful for anything other than upgrades?

I'm also curious about the applying and rolling backup upgrades and/or service packs. According to BOL:

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of the Resource database with the older version.

Seems a little too simple to me. I'm pretty sure service packs do a lot more than update system tables (e.g., upgrade dlls, executables, etc.). I'm also not clear on what is meant by an "upgrade". Does this refer to only service packs  as specifically mentioned in the text, or are cumulative updates and security patches included in this definition as well? These make modifications outside of the database for sure.

If anyone can clear this up for me I'd appreciate it.

 Aaron

The Resource database is required by SQL Server 2005 service to start and it has to be in the same location as your master database files.  Try it out. Stop the service and rename the two database files.  Then, start the SQL Server 2005 service.  You will notice that the service will not start. I agree that it seems a little too simple but service packs are the ones that changes a lot of system objects.  Security patches only change DLLs, EXEs and some other files, in general.

Imagine that you have a failed disk that contains your system database files. If you have to rebuild and restore the master database, you need to have the Resource database files on that same disk, otherwise, your SQL Server service won't start.  Check out this MSDN article about the Resource database

 Excellent Informatio compiled together. 

But I wanted to know can we really restore master database to a different location than original.

 I tried to Restore the master database from Drive R: to Drive C:, the restore went perfect but then i could not start the SQL Server as it was unable to find R:\...\mssqlsystemresource.mdf and R:\...\mssqlsystemresource.ldf for SQL Server 2005 and R:\...\model.mdf and R:\...\msdb.mdf for SQL Server 2000.

In my opinion, the reason behind this is master can be restored from R: to C: but the pointers to msdb/model/resource databases inside master are still referring to R:.

 I would like to know if there is a chance to change these pointers too while restoring the master database.

 Thaks

Sri

The disaster recovery process for restoring the master database is quite complex especially if you are restoring to a different drive.  What I normally do is that if you are moving the master database to a different drive letter - say, from R:\ to C:\ - I would keep the other system databases on the original drive until such time that SQL Server is able to restart successfully. This is because the pointers to the old drive/path still exist. Once SQL Server managed to restart successfully, I restore the other system databases - msdb, tempdb, model(if necessary) - on the new drive target.  The problem arises when the old drive is no longer accessble - such as a hardware failure or a totally inaccessible server.  There are a couple of ways to do that.  One is to emulate the old drive - stick in a USB drive, change the drive letter to the old one and restore the databases. Another way is to change the startup parameters. You can change the startup parameters to point to the new location of the master database files but make sure this is where your Resource database files whould be as well