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 *

June 2020
M T W T F S S
« Jul    
1234567
891011121314
15161718192021
22232425262728
2930