Jan 062012
 

For many reasons, you may run into a situation that resource database of SQL Server must be moved to a different location. There are many articles you can find through Google and in BOL such as http://msdn.microsoft.com/en-us/library/ms345408.aspx telling you how to do it. After going through steps indicated in the article, you may sucessfully move your resource database, however, you may fail to do so. This happened to one of my customers. I am going to provide a new way to relocate resource database as a last resort and you can use this way when you are experiencing the same.

Before doing any actual work, run following query to find out the location the SQL Server assesses to get the location of the resource database.

select name, filename from sys.sysaltfiles where dbid = 32767
name  filename
----- -----------------------------------------------------------------------------------------------
data  C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.mdf
log   C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.ldf

(2 row(s) affected)

The result of the query shows that my resource database is in the default location. Now I am going to move it to Binn\resource folder from default location. Now let’s click on Display Estimated Execution Plan of previous query.

It shows that the data retrived from view sys.sysaltfiles is originated from sys.sysbrickfiles system table. In SQL Server 2005, you will see different plan where the base table is poining to name sys.sysdbfiles.
Now let’s re-connect to SQL Server with Dedicated Admin Connection ( In SSMS, you can put admin:ServerName on logon dialog) and run

select lname, pname from sys.sysbrickfiles where dbid = 32767
lname   pname
------- -----------------------------------------------------------------------------------------------
data    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.mdf
log     C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.ldf

(2 row(s) affected)

Now we are going to change the location from Binn folder to resource folder

  1. Shutdown SQL Server: net stop MSSQLSERVER
  2. Copy the resource data file and log file to proper location. It’s to resource folder in our case.
  3. Start SQL Server in single user mode optionally with console mode: sqlservr.exe -c -m
  4. Back to SSMS and connect to the server with DAC and run
    update sys.sysbrickfiles set pname = replace(pname,'\Binn\', '\Binn\resource\')  where dbid = 32767
    select lname, pname from sys.sysbrickfiles where dbid = 32767
    shutdown
    
    Warning: System table ID 22 has been updated directly in database ID 1 and cache coherence may not have been maintained. SQL Server should be restarted.
    
    (2 row(s) affected)
    lname   pname
    ----------------------------------------------------------------------------------------------------------------
    data    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\resource\mssqlsystemresource.mdf
    log     C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\resource\mssqlsystemresource.ldf
    
    (2 row(s) affected)
    
    Server shut down by request from login Test\john.
    SQL Server is terminating this process.
    
  5. Rename the resource data and log files at default location
  6. Start SQl server Services in normal mode.

Now you can verify it by the first SQL statement posted here. This is only recommended when you are not able to relocate resource database by official approach.

  3 Responses to “New Way to Relocate Resource Database”

  1. Hi John

    For SQL Server 2008 and 2008R2 the offical statement from Microsoft (http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.100).aspx or http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.105).aspx) is “The database cannot be moved”.

    The entry for 2012 RC0 indicates that this has not changed.

    Only for SQL Server 2005 are there any steps provided for moving the Resource Database and these cone with the cavet “The Resource data and log files must reside together and must be in the same location as the master data file.”

    Will making the change that you suggest still leave the instance in a supported state?
    Also if this is done on a SQL Server 2005 instance does this still have to maintain same location with master?

    The information is interesting but I am interested in the risks involved.

    Regards

    Patrick Flynn

    • Good questions Patrick. I can’t answer any of them. Your first one, I don’t know as I have never been working @ Mcrosoft. The seconds one, since I don’t have SQL Server 2005 instance, thus, I don’t have the answer handy. As to your last statement, the risk has to be from test ( or live system if it’s a such unfortunate), the reason it that this is not a official approach to change the location of resource database. Defintely this is not something should be done for every task but It might be helpful in very rare cases

  2. In answer to the question concerning changing the resource database via this method, it will leave you out of support with Microsoft. Making changes to the system base tables should not be done according to Microsoft documentation http://msdn.microsoft.com/en-us/library/ms179503.aspx

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.