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
I assume that master_files and databases have the same referenced tables.
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.
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.
In conclusion, we need to update only a single table: sys.sysbrickfiles.
Le’t go and do that.
Close all opened connections and Stop the SQL Server. 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:
Start another elevated cmd or open a DAC connection from SSMS to perform the update on your system table.
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.
- Connect to SQL from cmd:
sqlcmd -SMACHINENAME\SERVERINSTANCE -E
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”
select lname, pname from sys.sysbrickfiles s1 inner join sys.sysdbreg s2 on s1.[dbid] = s2.[id] where s2.name = 'data1'
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'
The database should come online and its files will point to the new location.