Modelos de recuperación

Los modelos de recuperación son una parte muy importante de las bases de datos y relacionados a las copias de seguridad (backup), existen 3 tipos de modelos de recuperación, simple, full, bulk logged, esto impacta el espacio usado por los logs de transacciones y cuanto se puede recuperar en caso de recuperar información de una copia de seguridad.

El modelo recuperación es lo que nos permite controlar que tanto de las transacciones son mantenidas en el log de transacciones o si son desechadas tan pronto como son plasmadas en la base de datos.

Los modelos de recuperación son:

• Simple: En este modelo de recuperación las operaciones son plasmadas en la base de datos tan pronto sea posible, de esta manera nos evitamos el tener que mantener el log de transacciones, claro que las limitantes es que no tenemos recuperación en punto de tiempo, a la vez tampoco podemos usar algunas características como lo son log shipping, mirror, alwayson.
• Usarlo: Preferentemente cuando nos es importante las transacciones o es posible tolerar las pérdidas de las mismas, como lo que son ambientes de prueba, desarrollo, DWH, o tenemos limitantes de espacio.

• Bulk logged: El modelo de recuperación es idéntico y no rompe la cadena de transacciones de un full backup, pero solo hace un log minino de operaciones bulk (esto quiere decir que solo nota que se hizo mas no cada cambio realizado), no permite la recuperación en tiempo pero su uso solo debe de ser temporal.
•  Usarlo: Cuando se vaya a hacer una insercion masiva de datos, por medio de:
• SELECT INTO
• BCP, BULK INSERT, BULK IMPORT
• INSERT INTO SELECT
• Operationes Bulk dentro de un OPENROWSET
• CREATE, ALTER, DROP INDEX

• Full: Al igual que en bulk logged, se debe de dar mantenimiento al log de transacciones y cada transacción es guardada dentro del log de transacciones, aquí tenemos la menor perdida de información y si queremos restaurar hasta cierto momento algo que paso es posible hacerlo, esta restauración, aunque se requiere más espacio para los backup y el log de transacciones ocupara más espacio y debe de ser mantenido.
• Usarlo: Cuando la información que tenemos es muy valiosa o estamos usando alguna de las características como son log shipping, mirror o alwayson.

Ejemplo:

--Creamos la tabla Prueba
CREATE TABLE Prueba (
    id            int    IDENTITY(1,1),
    nombre        nvarchar(50),
    apaterno    nvarchar(50),
    amaterno    nvarchar(50),
    direccion    nvarchar(100),
    country        nvarchar(50),
    birthday    date
    )
--Vemos el tamaño de los archivos
SELECT name, FORMAT((size*8096), N'N', 'es-MX')  FROM sys.database_files;
GO
--Hacemos las inserciones
BEGIN TRANSACTION
DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 100000)
BEGIN
   INSERT INTO PRUEBA VALUES
   (
   CONVERT(varchar(50), NEWID()),
   CONVERT(varchar(50), NEWID()),
   CONVERT(varchar(50), NEWID()),
   CONVERT(varchar(150), NEWID()),
   CONVERT(varchar(50), NEWID()),
   DATEADD(
             SECOND,(RAND()*60+1),
             DATEADD(MINUTE,(RAND()*60+1) ,
             DATEADD(DAY,(RAND()*365+1),
             DATEADD(YEAR,-1,GETDATE())))) )
   SELECT @a = @a + 1;
END
COMMIT
GO
--Hacemos updates para llegar el log de transacciones
BEGIN TRANSACTION
UPDATE dbo.Prueba
SET apaterno = CONVERT(varchar(50), NEWID()),
    nombre = CONVERT(varchar(50), NEWID()),
    birthday = GETDATE()
COMMIT TRANSACTION
GO
--Vemos el crecimiento de los archivos
SELECT name, FORMAT((size*8096), N'N', 'es-MX')  FROM sys.database_files;

Más información:

Recovery Models (SQL Server)

Nota: Una de las cuestiones más importantes sobre los modelos de recuperación es la cadena de secuencia de backups, esta cadena se puede romper cuando se hace una copia de seguridad nueva, a que nos referimos con esto, es que differential o logbackup esta atado a un full o a un differential. Esta se rompe (se pierden las referencias) cuando se hace un nuevo full o differential, cuando se cambia el modelo de recuperación de full o bulk logged a simple o vice versa.