The SQL Server agent manages the process of restoring one or more databases. Databases can be restored either locally or remotely. The local SQL Server database restore functionality allows the restore of a single database or multiple databases onto the same SQL Server from which the database backup was performed. For example, a backup of Database A is performed on SQL Server A and Database A is subsequently restored onto SQL Server A.
The remote SQL Server database restore operation provides the capability to restore a single database or multiple databases onto the currently logged in to SQL Server from a different SQL Server in the network. For example, a backup of Database A is performed on SQL Server A and Database A is subsequently restored onto SQL Server B. Remote SQL Server database restores require that each SQL Server agent be registered on the selected backup system. In addition, the registered SQL Server agent must contain SQL Server database backups on the backup system.
As long as different clients backup their databases to the same system, they can be restored locally or from different clients.
Note: While restoring a database make sure that no backup process for the same database is running at that time. This can cause the restore to fail.
• Local SQL Server Restore – This option allows the local restore of a SQL Server database backup. The database can be restored to an alternate location on the same machine (different drive or different directory) or to the default location. If this option is chosen, the list of database backups for the selected SQL Server instance is populated. Highlight a single database or multiple databases to restore and click Next to continue.
• Remote SQL Server Restore – This option initiates the remote restore of a SQL Server database. Remote database restores can be performed to an alternate location or to the default location.
• Selecting the Remote SQL Server Restore option launches the remote SQL Server database process.
Both clients (the computer that is being backed up from and the computer that is being backed up to) must be registered to the backup system that contains the client database backup. You need to queue the restores from the client machine to which the database needs to be restored. The Select SQL Server interface is displayed on clicking on Remote SQL Server Restore.
On the System Supervisor Log in interface, enter the System Supervisor Override username in the Supervisor field and the System Supervisor Override password in the Password field.
The Supervisor login and password is the authentication that the administrator provided at the time the backup system was installed. If the administrator had chosen default settings at installation time, these fields can also be set on the backup system from the Task Manager window by going to Setup > Supervisor Override. Since databases will be restored from an alternate server, it is necessary that you provide authentication. Once you have successfully logged in to the backup system, select a remote SQL Server machine name from the Restore SQL Server drop-down list and the appropriate SQL Server instance from the SQL Server Instance drop-down list.
Once completed, a list of database backups meeting the selected criteria display on the database restore interface. Highlight a single database or multiple databases to restore and click Next to continue.
The SQL Server agent automatically determines the latest possible time for which a database can be restored. You have two options for restoring the database. One is to Restore the database up to the latest possible time and the other to Restore to a specific point-in-time. The Restore to a specific point-in-time option is available only when a single database is selected. The option to Kill all database connections before recovery should be selected before restoring your SQL Server database. Enabling this option terminates active database connections, therefore preserving database integrity during the restore process.
The Database Restore Location field allows the input of an alternate name to which the database will be restored. Restoring the database to an alternate name is useful for performing troubleshooting or optimization tasks without affecting the current live database. If the name that is entered into the Database Restore Location field already exists, you have an opportunity to continue with the restore and overwrite the database name or to enter a different name. The value in this field defaults to the current name of the database to be restored. If multiple databases are selected to be restored, this feature is disabled.
When Submit Restore Job is clicked, the restore request is queued on the server simultaneously, and will run upon availability of the server’s resources. Review the status of the requested database restores via the backup system’s Job Status screen.
Restoring to an alternate server has a few restrictions. These are as follows:
• A remote restore of SQL Server 2005 databases cannot be done to SQL Server 2000. The 2005 database design has some additional features that cannot be interpreted by the SQL 2000 server.
• Only administrators are allowed to perform restores. The administrator must be aware of the backup system’s supervisor login. It is recommended to set this information on the backup system, else the defaults would be used as login information which may be insecure.
• Remote restore from a tape device is not supported.