Resource database was announced by Microsoft in SQL server 2005 .it is one of the system database in SQL server but we cannot see it under “System databases” in SSMS.
Resource is read only and invisible database. It contains all the system objects which are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.
The Resource database makes it easy to apply the service packs (SP) or rolled back whenever necessary. Prior SQL Server 2005, whenever a service pack was applied, all the system objects that reside on both system and user databases will be updated, making it more difficult to roll back the changes whenever required. That’s why Microsoft recommends to back up all the system and user databases before applying a service pack. From the SQL Server 2005 version upgrade is just a way to copying resource database file to local server
Location of Resource database:
Physical file names of resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf & we can find out the default location using below query.
Most importantly the ID of resource database is always 32767. The DBID value of resource database has remained same across all versions of SQL 2005 and later.
Version of Resource database: The build number 10.50.1600 means you are running RTM version of SQL Server 2008 R2.
When the Resource database was last updated:
Backup Resource Database in SQL Server:
We cannot backup resource database using SSMS or T-SQL code, we need to perform our own file-based or disk-based backup by treating MDF and LDF files as they were .EXE files not the DB files.
We used XCOPY command to back up the MDF and LDF files:
Both files are backed up on the mentioned location:
Access Resource database:
If I try to use the resource database in
SSMS I got an error message that database doesn’t exist.
I stopped the SQL server services and Started back into Single user mode with –m flag. Even in single user mode we cannot see the hidden resource database & we cannot get it in sys.databases as well.
BUT we can use the Resource database in single user mode.
Renaming the Resource database files: if we rename any of the files, SQL server will not start.
1.We stopped the SQL server Services through CMD prompt:C:\Windows\system32>net stop mssql$prod
The SQL Server (PROD) service is stopping.
The SQL Server (PROD) service was stopped successfully.
2.We Rename the file name on OS level:
3.We try to start the SQL server services and below error message was reported
C:\Windows\system32>net start mssql$prodThe SQL Server (PROD) service is starting.
The SQL Server (PROD) service could not be started.
The service did not report an error.
More help is available by typing NET HELPMSG 3534.
4.We checked the Windows logs and got this error message because SQL server didn’t find the resource database file when it was starting:
C:\Windows\system32>net start mssql$prod
The SQL Server (PROD) service is starting.The SQL Server (PROD) service was started successfully.
Move the Resource Database:
In SQL 2005: Master and Resource database location must be same i.e.
If we thinking to move the resource database we must move the Master database to the same location.
1.Start in master-only recovery mode by running:
NET START MSSQLSERVER /f /T3608
You can also do it from configuration manager.
2.Launch a command prompt and run SQLCMD.
3.Use the ALTER DATABASE statement with the MODIFY FILE option to specify the new location for the resource database data and log files.
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=’data’, FILENAME=’D:\MSSQL\ mssqlsystemresource.mdf’)NET START MSSQLSERVER /f /T3608
You can also do it from configuration manager.
2.Launch a command prompt and run SQLCMD.
3.Use the ALTER DATABASE statement with the MODIFY FILE option to specify the new location for the resource database data and log files.
GO
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=’data’, FILENAME=’D:\MSSQL\ mssqlsystemresource.ldf’)
Go
4.Use the ALTER DATABASE statement to make the Resource database read-only.
Alter Database mssqlsystemresource set Read_only;
5.Stop the SQL Server service.
6.Move the database files for the “resource” database to new location.
7.Start the SQL Server service.
0 comments: