SQL Add-on Configuration

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.

What’s New in SQL Server

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:\).

Security

If you will also be using the Retrospect Exchange Server Agent add-on, please read

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.

Regardless of whether you have yet installed or run Retrospect, and regardless of how you previously set its security preference, follow these instructions to ensure you can work with SQL.

Create and Manage User Logins

You must create a special login for use by Retrospect and identify human users of Retrospect as Backup Operators. These steps, which use Active Directory Users and Computers, are described below.

Within the SQL server’s domain, create a login dedicated for use by Retrospect, such as RBU (for Retrospect backup user account).

Make this account a member of Domain Users, Domain Admins, Administrators, and Backup Operators.

Make the accounts of human users of Retrospect members of Backup Operators. Any login to be used with Retrospect on the backup computer must be a member of Backup Operators or Administrators.

Configure Retrospect Security Preference

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.

Log in to the backup computer using an administrator-privileged login.

If you have not already done so, install Retrospect on the backup computer as described in

Launch the Retrospect application.

If this is the first time you have run Retrospect on the backup computer, the Getting Started Wizard displays. It includes a screen on which to enter user account information.

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.

In both the preferences window and the first-launch user account window, the two radio buttons determine the user login under which the Retrospect application executes.

“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.

“Always run Retrospect as the specified user” is the preferred setting for use with SQL. See

When you click OK to accept the security preference change, Retrospect may ask you to confirm your entry; click OK.

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

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.

When you tell Retrospect to always run under a particular login, it assigns local administrator privileges to the login if it has none.

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.

This means a backup operator could exploit administrator privileges in Retrospect to manipulate files on the local computer.

To provide additional security, consider using Retrospect’s password protection preference to control user access to the Retrospect application.

Installation

If you have not already done so, install Retrospect on the backup computer as described in

If you will use a SQL server as a Retrospect client, install the Retrospect client software on that computer. For details see

Logging in the Client

If you are using a SQL server as a Retrospect client, log in the client from the backup computer. For details see

Licensing and Logging In

Choose License Manager from the Window menu to see licensed components.

If SQL Database Backup is not listed, or if it is listed with no available licenses, click Add and enter a license code for SQL Database Backup. (Click Purchase to get a code from Retrospect.)

After adding a SQL Backup license, it is listed as available for use. The next step is to use it.

From the navigation bar, click Volumes to see the volumes database window. Under My Computer and Backup Clients are all the SQL servers known to Retrospect.

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

For a server that is a client of Retrospect, Retrospect asks you to enter SQL server login information immediately after applying a license.

Follow the steps below.

For each SQL server, Retrospect lets you specify an authentication method other than the Retrospect backup user account.

For each SQL server that is a client of Retrospect, you must enter login information for the SQL server immediately after applying its license.

For a just-licensed client, Retrospect already has the login window up. Otherwise, in the volumes database window, select a SQL server and click the Login as button on the toolbar.

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.

Most users use the SA account for SQL Authentication when authenticating to the SQL Server.

If you use Domain Authentication, double check if NT AUTHORITY\SYSTEM has the sysadmin Server Role. You will need to try enabling that role before backup of the SQL database. 

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

General Use

After licensing a SQL server, you can add it to your scripts, working with it in source and destination lists. You can also work with SQL servers in the volumes database window.

You cannot archive SQL data.

Working with a Volumes List

From the navigation bar, click Configure>Volumes to display the Volumes Database window. Under My Computer and Backup Clients are all the SQL servers known to Retrospect.

For each SQL server there is a SQL Server container under each computer operating as a SQL server. If you are running Retrospect on the server itself, the SQL container is under My Computer.

If you are not running Retrospect on the server, the SQL container is under Backup Clients.

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

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.

The SQL Server Container

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.

A SQL Server container includes one or more databases. Click the + or – controls of a SQL Server container to expose or hide its contents.

Selecting a SQL Server container as a backup source causes Retrospect to back up all of the server’s databases.

If you do not wish to back up all databases within a given server, you can select one or more individual databases to be backed up. Individual databases appear under the SQL Server container.

Releasing a License

You can release Retrospect’s license of a SQL server you no longer wish to back up or restore, making the license available for other use with other SQL servers.

Select a SQL container and click the Licensing button on the toolbar. Retrospect asks whether you wish to release the used license. Click OK to release it.

After releasing a SQL license, SQL containers and their databases become invalid in scripts.

Backing Up

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.

For the source, select any one or combination of SQL Server containers and databases. Or, select My Computer or Backup Clients to back up a local SQL server or client SQL server, respectively.

For the destination, select any one or combination of Backup Sets.

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.

The SQL Server options group lets you determine the type of backup Retrospect should attempt for the source. Choose Full Backup, Differential Backup, Log Backup, or Log Backup No Truncate.

Retrospect cannot do any other type of backup unless the database has previously had a full backup. In this case it automatically attempts a full backup.

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

Recovery Model

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.

Backup Sets

Unlike backups of other data, database backups are not necessarily self-contained within a single Backup Set.

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.

Database Backup History

Retrospect tracks each backup session of each database. You can view the history of these backups from Reports>Database Backup History.

The window lists all of Retrospect’s database session Snapshots of each SQL database. (Exchange databases and storage groups are also listed in this window.)

Using the buttons at the bottom of the window, you can view the properties of a Snapshot, add a database session from a Backup Set, or remove a Snapshot from this list.

Viewing the properties of a Snapshot shows you the Backup Set media required to restore the database.

Restoring

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.

Restoring a SQL Database

To restore a database from a SQL server backup, click Restore>Database (to initiate an immediate restore) or create a Restore Database script.

For the source, select a database Snapshot from which to restore.

You can add and remove database Snapshots from this list with the Add and Forget buttons.

Set the destination for the database to be restored by selecting a SQL Server container.

Click Options to specify execution options. For details on the available options, see

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.

Once your database restore operation is set up, you can save the script or proceed with the immediate restore.

Disaster Recovery

After completely backing up a SQL server (system volume and databases), you can recover from a disastrous data loss.

First, follow the appropriate steps in

Restart Services

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.

If a SQL server lost its registration in Enterprise Manager, simply re-register it.

Skip the next step.

Rebuild then Restart Services

If you did

Use the Microsoft Search utility to find the rebuildm.exe utility on your system. Have your Microsoft SQL installation CD available.

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.

Use Retrospect to restore only the master database from its most recent database Snapshot.

After completing the restore operation, remove the -m option, then stop and restart the SQL server to return to normal mode.

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.

See the SQL Enterprise Manager logs for details on errors reported by Retrospect.

Retrospect and SQL SMO Support

Retrospect Backup, as of 18.5.3 for Windows, now supports connecting to Microsoft SQL databases via Server Management Objects (SMO). Follow the configuration steps below to setup Retrospect to backup SQL locally or through the Retrospect client.

Setup a Windows user for Retrospect to use to backup SQL databases.

The user needs to belong to the Administrators group, then the user needs to be added to "Security" > "Logins" under the SQL instance in the SQL Server Management Studio. Right click on Logins, select "New Login…" add the Windows user in the Login name field, click on Server roles, check sysadmin and click OK to create the user.

Configure Retrospect

If Retrospect is running on the SQL Server, you will want to log into Windows as this user account when running Retrospect. After launching Retrospect go to Configure Volumes, click on the SQL Server object, click on OK in the "Database license required" pop-up, in the "Enter SQL Server login information" window, select Use Domain Authentication and enter the user credentials. If the machine is not on a domain enter the computer name in the Domain field. SMO only supports Windows authentication; SQL authentication is not supported.

If the Retrospect client is installed on the SQL server, you will need to configure the client service to run as the user setup in step 1. Bring up the Windows run menu, type services.msc and click OK. Find the Retrospect Client service and get properties, select the Log On tab, select "This account:" and enter the user credentials. You will need to restart the client service for this to take effect.

Troubleshooting

If you see "File (database_name) can’t read, error -1004 and you have followed the configuration steps above, you may need to run the following commands to enable win remoting through powershell. Launch powershell, run "Enable-PSRemoting -Force" and then run "winrm quickconfig".

Updated July 2024