- What's New
- General Use
- Backing Up
- Database Backup History
- Disaster Recovery
The SQL Server Agent provides the ability to back up and restore Microsoft SQL servers (2005, 2008, 2012, 2014, 2016, 2017, 2019). Without the agent add-on, Retrospect cannot easily and reliably back up databases from a running SQL server.
You can back up a SQL server locally, running the Retrospect application on the server itself, or you can back up the SQL server as a Retrospect client, running the Retrospect application on a different computer.
SQL cluster support
Retrospect supports backing up and restoring a SQL server in a cluster environment with the following limitations:
- The cluster can only have two nodes.
- You can back up multiple SQL resource groups that are shared between the two nodes.
- Nodes must be backed up and restored as Retrospect clients. The Retrospect backup computer cannot be one of the nodes in the cluster.
- You can only back up SQL data on the node that owns the SQL resource group. Likewise, you can only restore SQL data to the node that owns the SQL resource group.
- Retrospect does not back up and restore cluster configuration information. In a disaster recovery scenario, you will have to manually recreate the cluster before restoring the SQL data with Retrospect.
- If you want to make sure your SQL data gets backed up regardless of which node currently owns the SQL resource group, you must have a separate SQL license (and a client license) for each node. If you only want to back up your SQL data when a specific node owns the SQL resource group, you only need a SQL license (and a client license) for that node.
SQL container does not appear
If you install SQL server on a system that is already running Retrospect, you will need to reboot the server before Retrospect recognizes the SQL container.
Custom path for restoring data and log files
During a database restore operation with the default options, Retrospect restores database data and log files to their original locations, even if they are stored in different places (e.g., data files on C:\ and log files on E:\). If you choose to restore to a custom path, both data and log files are restored to a single location that you specify (e.g., D:\).
If you will also be using the Retrospect Exchange Server Agent add-on, please read Security for details on setting up Exchange Server Agent security.
Numerous security measures prevent an unauthorized user from simply launching Retrospect on the backup computer and backing up a SQL server. You must prepare Retrospect to run in the correct security context, or to access each SQL server in the correct security context. This involves configuring preferences in Retrospect so that the application runs as a certain user with sufficient privileges for the SQL server, or configuring Retrospect to log in to each SQL server individually using SQL authentication or domain authentication.
After configuring the Retrospect backup user account, you must configure Retrospect to use the new account. Although you can configure Retrospect to run as a different domain user for each SQL server, or to use a given SQL login for each SQL server, it is easier and better to configure a particular preference so Retrospect runs as the Retrospect backup user for all SQL servers. The following steps describe how to set that preference. You can later configure different SQL server logins.
If you have not already done so, install Retrospect on the backup computer as described in Getting Started.
If this is not the first time you have run Retrospect on the backup computer, click Configure on the Retrospect navigation bar, then click Preferences. In the Execution preferences group, click Security.
“Run Retrospect as the logged-in user” does so when you launch Retrospect manually (from the Start menu or Windows Explorer). When Retrospect automatically launches (to execute a script), it runs under the Local System account. Neither is ideal for use with SQL, because backups will likely fail due to lack of privileges. Therefore, do not select this preference.
If Retrospect tells you the login is invalid, you may have mistyped the domain user name or password. Re-check the name and follow the above steps starting at Create a Retrospect Backup User Account.
When it verifies the specified domain user, Retrospect tells you to exit the application and re-launch for the change to take effect. Exit now and log off the administrator before taking the next step.
Log in to the backup computer as a user who is a member of Backup Operators. Launch Retrospect and take note of its application window title, which includes the user the application is running as. Though you are logged in to the backup computer as a different user, the “user” running the application is the Retrospect backup user account.
With the security preference set as described previously and a member of Backup Operators or Administrators logged in to the backup computer, Retrospect always runs with local administrator privileges, at minimum.
If you will use a SQL server as a Retrospect client, install the Retrospect client software on that computer. For details see Networked Clients.
If you are using a SQL server as a Retrospect client, log in the client from the backup computer. For details see Networked Clients.
Their icons are grayed out because they are not yet licensed, as indicated by the text. Though there is a license available in the license manager, Retrospect does not know with which servers you want to use it. You must apply a license to each server you wish to use with Retrospect.
Click on the SQL server you want to work with, which makes Retrospect ask whether you wish to use an available license. Click OK. After a moment, the “not licensed” tag disappears from the SQL Server container.
If Retrospect does not do anything when you click SQL Server, or it asks you to enter a login for a local (non-client) server, the logged in user may not have sufficient privileges to access the SQL server or the service may not be running. Confirm the service is working and re-read Security and set up security correctly before trying again.
Select an authentication method: Use SQL Authentication, Use Domain Authentication, or Use current RBU information (to authenticate using the Retrospect backup user account you supplied in the security preference). Enter the login information for your selected authentication method.
If Retrospect does not display the SQL server databases, or it tells you authentication failed, the specified user may not have sufficient privileges to access the SQL server on the client computer or the service may not be running. Confirm the service is working and re-read Security and set up security correctly before trying again.
If a SQL Server container’s icon is gray, or nothing is nested beneath it, Retrospect may be running as a user with insufficient privileges to see the SQL server data. If you use the SQL Server container in scripts they will fail when executed. Re-read Security and set up security correctly.
If a SQL Server container’s icon is colored and one or more items are nested beneath it, Retrospect is running as a user privileged to see the SQL server data. This means you set up the security correctly.
Retrospect displays one SQL Server container per SQL server. It appears under My Computer when Retrospect is run locally on the server itself. For a SQL server that is a networked client of Retrospect, its SQL Server container appears under its computer’s client container.
To back up a SQL server or a database, you can use a backup script or you can initiate an immediate backup. Each method uses the familiar components of a backup: source, destination, selection criteria, and options.
Retrospect ignores selectors when backing up SQL databases. If your sources include both non-database volumes and SQL databases, Retrospect applies the specified selector to the non-database volumes only.
When implementing a scripted backup strategy, you will have separate scripts for the different backup types. For example, you might have a full backup script scheduled to run on Fridays and a differential backup script running daily.
For details on the available options, see Windows SQL Server Options.
You must ensure a given database uses a recovery model compatible with your desired types of backup. For example, Retrospect will report an error trying to do a log backup of a SQL database that uses the simple recovery model.
You are not assured you will be able to restore a database Snapshot from one set of media, as is the case with restoring files. Retrospect may require media from multiple Backup Sets to restore a given database, depending on your backup strategy and backup history.
To restore a SQL server or one or more databases, you can use a script or you can initiate an immediate restore. Each method uses the familiar components of a restore: source database Snapshot, destination server, chosen files (messages and folders from mailboxes only), and options.
Notice how multiple sessions are listed below the source database Snapshot if its most recent backup was not the full backup type. This is because Retrospect has an execution option, on by default, that causes it to restore each session needed to completely restore the source.
First, follow the appropriate steps in Restoring the Backup Computer. Next, follow the steps below.
If you restored from a backup that used the Retrospect Open File Backup add-on to back up the SQL server (specifically, its master, model and msdb system databases), follow this step. If not, skip this step on continue with the next step.
These databases allow the SQL service to start after the SQL application has been restored. After all partitions on the system have been restored, verify the SQL services are running. If a service is not running, start it from SQL Server Service Manager or SQL Server Enterprise Manager.
Rebuild then Restart Services
If you did not restore from a backup that used the Retrospect Open File Backup add-on to back up the SQL server (specifically, its master, model and msdb system databases), follow this step. If you did, skip this step and continue with the next step.
Because the SQL service will not start without the master and other system databases, use the rebuildm.exe utility to rebuild the databases. Run the utility for each instance of SQL on the system. After the system databases are restored, verify the SQL service has started. Manually restart the service if needed.
Restore Databases in Single User Mode
Start the SQL server in single user mode from the SQL Server Enterprise Manager, not from the command line. Right-click on the server and choose Properties. Click Setup Parameters and add new parameter -m. Stop and restart the SQL service using SQL Server Service Manager. The SQL server is now running in single user mode and all services are running with the same user ID.
Delete Suspect Databases
After completing the previous step, there are databases in the SQL Enterprise Manager that are grayed out and marked as suspect. This is because the master database contains a record of all the databases for that server and any databases which lack an associated data file are suspect. Delete all suspect databases on SQL servers to ensure the next step is successful.
- Restore Databases
Use Retrospect to restore the msdb database, then restore all other databases, always restoring from the most recent database Snapshot. After you have restored all SQL databases for each SQL server, your system is ready for use.