How to update system tables SQL 2008 & 2012 (FIX: “Ad hoc updates to system catalogs are not allowed”)

I came across an old version update that was enabled for all SQL 2005 + versions, meaning: update on system tables. In older versions, there was the possibility to update system tables, thus allowing you to perform some administrative tasks/fixes without having to shut down the server.
I agree that updating system tables are not meant to be done by an usual user, and therefore hacking SQL should be done only by Database Administrators and by those who have enough knowledge to not ruin everything and put the production on hold :) yes… that D smile is on my face now.
So, let’s assume I have restored the master database on a new machine and I don’t have enough space on C drive or I don’t want to use it, and want to put the databases on a SAN. Drive C was used by all user databases from the old server. In order to attach the databases rather that perform a restore on each one of them from backups (reasons: time consuming, backups are not up to date, backups don’t exist), I have to change the physical path for all user databases inside master database to point to an existing location with enough space, so that at the next SQL startup the databases will all come online as well. In this way, you can even change the databases storage by putting data files on a separate drive from the log files.

Before I go on, I am strongly reminding that this method is not documented and should be used very carefully, and only by those who feel confident in their knowledge. Backup to master database is a must before every hack on it.

These said… In past SQL 2000 version, the update should have been done on 2 system tables contained inside master database:

  • sysaltfiles – contains the physical location of each data / log file for each database and
  • sysdatabases – contains the physical location for each data file from each database.

Today in SQL 2005 – 2012 versions, these 2 tables have been kept for backward compatibility as system views. SQL recommends that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views.

To spare you from research, sysaltfiles has been replaced by sys.master_files and sysdatabases by sys.databases.

A simple select would show that all of these are views.

use master
go
select name, xtype from sysobjects
where name in ('sysaltfiles','sysdatabases','master_files','databases')
order by name 

 

I want to update the system table which is on top of above system views. How do I know which one?
In order to gain access to all system tables/views you need to open a DAC connection from SSMS. So go on and open a DAC connection for future queries. Don’t use the “Connect” option to make the DAC connection from SSMS but “NEW QUERY”.

I will limit myself and run the select only on sysaltfiles and sysdatabases to look at their execution plan.
I assume that master_files and databases have the same referenced tables.

 
select name, filename from sysdatabases 

For this query SQL will actually query sys.sysdbreg and sys.sysbrickfiles. In sys.sysdbreg SQL stores the database names and some additional information, but only sys.sysbrickfiles table is used as source for filenames location.

 
select name, filename from sysaltfiles

For this query SQL will actually query sys.sysbrickfiles.
 

The following query will confirm that both are system tables. xtype = S stands for a system table.

select name,xtype from sysobjects where name in ('sysbrickfiles','sysdbreg')

 
In conclusion, we need to update only a single table: sys.sysbrickfiles.

Le’t go and do that.

  1. Close all opened connections and Stop the SQL Server.
  2.  

  3. Start SQL in single server mode
     
    From an elevated cmd (Run as administrator), access the right folder where sqlservr.exe executable is located and execute below commands. Normally you will find it at “C:\Program Files\Microsoft SQL Server\MSSQL[InstanceName]\MSSQL\Binn”. Run below commands:
  4.  

    D:\>CD /D "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn"
    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn>sqlservr.exe -m
    

    You will see a lot of information in the shell, allow it to start the SQL and when it’s done, let the shell opened.
     

  5. Start another elevated cmd or open a DAC connection from SSMS to perform the update on your system table.
  6.  

    • Connect to SQL from cmd:
    • sqlcmd -SMACHINENAME\SERVERINSTANCE -E
    • Make sure you are connected to right server and then enable the update on system tables.
    •  

      select @@servername
      GO
      use master
      GO
      sp_configure 'allow updates', 1
      GO
      reconfigure with override
      GO
      

      A small note on this: If you had done it with SQL started in normal (multi) user mode, the above configuration change would have worked fine without any warning, but the update step on system table would have failed with the error message:
      “Ad hoc updates to system catalogs are not allowed”
       

    • Identify logical file names and their physical name locations for the database in question: – my Database Name is “data1″
    •  

      select lname, pname from sys.sysbrickfiles s1 
      inner join sys.sysdbreg s2 on s1.[dbid] = s2.[id] 
      where s2.name = 'data1' 
      
      
      
    • Perform the update.
    •  

      update sys.sysbrickfiles set pname = 'D:\MSSQL\data1.mdf' where lname ='data1';
      GO
      update sys.sysbrickfiles set pname = 'D:\MSSQL\data1_log.ldf' where lname ='data1_log';
      GO
      select lname, pname from sys.sysbrickfiles s1 
      inner join sys.sysdbreg s2 on s1.[dbid] = s2.[id] 
      where s2.name = 'data1' 
      
      
  7. Close the first cmd shell – It will stop SQL Server.
  8.  

  9. Copy database data file and log file to the new “D:\MSSQL” location.
  10.  

  11. Allow SQL Service Account full rights on “D:\MSSQL\” location – Right click on folder –> Properties –> Security –> Edit –> Add –> enter the SQL Service Account, in my case it was Network Service –> press OK –> check Full Control and press OK
  12.  

  13. Restart SQL Server (Normal mode)
  14.  
    The database should come online and its files will point to the new location.

  1. Hey man,
    this does not resolve issue. I did run this things on master and my own database just in case.
    I still getting this freekesh Msg 259, Level 16, State 1, Procedure SPNAME, Line 27 :evil:
    Ad hoc updates to system catalogs are not allowed.
    Any other ideas how to fix this on SQL 2012? :idea:

  2. Hi, I didn’t test it on 2012 but I will get back to you soon. Thanks, and I am not a man :)

  3. hi, I just tested on SQL Express version of 2012 RTM and everything worked as described. The one thing that is different on Express version is that DAC connections must be enabled from startup parameters, by adding the -T7806 flag. If you follow the exact steps it will work. Let me know in case you don ‘t succeed.

  4. Its really helpful. Thanks for sharing.

Leave a Comment


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