- 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.
Compatibilidad con clusters de SQL
Retrospect permite la copia de seguridad y restauración de SQL Server en entornos de clusters con las siguientes limitaciones:
- El cluster solo puede tener dos nodos.
- Solo puede hacer copia de seguridad de grupos de recursos de SQL que estén compartidos entre los dos nodos.
- Se debe hacer copia de seguridad y restaurar los nodos como clientes de Retrospect. El ordenador de copia de seguridad de Retrospect no puede ser uno de los nodos del cluster.
- Solo se puede hacer copia de seguridad de datos de SQL en el nodo al que pertenece el grupo de recursos de SQL. Del mismo modo, solo se puede hacer restauración de datos de SQL en el nodo al que pertenece el grupo de recursos de SQL.
- Retrospect no hace copia de seguridad ni restaura información de la configuración de clusters. En una situación de recuperación de desastres, tendrá que volver a crear manualmente el cluster antes de restaurar los datos de SQL con Retrospect.
- Si desea asegurarse de que se hace copia de seguridad de los datos de SQL independientemente de qué nodo posee actualmente el grupo de recursos de SQL, deberá tener una licencia de SQL independiente (y una licencia de cliente) para cada nodo. Si solo desea hacer copia de seguridad de los datos de SQL cuando un nodo específico posee el grupo de recursos de SQL, solo necesitará una licencia de SQL (y una licencia de cliente) para dicho nodo.
El contenedor de SQL no aparece
si instala SQL Server en un sistema en el que ya se esté ejecutando Retrospect, deberá reiniciar el servidor antes de que Retrospect pueda reconocer el contenedor de SQL.
Ruta personalizada para la restauración de archivos de datos y de registro
durante una operación de restauración de base de datos con las opciones predeterminadas, Retrospect restaura los archivos de datos y de registro en su ubicación original, incluso si se almacenan en lugares diferentes (p. ej. los archivos de datos en C:\ y los archivos de registro en E:\). Si decide restaurar en una ruta personalizada, tanto los archivos de datos como los de registro se restaurarán en la ubicación que especifique (p. ej. 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.