Copia de seguridad de "Microsoft SQL Server".

Este tipo de elemento protegido realiza una copia de seguridad de una base de datos de Microsoft SQL Server. La tecnología subyacente es VDI y es compatible con SQL Server 2005 y posteriores.

No data is spooled to the local disk. As per the "Program Output" type, no progress bar or ETA appears during a Microsoft SQL Server backup.

Databases are backed up one-at-a-time. If you require point-in-time consistency across multiple databases, please use the "Application-Aware Writer" option instead.

Detalles de la conexión

Al configurar el nuevo elemento protegido MSSQL, los detalles de conexión deben ser suministrados antes de seleccionar las bases de datos. eazyBackup sólo se conectará a SQL Server que se ejecute en la máquina local. Debe introducir el nombre de la instancia, o dejar el campo en blanco para utilizar la instancia por defecto.

Dirección

The address is always localhost, but EazyBackup does not use TCP addresses or TCP ports to connect to SQL Server instances. eazyBackup uses "Shared Memory" to connect to SQL Server instances.

EazyBackup's use of "Shared Memory" connection does improves performance for some operations, at the expense of only working on the local machine; but eazyBackup's use of VDI requires it to run against the local machine anyway.

If you encounter issues connecting to your SQL Server, you must ensure that "Shared Memory protocol" is enabled in SQL Server Configuration Manager.

Abrir el Administrador de Configuración de Sql Server -> Configuración de Red de SQL Server -> Protocolos -> Memoria Compartida -> Habilitar

Conductor

OLE DB and ODBC are data access methods that use pluggable "drivers" / "providers" for connecting to databases like SQL Server. The following drivers for OLE DB / ODBC support SQL Server:

Conductor	Soporte de TLS 1.2	Notas
MSOLEDBSQL	Sí	Incluido con SQL Server 2016 y 2017; descarga opcional desde https://www.microsoft.com/en-us/download/details.aspx?id=56730
SQLNCLI11	Sí	Incluido en SQL Server 2012 y 2014; descarga opcional desde https://www.microsoft.com/en-us/download/details.aspx?id=50402
SQLNCLI10	No	Incluido en SQL Server 2008
SQLNCLI	No	Incluido en SQL Server 2005
SQLOLEDB	No	Incluido con SQL Server (todas las versiones); Incluido con Windows desde XP / Server 2003

EazyBackup se ha ido actualizando con el paso del tiempo para soportar la prueba de controladores adicionales:

Versión de EazyBackup	Conductor preferido	Conductor(es) de reserva
>= 18.9.6, >= 18.8.6	MSOLEDBSQL	SQLNCLI11, SQLOLEDB

Puede listar los controladores instalados

• for ODBC, via the odbcad32.exe program > "Drivers" tab; or
• para OLE DB, a través de este fragmento de PowerShell.

Autenticación

EazyBackup allows you to connect to SQL Server using either Windows authentication (running as the backup service account - usually NT SERVICE\backup.delegate or SYSTEM), or native SQL Server authentication.

• Si se utiliza la autenticación de Windows, la conexión se produce como la cuenta de servicio de copia de seguridad.
  • Puede asignar esta cuenta de usuario de Windows para que tenga derechos de administrador del sistema dentro de SQL Server.
• Si utiliza la autenticación de SQL Server, debe introducir un nombre de usuario y una contraseña válidos para conectarse a SQL Server.

La suplantación no está disponible actualmente para la autenticación de Windows. Las futuras versiones de eazyBackup soportarán la suplantación para la autenticación de Windows.

Múltiples instancias

EazyBackup supports backing up multiple instances from SQL Server. You can select an instance for backup, by entering the instance name in the "Instance Name" field. Leave this field blank to use the default instance.

EazyBackup Backup lista automáticamente las instancias disponibles para su selección en el menú desplegable.

Modo de copia de seguridad

Por defecto, EazyBackup opta por realizar una exportación completa de la base de datos desde SQL Server, y luego utiliza su propio sistema de deduplicación para optimizar los datos almacenados/cargados.

This is the "Full (copy only)" option. It is equivalent to the BACKUP WITH COPY_ONLY T-SQL statement.

Debido a que EazyBackup puede deduplicar eficientemente las copias de seguridad de imágenes completas, normalmente es suficiente con tomar sólo copias de seguridad completas de SQL Server en eazyBackup.

Imágenes base

You have the option to use SQL Server's own differential/log backup system. This may be more efficient, but it does require additional administrative work, and complicates the process of restoring data.

The SQL Server maintains one single point-in-time reference, from which it can produce differential backups and/or log-based backups. When you take a new "Full (base image)" backup, the point-in-time reference is moved forward, so that any future differential and/or log-based backups are based on the last base-image backup.

To use SQL Server's own differential/log backup system, you must create multiple Protected Items (each with a different schedule) in order to capture both a base image and a differential/log backups. By creating multiple Protected Items, you can individually schedule, report-on, and manage retention policies for both base and differential/log backups.

If you are using EazyBackup alongside another product for SQL server backups, you should ensure that only one product is taking base-image backups. Otherwise, it's possible that a chain of differential/log backups would be incomplete.

Diferencial 

EazyBackup can use SQL Server's own systems for differential backup. In this mode, you can regularly make "differential base" backups, and then a series of small "differential increment" backups, each containing the difference from the last base backup. These operations are equivalent to the BACKUP and BACKUP WITH DIFFERENTIAL T-SQL statements respectively. eazyBackup will still deduplicate multiple base backups that are sent to the same Storage Vault.

This is the "Differential increment" option.

Registro 

You can opt to use SQL Server's own systems for log backup. In this mode, you must periodically take full (base image) backups, and regularly take log backups.

Puede elegir si aplicar el truncamiento del registro. Estas operaciones son equivalentes a las sentencias T-SQL BACKUP LOG y BACKUP LOG WITH NO_TRUNCATE respectivamente. eazyBackup seguirá deduplicando todos los datos que se envíen al mismo Storage Vault.

To use SQL Server's own log system, you must create multiple Protected Items (each with a different schedule) in order to capture both full and log backups.

This mode requires that the database Recovery Model is set to "Full" or "Bulk Logged" in SQL Server. For more information, please see https://msdn.microsoft.com/en-us/library/ms189275.aspx .

Recomendaciones 

In general, we would recommend using the default "Full" backup technique.

SQL Server's native differential/log systems may be used if you experience performance issues with the default mode, however, you must ensure that

1. Ningún otro sistema de copia de seguridad está restableciendo la última copia de seguridad base;
2. Siempre que el diferencial/registro haga una copia de seguridad con éxito, que la base también ha hecho recientemente una copia de seguridad con éxito;
3. Las copias de seguridad de base se realizan regularmente para minimizar la sobrecarga de los diferenciales.
4. La retención se gestiona cuidadosamente para garantizar que la recuperación sea posible

Formas alternativas de hacer copias de seguridad de Microsoft SQL Server

You can use the "Application-Aware Writer" type to back up SQL Server using the VSS Writer. Compared to eazyBackup's standard VDI approach, this option enables more detailed progress information, and can take a consistent point-in-time snapshot of multiple databases at once; but offers more limited control over SQL Server features such as log truncation. The resulting files also must be restored in a different way.

You can use eazyBackup's "Commands" feature to call osql/sqlcmd to run a T-SQL BACKUP statement against the database, and then back up the resulting spooled file with the "Files and Folders" type. This option requires more temporary disk space than the built-in system above.

You can use the "Files and Folders" type to back up individual database files if the "Take filesystem snapshot" option is selected. However, the "Files and Folders" backup type does not invoke SQL Server's VSS writer, so this would (at best) produce a "crash-consistent" backup and is no se recomienda.