Sunday, November 27, 2011

0 Resource Database & System View

Since SQL Server 2005 release, MSFT has discontinue the traditional system tables such as sysdatabase which original inherit  from Sybase. It is being replace by the System view. The document here illustrate the map between the old style system table and new system view.

Along with the system view changes, Microsoft also said, there will be a new hidden database being ship with the SQL Server. Of course, for some of us, this statement is not enough to satisfy us. We like to poke around to see what it is. The initial default databases upon SQL server installation are Master, msdb, tempdb, model and resource database.

Resource database is used to store the system objects. Whenever, SQL server upgrade, apply fix pack, service pack, it will upgrade the resource database. Some says, we can roll back the fix pack by replacing the resource database file using the older fix pack upgrade. However, I personally have not try it it myself and not found much more information from internet if anyone have try it.

This resource database can not be view from SSMS, sp_helpdb and even the sys.databases has been explicitly design to exclude this. 

image

There are few ways we can get our hands on it.

Attached the database file.

Since every database has data file and log file. As long as we can find the file, rename it and re attach to the different name. We should be able to get inside the database. The actual data file of the resource database is under SQL Server binary installation directory (ex: Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\.)

image

  • Stop the SQL Server instance.
  • Copy the file to the new name and restart the SQL Server instance.
  • Attached the database
  1. USE master;
  2. GO
  3. CREATE DATABASE backup_resouceDB
  4.     ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\Binn\backup_mssqlsystemresource.mdf'),
  5.     (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\Binn\backup_mssqlsystemresource.ldf')
  6.     FOR ATTACH;
  7. GO

image

As you can see the screen shot, it is the read only database. Since it is the complete replicate of the actual resource database. It is safe to poke around if you like.

Access from Single user mode

open the cmd under administrator privilege and type the SQL Server executable path. 

"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Binn\sqlservr.exe" –m -sMSSQL2008R2

-m is for starting SQL Server from command line with Single user mode.

image

How to find the database id for the resource database

Beside to set the SQL Server to single user mode and run the select db_id under resource database. Another way to try is access from DAC and query the system base table.

  1. SELECT id, name FROM master.sys.sysdbreg

image

However, we can not get inside the resource database from the DAC. Another thing to be note is that the system base table only can be query from DAC or Single user mode. Query from regular session will receive the error.

Msg 208, Level 16, State 1, Line 1
Invalid object name 'master.sys.sysdbreg'.

How to find the resource database version and last update time

  1. SELECT SERVERPROPERTY('ResourceVersion');
  2. SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

image

How would resource database being used?

Since the resource database hold the actual system objects. Any system store procedure we run will actual being use from resource database.

  1. sp_who2
  2. go 10
  3. sp_helpdb
  4. go 10

Then lasts query the dm_exec_query_stats and dm_exec_sql_text.

  1. select x.dbid, x.text, a.creation_time, a.Last_execution_time, a.execution_count
  2. from sys.dm_exec_query_stats a
  3. cross apply sys.dm_exec_sql_text(a.sql_handle) x
  4. go

image

As you can see the sp_hepdb and sp_who2 we execute previously is involved in the db id 32767 which is resource database.  On the side note, the db_id shows up NULL is because it is ad-hoc query not store procedure.

Download the System View Map

Microsoft publish the good System View map. You can download it from http://www.microsoft.com/download/en/details.aspx?id=722 or you can buy the print version for left-brain.com http://www.left-brain.com/topics/topic/sql-server

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates