SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The Resource Database

I saw a post recently that asked about backing up the Resource database. The person said they just realized that it wasn't included in their backup scheme and was worried.\

Don't worry, you don't need to back it up.

The page at MSDN describes the Resource database as essentially a large lookup table(s) for the SQL Server itself. It can find the system objects that are used: tables, views, DMVs, DMFs, etc. When the server is upgraded, a new copy of the Resource database is copied over. The objects don't have to be built from scripts with DROPs and CREATEs.

This means, however, that each time you patch your server, you potentially could be copying over a new version here if some system object changes. That means that you don't really want to back up this database.

If you are restoring to a new server, when you install that server, it will have the correct copy of the Resource database for your system. If you were to copy over an incorrect version, the system objects might not match up with the system code in the SQL Server executables and DLLs. That would be bad.

I haven't seen notes about the Resource database becoming corrupted, but it could. In that case, I'd suggest you copy the files from another SQL Server of the same version, or install SQL to another machine, patch it up, and then copy the files to your damaged installation. That could be a problem, but it does mean one other thing.

You ought to be tracking your SQL Server version and build on a regular basis as part of your monitoring.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by bhushans.dhanke on 9 April 2014

why resource database kept hidden from user?

Posted by Steve Jones - SSC Editor on 9 April 2014

I don't have an official answer, but my guess is because they don't want users to do anything in it.

Leave a Comment

Please register or log in to leave a comment.