How to restore a Microsoft SQL Server 7.0 or later database, locally or redirected (not including master)

Article:TECH22575  |  Created: 2006-01-02  |  Updated: 2006-01-02  |  Article URL
Article Type
Technical Solution




How to restore a Microsoft SQL Server 7.0 or later database, locally or redirected (not including master)


In the event that a restore of a Microsoft SQL server database is needed for recovering a database, follow these instructions:

When restoring a Microsoft SQL Server 7.0 or later database, make sure that no users are connected to the database. Use SQL Server Enterprise Manager to enable the database option Single User. This limits access to the selected database to one user connection (Figure 1).

Figure 1 (version 9.x)

When restoring online backups of SQL databases, the destination computer has to have SQL installed and running. If the restore is to a production SQL server, note the location of the data files so that the restore can be directed to the same location as the production databases.

1. In Backup Exec, go to the Restore tab. Select the backup that contains the database that needs to be restored (Figure 2).  

Figure 2

2. In the SQL tab, make the following selections (Figure 3):
  • Recovery completion state
  • Leave database operational. No additional transaction logs can be restored
Select this option when restoring the last database, differential, or log backup in the restore sequence in order to have the restore operation roll back all incomplete transactions.
  • Leave database nonoperational but able to restore additional transaction logs
Select this option during a restore if you have additional differential or transaction log backups to be restored in another restore job.
  • Leave database read-only and able to restore additional transaction logs
Select this option during transaction log and database restore to create and maintain a standby database. See your SQL documentation for information on standby databases.

  • Replace database or filegroups
Select this check box to replace a database or filegroup, even if another database or filegroup with the same name already exists on the server. If Replace Databases or Filegroups is not specified for a restore, SQL performs a safety check to ensure that a different database or filegroup is not accidentally overwritten.

  • Alternative drive for restoring database files
Use this option to select a default drive to which SQL database files can be restored if the drive where one or more of the database files previously resided no longer exists.
  • Restore all database files to the target instance's data location
Select this check box to restore files to the default data and log directories of the destination instance.

Figure 3

Note: If redirecting to another SQL server, follow the steps in the Redirection tab, else click Run Now or Save Job.

Redirection Restore:

When redirecting the database backup to an alternate server, SQL must be installed and the restore will need to go to the same path as the existing database.

After following step 3, select the SQL Redirection tab. Select Redirect Microsoft SQL Server sets. Provide the target server's NetBIOS name. Provide the database name if restoring data to an already existing database. If restoring SQL 2000 data to a differently named instance, use the Restore to named instance field to supply the instance name (Figure 4).
Figure 4

Click Run Now or Save Job when the necessary information has been entered.

Legacy ID


Article URL

Terms of use for this information are found in Legal Notices