In this article we will discuss about
SQL Server resource database.
Resource DB is a system database. It is
a read-only/hidden DB that contains all the system objects that are included
with SQL Server. SQL server system objects (sys.objects) are physically
persisted on resource DB but they logically appear in the sys schema of each
database.
- Resource database is introduced from SQL server 2005 version.
- SQL Server cannot back up the Resource database
- Resource database does not contain user data or user metadata.
- Resource DB ID is always 32767.
- Name of Resource DB is “mssqlsystemresource”.
Physical location of
Resource DB:
The physical file names of the Resource
database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are
located at <drive>:\Program Files\Microsoft SQL
Server\MSSQL11.<instance_name>\MSSQL\Binn\.
Note: Each instance of SQL server will
have only one mssqlsystemresource.mdf file and will not share this file.
Purpose of Resource
DB:
Using resource DB, upgrading the SQL
server version will be easier and faster. In earlier version of SQL server
while upgrading the versions it requires dropping and creating system objects.
But now with the use of resource DB, we can do upgrade of SQL server versions
easily by copying the single resource DB file to server machine.
Backing up and
restoring the Resource DB:
SQL Server cannot back up the Resource
database so we have to do manually. To back up this database stop the SQL
server copy the physical files to another location of disk then start the
service again.
To restore the Resource DB, create a
new database and attach the files with below query.
|
sp_attach_db 'd:\mssqlsystemresource.mdf','d:\mssqlsystemresource.ldf'
|
Caution: you must be careful not to
overwrite the current Resource database with an out-of-date or
potentially insecure version of resource DB backup.
The other important parameters which
are associated with Resource database are
- Version number of the Resource database – to obtain this value use SELECT SERVERPROPERTY('ResourceVersion');
- Last updated date of Resource database – to get this value use SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
