Resource DB help

[jpotucek]

after moving the resource database files with this command:


ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');
GO

I am getting this error trying to do anything in the instance while I have it started in minimal mode (/f /t3608)

(the instance WILL NOT COME UP UNLESS I USE THE /F /T3608 from a command promt.. please .. any suggestions??????????????????????
Also.. I checked and the primary file is NOT READ-ONLY.

File activation failure. The physical file name "E:\MSSQL\KOCSQLDEV01\Datafiles\mssqlsystemresource.ldf" may be incorrect.

The log cannot be rebuilt when the primary file is read-only.

File activation failure. The physical file name "E:\MSSQL\KOCSQLDEV01\Datafiles\mssqlsystemresource.ldf" may be incorrect.

The log cannot be rebuilt when the primary file is read-only.

Msg 945, Level 14, State 2, Line 1

Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

 

[calico]

Hi,

I think you cannot move or rename the Resource database file

Here some hint i founded into th book online
--------------------------------------------------
In SQL Server 2005, you can move system and user-defined database files, except the Resource database files
--------------------------------------------------
The physical file name of the Resource database is Mssqlsystemresource.mdf. By default, this file is located in x:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Mssqlsystemresource.mdf
--------------------------------------------------
Caution Do not move or rename the Resource database file. If the file is renamed or moved, SQL Server will not start. For more information, see Resource Database
-------------------------------------------------

I hope I understanded correctly your question

Calico

 

[jpotucek]

You can move system databases - you just have to be careful to put the resource DB files in the same directory as the master DB data file. Here the link that documents this:

http://msdn.microsoft.com/en-us/library/ms345408.aspx

What they DON'T tell you is that if you install SP2 on a default instance, it will create two new resource files (mdf ldf) in their original locations.

What I had to do is shutdown the SQL services and move them to where they are supposed to be and it's working (still testing but keeping my fingers crossed) live and learn.. AND DOCUMENT!

 

[calico]

Hi,

Thank for the link:

http://msdn.microsoft.com/en-us/library/ms345408.aspx

Those 3 blocs are very important to my understand, but I never experimeted those steps


1 -------------------------------------------------------
Planned Relocation and Scheduled Disk Maintenance Procedure
To move a system database data or log file as part of a planned relocation or scheduled maintenance operation, follow these steps. This procedure applies to all system databases <<<except the master and Resource databases>>>.

2 -----------------------------------------------------
Failure Recovery Procedure
If a file must be moved because of a hardware failure, follow these steps to relocate the file to a new location. This procedure applies to all system databases <<<except the master and Resource databases>>>.

3 ------------------------------------------------------
Moving the master <<<and>>> Resource Databases
The Resource database depends on the location of the master database. If you move the master database, you must also move the Resource database to the same location as the master data file. Do not put the Resource database in either compressed or encrypted NTFS file system folders. Doing so will hinder performance and prevent upgrades.

To move the master <<<and>>> Resource databases, follow these steps.
------------------------------------------------------

What I understand is : you can move Mssqlsystemresource files ONLY if you also move Master files

Is it what you want ? Moving both Mssqlsystemresource and master ?

Calico