In SQL Server 2005 we able to change the location of the resource database files what is stated in msdn.
If we look the same on the SQL Server 2008 R2 , we read:
The resource database cannot be moved.

Let’s be curious, and try to do the same, as described in msdn for SQL Server 2005:

C:\>NET START MSSQLSERVER /f /T3608

Version check:

select @@version

Microsoft SQL Server 2008 R2 (SP2) – 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1(Build 7601: Service Pack 1) (Hypervisor)Specifying a new location:

ALTER DATABASE mssqlsystemresource
 MODIFY FILE (NAME=data, FILENAME= 'C:\DATA\mssqlsystemresource.mdf');
 ALTER DATABASE mssqlsystemresource
 MODIFY FILE (NAME=log, FILENAME= 'C:\DATA\mssqlsystemresource.ldf');

The file “data” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “log” has been modified in the system catalog. The new path will be used the next time the database is started.
Move the database mssqlsystemresource files:

C:\>move "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.*" C:\DATA\

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.ldf
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.mdf
2 file(s) moved.
And restart the SQL Server – it works.

Leave a Reply

Your email address will not be published. Required fields are marked *

October 2019
M T W T F S S
« Jul    
 123456
78910111213
14151617181920
21222324252627
28293031