Please enter search query.
Search <product_name> all support & community content...
Article: 100001936
Last Published: 2012-07-21
Ratings: 0 0
Product(s): NetBackup & Alta Data Protection
Problem
How to restore master, msdb and model databases in Microsoft SQL Server 2012 and higherSolution
Overview:
Master, msdb, model and tempdb databases are created by default when you install Microsoft SQL Server 2012. Each database plays an important role in managing user databases. Refer to https://msdn.microsoft.com/en-us/library/ms178028(v=sql.110).aspx for further information on the roles.
During disaster recovery, master, msdb and model databases can be restored back to the original host or to an identical DR host. This will allow Database Administrator to retain login accounts, custom templates, alerts, among other things. Tempdb only stores temporary tables and stored procedures, and cannot be backed up or restored.
General Requirements:
1. If destination host is a new server, ensure the setup is the same as the original host. These include:
Master, msdb, model and tempdb databases are created by default when you install Microsoft SQL Server 2012. Each database plays an important role in managing user databases. Refer to https://msdn.microsoft.com/en-us/library/ms178028(v=sql.110).aspx for further information on the roles.
During disaster recovery, master, msdb and model databases can be restored back to the original host or to an identical DR host. This will allow Database Administrator to retain login accounts, custom templates, alerts, among other things. Tempdb only stores temporary tables and stored procedures, and cannot be backed up or restored.
General Requirements:
1. If destination host is a new server, ensure the setup is the same as the original host. These include:
- Operating System and Service Pack.
- Host name.
- Domain name.
- SQL Sysadmin account.
- Drive letter assignment (ensure that space is adequate as well).
- NetBackup Client software and Maintenance Pack/Patch.
2. NetBackup master and media servers must be able to resolve client's host name/IP address and vice versa.
3. NetBackup Client Service must be run by an account with SysAdmin privilege.
4. In clustered SQL Server setup, the restore should be run on the active node.
Restoring master database:
This procedure assumes the SQL Server instance can start without problem either by rebuilding existing master database or installing a fresh SQL Server software.
1. Before restarting the SQL Server instance in single user mode, launch the NetBackup MS SQL Client GUI. Select File > Restore SQL Server objects and locate the backup image to restore. Select only the master database. In the restore options, select Restore selected object, tick the use replace option and leave the consistency check option as None. Select the Save radio button and click Restore. Give the script a name and click Save. NetBackup will automatically append .bch extension to the file.
2. Stop the SQL Server instance and start it again in single-user mode by following https://msdn.microsoft.com/en-AU/library/ms188236(v=sql.110).aspx . SQL Server Agent must be stopped so it does not take over the SQL Server connection, however do not set the service to Disabled as NetBackup will attempt to start it after recovery.
3. As soon as the SQL Server instance starts in single user mode, launch the restore. For rapid start, it is recommended to run the restore from command line using dbbackex.exe command. For example:
<install dir>\Veritas\NetBackup\bin\dbbackex.exe -f "<script path>\scriptname.bch"
4. The restore may fail with status code 5 if the SQL connection has been taken by a user or another application. Ensure there are no users or other applications trying to connect to the SQL instance.
3. NetBackup Client Service must be run by an account with SysAdmin privilege.
4. In clustered SQL Server setup, the restore should be run on the active node.
Restoring master database:
This procedure assumes the SQL Server instance can start without problem either by rebuilding existing master database or installing a fresh SQL Server software.
1. Before restarting the SQL Server instance in single user mode, launch the NetBackup MS SQL Client GUI. Select File > Restore SQL Server objects and locate the backup image to restore. Select only the master database. In the restore options, select Restore selected object, tick the use replace option and leave the consistency check option as None. Select the Save radio button and click Restore. Give the script a name and click Save. NetBackup will automatically append .bch extension to the file.
2. Stop the SQL Server instance and start it again in single-user mode by following https://msdn.microsoft.com/en-AU/library/ms188236(v=sql.110).aspx . SQL Server Agent must be stopped so it does not take over the SQL Server connection, however do not set the service to Disabled as NetBackup will attempt to start it after recovery.
3. As soon as the SQL Server instance starts in single user mode, launch the restore. For rapid start, it is recommended to run the restore from command line using dbbackex.exe command. For example:
<install dir>\Veritas\NetBackup\bin\dbbackex.exe -f "<script path>\scriptname.bch"
4. The restore may fail with status code 5 if the SQL connection has been taken by a user or another application. Ensure there are no users or other applications trying to connect to the SQL instance.
NOTE: The SQL Server Agent and SQL Server VSS Writer services can cause the restore to fail and should be stopped after SQL Server is started in single-user mode.
In the above scenario, Windows Application log will show event ID 18461: Login failed for user ' <NetBackup user>'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: Local IP].
5. After restore completes, NetBackup will bring up the SQL Server instance and SQL Agent automatically. Other services such as SQL Server Browser will need to be started manually.
Restoring model database:
1. After verifying that SQL Server instance is running properly post master database restore, launch the MS SQL Client GUI again. Select File > Restore SQL Server objects and locate the backup image to restore. Select only the model database. In the restore options, select Restore selected object and tick the use replace option . For the recovery option, select Recovered and leave the consistency check option as None . Select the Launch immediately radio button and click Restore to start the restore.
2. Verify the model database is restored successfully.
Restoring msdb database:
SQL Server Agent service must be stopped prior to restoring the msdb database. The service can be restarted again after restore completes.
In the above scenario, Windows Application log will show event ID 18461: Login failed for user ' <NetBackup user>'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: Local IP].
5. After restore completes, NetBackup will bring up the SQL Server instance and SQL Agent automatically. Other services such as SQL Server Browser will need to be started manually.
Restoring model database:
1. After verifying that SQL Server instance is running properly post master database restore, launch the MS SQL Client GUI again. Select File > Restore SQL Server objects and locate the backup image to restore. Select only the model database. In the restore options, select Restore selected object and tick the use replace option . For the recovery option, select Recovered and leave the consistency check option as None . Select the Launch immediately radio button and click Restore to start the restore.
2. Verify the model database is restored successfully.
Restoring msdb database:
1. Stop the SQL Server Agent, either from the Services console (services.msc) or the Failover Cluster Manager (cluadmin.msc), if clustered.
2. Repeat the model restore procedure above, but this time select the msdb database instead of model.
3. Verify the msdb database is restored successfully.
Was this content helpful?
Translated Content
Please note that this document is a translation from English, and may have been machine-translated. It is possible that updates have been made to the original version after this document was translated and published. Veritas does not guarantee the accuracy regarding the completeness of the translation. You may also refer to the English Version of this knowledge base article for up-to-date information.