SQL Resource Database

1. Read-only hidden system database
2. Does not contain any user data or any user metadata.
3. mssqlsystemresouce.mdf mssqlsystemresource.ldf
4. each instance has only and only one resource database, ID is always 32767
5. in SQL 2005, resource database file need to be at the same location as Master database files.
6. in SQL 2008, Master and Resource database files can be at different locations.
7. in SQL 2008, it is under binn directory and don't recommend to move it since any service packs and hotfixes will move it back to the original location. :\Program Files\Microsoft SQL Server\MSSQL10.\MSSQL\Binn.
8. Backup resource database using file based or a disk based backup .
9. Restore it by coping the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the respective folders.

NET START MSSQLSERVER /f /T3608
NET START MSSQL$instancename /f /T3608

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME='new_path\mssqlsystemresource.mdf')

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME='new_path\mssqlsystemresource.ldf')

ALTER DATABASE mssqlsystemresource SET READ_ONLY


SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'mssqlsystemresource')