- O que há de novo
- 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). 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.
O que há de novo
Suporte a cluster SQL
O Retrospect suporta o backup e a restauração de um servidor SQL em um ambiente de cluster com as seguintes limitações:
- O cluster pode ter apenas dois nós.
- Você pode fazer backup de vários grupos de recursos SQL que são compartilhados entre os dois nós.
- Os nós devem ser submetidos a backup e restaurados como clientes do Retrospect. O computador de backup do Retrospect não pode ser um dos nós no cluster.
- Você só pode fazer backup de dados SQL no nó que possui o grupo de recursos SQL. Da mesma forma, você só pode restaurar dados SQL para o nó que possui o grupo de recursos SQL.
- O Retrospect não faz backup e restaura as informações de configuração do cluster. Em um cenário de recuperação de desastre, você precisará recriar manualmente o cluster antes de restaurar os dados SQL com o Retrospect.
- Se você quiser garantir que seus dados SQL sejam submetidos a backup, independentemente de qual nó possui atualmente o grupo de recursos SQL, você deve ter uma licença SQL separada (e uma licença de cliente) para cada nó. Se você quiser apenas fazer backup de seus dados SQL quando um nó específico possuir o grupo de recursos SQL, você precisará apenas de uma licença SQL (e uma licença de cliente) para esse nó.
Contêiner SQL não aparece
Se você instalar o SQL Server em um sistema que já esteja executando o Retrospect, será necessário reinicializar o servidor antes que o Retrospect reconheça o contêiner SQL.
Caminho personalizado para restaurar dados e arquivos de log
Durante uma operação de restauração de banco de dados com as opções padrão, o Retrospect restaura os dados e arquivos de log do banco de dados em seus locais originais, mesmo se eles estiverem armazenados em locais diferentes (por exemplo, arquivos de dados em C: \ e arquivos de log em E: \). Se você optar por restaurar um caminho personalizado, os dados e os arquivos de log serão restaurados para um único local que você especificar (por exemplo, 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.