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
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.

  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:

    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.

    • Connect to SQL from cmd:
    • Make sure you are connected to right server and then enable the update on system tables.

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

      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 = 'data1' 
    • Perform the update.

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

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

  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

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

Leave a comment ?


  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 👿
    Ad hoc updates to system catalogs are not allowed.
    Any other ideas how to fix this on SQL 2012? 💡

  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.

  5. Thanks for sharing nice article. Some good question is available here:

    (admin: i removed the link following your comment, as my blog is not a place for advertising)

  6. i have a question and question is how i can do view all hidden systems table with data? Is it possible or not?

    Finally Nice Blog. I will do appreciate.

  7. In tools, options, environment, startup, there should be a checkbox under “Open Object Explorer” that must be unchecked.
    Thanks for the appreciation.

  8. Hello Irina, thanks for this, just please note that the instance name – if not the default one – must also be specified when running sqlserver -m
    Good night!

  9. “sqlservr”, sorry.

  10. sure, thanks for taking the time to comment.

  11. Hi, when I am trying to update size field in sys.sysbrickfiles, it says Invalid object name ‘sys.sysbrickfiles’

    what am I missing?

    many thanks,

  12. i am using SQL 2008R2

  13. hello,
    it can be a stupid question but are you in master database, when executing that update ? ❓

  14. Hey, Can we update as per below in 2008r2?
    SET data_source=’TEST’ WHERE server_id=1

  15. Update is not giving errors, but the values are not getting updated.

  16. I have a slightly different issue – I have a spurious entry in the former sysobjects table. The object is’tempdb..export_bib’ id = 1012302766. I have found that the sysobjects is now a view and that two tables and have replaced it. However, if I try to operate on these tables – it acts as if they are not recognized. Is there a way to clear out a spurious entry from sysobjects now?

  17. Lucas Benvegnú Zambon

    do these steps work for inserts too?
    I need to insert a new line into sys.certificates.

    Or perhaps there is another way?

    Thanks a lot

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=""> <s> <strike> <strong>