Performing local and redirected restore of SQL 2000, 2005 and 2008 Databases with Backup Exec

Article:TECH56694  |  Created: 2009-01-07  |  Updated: 2013-04-09  |  Article URL http://www.symantec.com/docs/TECH56694
Article Type
Technical Solution


Subject

Issue



Performing local and redirected restore of SQL 2000, 2005 and 2008 Databases with Backup Exec


Solution




The following are important points to remember prior to running an SQL Restore:
  • The SQL service pack level while restoring system databases (i.e., Master, Model and Pub databases) must be the same as that of the SQL service pack level of the SQL Instance that was backed up or the restore job will fail.
  • To restore an SQL Server database backup, the Backup Exec logon user account must be a member of the SQL Server's Administrators group.
  • Backup Exec needs to have full administrator access to two registry keys of the SQL server (i.e.:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server and MSSQLServer) otherwise a restore to the default directory and the "Automate Master Database" restore for SQL will not complete.
  • While restoring, the master database must be in Single User Mode.  If "Automate Master Database" option is used during restore, then the master database is automatically put in Single User Mode.
  • While performing a full restore, the master database must be restored first in a separate restore job. After the master database has been restored successfully, the other databases can be restored.
  • Because the master database contains machine specific security information, the SQL Services may not start if the master database is redirected to a different machine or SQL Instance, even if the restore completes successfully. A support case will need to be opened with Microsoft if this issue does occur.
  • Only one database can be selected for restore at a time when a redirected restore is performed.
  • MASTER Database cannot be redirected on a different server.

To restore the Master Database to the original location it was backed up from:

1. Open Backup Exec
2. On the navigation bar, click Restore
3. On the Properties pane, under Source, click Selections
4. Select the master database from the last full backup of SQL server database for restore. (Figure 1)

Figure 1
 

NOTE:  It is a good option to test the logon credentials. This can be accomplished by clicking Resource Credentials under Properties pane. This way, a user has the option to test other logon accounts listed under BE

5. On  the Properties pane, under Settings, click Microsoft SQL, select Automate master database restore if the Master database is not in single user mode
6. Select the "Leave database operational. No additional transaction logs can be restored" option only if no point-in-time backups are available to be restored. (Figure 2)

Figure 2
 

7.  Start  the restore job.

To restore all the remaining databases back to where they were originally backed up from:

1. Open Backup Exec
2. On the navigation bar, click Restore
3. On the Properties pane, under Source, click Selections
4. Select the last full backup of SQL server database for restore and deselect the master database. (Figure 3)

Figure 3
 

5. On the Properties pane, under Settings, click Microsoft SQL
6. Select the "Leave database operational  No additional transaction logs can be restored" option only if no point-in-time backups are available to be restored.
7. Set Consistency check after restore to the desired check. (Figure 4)

Figure 4
 

8. Start the restore

To perform a Redirected Restore of a database:

1. Ensure SQL Server 2000, 2005 and 2008 with relevant service packs are installed on the target server similar to the source server when the database was backed up

NOTE: A SQL 2005 database cannot be redirected to a SQL 2000 server

2. Open Backup Exec
3. On the navigation bar, click Restore
4. On the Properties pane, under Source, click Selections
5. Select the database to restore. (Figure 5)

Figure 5
 

6. On the Properties pane, under Destination, click Microsoft SQL Redirection.
7. Check the box for Redirect Microsoft SQL Server sets.
8. Enter the destination server name or alternate database name if redirecting to the original server but a different database.

NOTE:  If the database the restore is being redirected to does not exist, the restore will create the new database during the restore process.

9. Select the instance and database name (if any) to redirect the database to.
10. Select the destination options (if any) to change the destination of the database files. (Figure 6)

Figure 6
 

NOTE: If the Backup Exec account does not have the proper security rights, use the 'sa' account by clicking on Change for SQL logon account and provide 'sa' account credentials.  This account must exist under Network | Logon Accounts before it can be selected.

11.  Start the restore job.

Supplemental Materials

SourceUMI
ValueV-79-65323-3108
Description

To restore the master database, the database must be running in single-user mode.

 



Legacy ID



295642


Article URL http://www.symantec.com/docs/TECH56694


Terms of use for this information are found in Legal Notices