SQL Server backup and restore of the Resource database
Problem
We 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 us to include in our 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 <drive>:\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. we can verify by renaming the database files while the service is stopped.
we will not be able to start the service after this. we 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. we can backup the database using the following options:
It is important to document the location of our master database as part of our 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 we 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.
Next Steps
http://www.mssqltips.com/sqlservertip/1544/sql-server-backup-and-restore-of-the-resource-database/
We 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 us to include in our 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 <drive>:\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. we can verify by renaming the database files while the service is stopped.
we will not be able to start the service after this. we 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. we can backup the database using the following options:
- we can use a simple xcopy command to copy from the source location to a destination where we keep our daily database backups. Use the -Y
option to suppress the prompt to confirm if we want to overwrite the
file. we can create a scheduled task to do this on a daily basis. If we
want to keep multiple copies of the database files, we can create an
automated script to rename them after the copy process.
xcopy <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf <destination folder> /Y xcopy <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf <destination folder> /Y - we can use our file-based backup utilities such as NTBackup, IBM Tivoli Storage Manager, Symantec BackupExec, etc.
It is important to document the location of our master database as part of our 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 we 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.
Next Steps
- Try renaming the Resource database files while the service is stopped and verify that the SQL Server service will not start after that
- Backup the Resource database in your SQL Server 2005 instances
- Include the Resource database in your disaster recovery plan
http://www.mssqltips.com/sqlservertip/1544/sql-server-backup-and-restore-of-the-resource-database/
No comments:
Post a Comment