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 *

March 2023
M T W T F S S
« Jul    
 12345
6789101112
13141516171819
20212223242526
2728293031