SQL Server backup and restore of the Resource database
July 22nd 2008 04:16
SQL Server backup and restore of the Resource database
Problem
I have been hearing about the Resource database in SQL Server 2005 and that it should be included in our system database backups. Unfortunately, this database does not show up in SQL Server Management Studio for me to include in my database backups. How do we backup and restore the Resource database?
Solution
What is the Resource database?
The Resource database (shortly referred to as RDB) is a hidden, read-only database that contains all the system objects that are included with SQL Server 2005. This is the reason why it does not appear in SQL Server Management Studio. It contains all the system objects that ship with SQL Server 2005. These objects physically exist in the Resource database but logically appear in the sys schema of every database on the instance. It complements the master database in a sense as the SQL Server service now also depends on this database. The Resource database makes it easy for service packs to be applied or rolled back whenever necessary. In SQL Server 2000, whenever a service pack is applied, all the system objects that reside on both system and user databases will be updated, making it more difficult to rollback the change whenever necessary. It is also the reason why Microsoft recommends that you backup all the system and user databases before applying a service pack.
In SQL Server 2005, changes will only be made to the this database and will be reflected on all the system and user databases on the instance. If you need to apply a service pack on multiple instances, all you need to do is copy the Resource database's MDF and LDF files to the target instances. Rolling back the changes is as simple as overwriting the database files with an older copy. The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf and are located, by default, in <
rive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
Why is it important?
The Resource database appears to be a critical system database as the SQL Server service is now dependent on this. You can verify by renaming the database files while the service is stopped. You will not be able to start the service after this. You can also try moving the master database on a different location without moving the Resource database together with it and you will not be able to start the service. It's location is dependent on the master database. This is critical during a disaster recovery process as we have gotten used to dealing with only the master database in previous versions.
Backing up the Resource database
Since the Resource database is not available from the SQL Server tools, we cannot perform a backup similar to how we do it with the other databases. You can backup the database using the following options:
1.You can use a simple xcopy command to copy from the source location to a destination where you keep your daily database backups. Use the -Y option to suppress the prompt to confirm if you want to overwrite the file. You can create a scheduled task to do this on a daily basis. If you want to keep multiple copies of the database files, you can create an automated script to rename them after the copy process.
xcopy <rive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mss qlsystemresource.mdf <estination folder> /Y
xcopy <rive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mss qlsystemresource.ldf <estination folder> /Y
2.You can use your file-based backup utilities such as NTBackup, IBM Tivoli Storage Manager, Symantec BackupExec, etc.
Restoring the Resource database
It is important to document the location of your master database as part of your disaster recovery process. In previous versions of SQL Server, all we need to do to restore the server instance is to worry about the master database.
After a SQL Server 2005 instance has been rebuilt a restore of the master database will be done, the Resource database files should go along with it should a WITH MOVE option be required. This means that if the old location of the master database will be different from the one after the restore, the Resource database files should already be there prior to restoring the master database. This is very critical if a hardware failure occurred and you need to move the system databases on a different drive during the server instance rebuild.
To restore the Resource database, just copy the database files to the location of the master database files. If you have an older version of the Resource database, it is important to re-apply any subsequent updates. This is why the recommended approach is to simply do a daily backup of these files.
Problem
I have been hearing about the Resource database in SQL Server 2005 and that it should be included in our system database backups. Unfortunately, this database does not show up in SQL Server Management Studio for me to include in my database backups. How do we backup and restore the Resource database?
Solution
What is the Resource database?
The Resource database (shortly referred to as RDB) is a hidden, read-only database that contains all the system objects that are included with SQL Server 2005. This is the reason why it does not appear in SQL Server Management Studio. It contains all the system objects that ship with SQL Server 2005. These objects physically exist in the Resource database but logically appear in the sys schema of every database on the instance. It complements the master database in a sense as the SQL Server service now also depends on this database. The Resource database makes it easy for service packs to be applied or rolled back whenever necessary. In SQL Server 2000, whenever a service pack is applied, all the system objects that reside on both system and user databases will be updated, making it more difficult to rollback the change whenever necessary. It is also the reason why Microsoft recommends that you backup all the system and user databases before applying a service pack.
In SQL Server 2005, changes will only be made to the this database and will be reflected on all the system and user databases on the instance. If you need to apply a service pack on multiple instances, all you need to do is copy the Resource database's MDF and LDF files to the target instances. Rolling back the changes is as simple as overwriting the database files with an older copy. The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf and are located, by default, in <
rive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Why is it important?
The Resource database appears to be a critical system database as the SQL Server service is now dependent on this. You can verify by renaming the database files while the service is stopped. You will not be able to start the service after this. You can also try moving the master database on a different location without moving the Resource database together with it and you will not be able to start the service. It's location is dependent on the master database. This is critical during a disaster recovery process as we have gotten used to dealing with only the master database in previous versions.
Backing up the Resource database
Since the Resource database is not available from the SQL Server tools, we cannot perform a backup similar to how we do it with the other databases. You can backup the database using the following options:
1.You can use a simple xcopy command to copy from the source location to a destination where you keep your daily database backups. Use the -Y option to suppress the prompt to confirm if you want to overwrite the file. You can create a scheduled task to do this on a daily basis. If you want to keep multiple copies of the database files, you can create an automated script to rename them after the copy process.
xcopy <
rive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mss qlsystemresource.mdf <estination folder> /Y xcopy <
rive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mss qlsystemresource.ldf <estination folder> /Y2.You can use your file-based backup utilities such as NTBackup, IBM Tivoli Storage Manager, Symantec BackupExec, etc.
Restoring the Resource database
It is important to document the location of your master database as part of your disaster recovery process. In previous versions of SQL Server, all we need to do to restore the server instance is to worry about the master database.
After a SQL Server 2005 instance has been rebuilt a restore of the master database will be done, the Resource database files should go along with it should a WITH MOVE option be required. This means that if the old location of the master database will be different from the one after the restore, the Resource database files should already be there prior to restoring the master database. This is very critical if a hardware failure occurred and you need to move the system databases on a different drive during the server instance rebuild.
To restore the Resource database, just copy the database files to the location of the master database files. If you have an older version of the Resource database, it is important to re-apply any subsequent updates. This is why the recommended approach is to simply do a daily backup of these files.
| 38 |
| Vote |
Add To: 
