As everyone know till SQL Server 2000 there are four system database is
available and they are master, model, msdb and tempdb databases. From
SQL Server 2005 Microsoft has introduced a new system database to the
database engine called Resource database (actual name for this database is mssqlsystemresource ) which is hidden to the users.
What is the use of this Database?
Resource database is a read-only and hidden database which physically
contains all the system objects that accompanies with SQL Server. All
the objects under sys schemas are exists logically in each database. The
Resource database does not contain user data or user metadata. The ID
of the Resource database is always 32767.
The main purpose of Resource database is to make the update to the
server in a fast and easy way. Since all the system objects resides
under resource database, during the upgrade we can just over write the
resource database files with the new resource db files which will update
all the system objects exists in the database. In the old version of
SQL all the system objects need to be dropped and recreated at the time
of upgrade which is time consuming and the setup will fail if any one of
the object gives some problem.
By default the physical files for Resource database exists under the
default data directory <drive>:Program FilesMicrosoft SQL ServerMSSQL10.<instance_name>Binn. You can find two files named mssqlsystemresource.mdf and mssqlsystemresource.ldf in the directory mentioned above.
Can I backup the database?
It is not possible to backup the database using BACKUP t-sql statement
since the database will be in hidden state. Since backup is not possible
obviously you cant restore the database. We have only one way to take
the backup of this database is to copy the physical files of the
database. You can stop the SQL server, copy the physical files of this
database to another location and start the SQL Service. Now you can have
the backup of Resource database physical files, you can just replace
the physical files from the backup taken in case needed.
How to access Resource database?
I dont think there is any use in access this database for users, since
users are curious about whats inside this database Im writing this.
Since this is for testing purpose, please dont modify anything and
overwrite the resource database physical files. Accessing the resource
database is not possible in normal mode since the database will be in
hidden state. There are two ways to access this database.
Now you have attached the Resource database to sql server as a user database. You can now start exploring the database.
Running SQL Server in single user mode
In this method you can directly access the resource database however to
do this you need to start SQL server services in single user mode. Lets
access the database through single user mode.
Thats it start exploring the database. You can see from the above image
that the database is in read only mode.
Please note that this is for testing purpose only, please dont modify
anything in the database unless you know what you are performing or as
directed by Microsoft customer support services.
In normal mode we can get only two values for the Resource database. The
are version number and the last update done for resource database.
- Attaching Physical files
- Running SQL Server in single user mode
- Stop SQL Server Services
- Copy the physical files (mssqlsystemresource.mdf and mssqlsystemresource.ldf) of this database to another location. In this test Im going to copy the database to C:Temp folder
- Start SQL Server Services
- Attach the database as a user database (you can provide any name to the database) from the physical file copied.
1
2
3
| sp_attach_db 'Resource','c:\temp\mssqlsystemresource.mdf','c:\temp\mssqlsystemresource.ldf' |
Now you have attached the Resource database to sql server as a user database. You can now start exploring the database.
Running SQL Server in single user mode
In this method you can directly access the resource database however to
do this you need to start SQL server services in single user mode. Lets
access the database through single user mode.
- Start the SQL Services in single user mode.
- Now connect to the server and access the database as shown below.
Thats it start exploring the database. You can see from the above image
that the database is in read only mode.
Please note that this is for testing purpose only, please dont modify
anything in the database unless you know what you are performing or as
directed by Microsoft customer support services.
In normal mode we can get only two values for the Resource database. The
are version number and the last update done for resource database.
No comments:
Post a Comment