Con el siguiente script es facil programar un backup de todas las bases de datos, de una instancia de MS Sql Server.
Primero hay que tener en cuenta cuales son los metodos de recuperación con que esta configurada cada base de datos, ya que de esto depente el tamaño del backup y si se hace backup del registro de transacciones (log).
Modos de Recuperación de SQL Server (Modos de Registro o Recovery Model)
Ahora que ya entendemos qué es una Operación de Registro Mínimo en SQL Server y sabemos qué operaciones y en que escenarios son consideradas Operaciones de Registro Mínimo, estamos en situación de explicar las diferentes alternativas del Modo de Recuperación de SQL Server (también conocido como Modelo de Recuperación o Modo de Registro), y su impacto en las estrategias de Copia de Seguridad y Restauración (Plan de Contingencias - Backup y Restore), así como de crecimiento de LOG.
- Modo de Recuperación Simple o Sencillo (Simple Recovery Model).
- Las Operaciones de Registro Mínimo realizarán un registro mínimo en el LOG de SQL Server, minimizando las escrituras en LOG y maximizando el Rendimiento de SQL Server.
- Todas las transacciones serán registradas en LOG, sin embargo, una vez que la transacción finaliza, el espacio de LOG utilizado por dicha transacción podrá ser reutilizado para el registro de otras transacciones. No es necesario realizar copias de seguridad del LOG para poder reutilizar su espacio.
- Sólo se permiten Copias de Seguridad Completas o Diferenciales. No se puede recuperar (RESTORE) a un momento en el tiempo (STOPAT).
- Modo de Recuperación Completo (Full Recovery Model).
- Las Operaciones de Registro Mínimo no se comportarán como tal, realizándose siempre un registro completo en el LOG de SQL Server.
- Todas las transacciones serán registradas en LOG. Sólo la ejecución de una sentencia BACKUP LOG permitirá reutilizar el espacio de LOG ocupado por transacciones antiguas almacenadas en el Backup del LOG. Es decir, hasta que una transacción del LOG de SQL Server no se guarda en un BACKUP, no se permite su eliminación del LOG, con la excepción de la sentencia BACKUP LOG WITH TRUNCATE_ONLY (o BACKUP LOG WITH NO_LOG), la cual permite vaciar el LOG sin realizar físicamente una copia de seguridad (ojo: sólo se debe usar en caso de emergencia). Si no se realizan copias de seguridad de LOG periódicamente, el LOG de SQL Server crecerá indefinidamente.
- Se permite cualquier tipo de Copia de Seguridad (BACKUP): Completas, Diferenciales, de Fichero, de Grupo de Ficheros (FILEGROUP), y de LOG. Es posible recuperar (RESTORE) a un momento del tiempo (STOPAT).
- Modo de Recuperación de Registro Masivo (Bulk-Logged Recovery Model). Este Modo de Recuperación sólo debe ser utilizado de forma intermitente o eventual para mejorar el rendimiento de las Operaciones de Registro Mínimo.
- Las Operaciones de Registro Mínimo realizarán un registro mínimo en el LOG de SQL Server, minimizando las escrituras en LOG y maximizando el Rendimiento de SQL Server.
- Todas las transacciones serán registradas en LOG. Sólo la ejecución de una sentencia BACKUP LOG permitirá reutilizar el espacio de LOG ocupado por transacciones antiguas almacenadas en el Backup del LOG. Es decir, hasta que una transacción del LOG de SQL Server no se guarda en un BACKUP, no se permite su eliminación del LOG, con la excepción de la sentencia BACKUP LOG WITH TRUNCATE_ONLY (o BACKUP LOG WITH NO_LOG), la cual permite vaciar el LOG sin realizar físicamente una copia de seguridad (sólo usar en caso de emergencia). Si no se realizan copias de seguridad de LOG periódicamente, el LOG de SQL Server crecerá indefinidamente.
Debe tenerse en cuenta, que al realizar una copia del LOG (Backup LOG) durante un periodo de tiempo en el que han ocurrido Operaciones de Registro Mínimo, la copia del LOG almacenará tanto el contenido del LOG (o Transaction LOG), como el contenido de las páginas de datos afectadas (que se leerá de los correspondientes ficheros de datos). Es decir, tendremos un tamaño de LOG relativamente pequeño, pero sin embargo, la copia del LOG (Backup LOG) podría tener un tamaño considerablemente grande.
- Se permite cualquier tipo de Copia de Seguridad (BACKUP): Completas, Diferenciales, de Fichero, de Grupo de Ficheros (FILEGROUP), y de LOG, pero no se puede recuperar (RESTORE) a un momento en el tiempo (STOPAT).
Una conocida estrategia de configuración de bases de datos SQL Server es mantener un Modo de Recuperación Completo (FULL) durante toda la jornada, y en periodos de carga masiva o de operaciones de mantenimiento, utilizar un Modo de Recuperación de Registro Masivo (Bulk Logged), con el objetivo de mejorar el rendimiento de Operaciones de Registro Mínimo (SELECT INTO, BULK INSERT, BCP.EXE, operaciones INDEX DDL como CREATE INDEX o DROP INDEX, etc.). La utilización de una estrategia mixta de Modo de Registro Completo y Registro Masivo (FULL y Bulk-Logged) probablemente requiera una actualización del Plan de Contingencias o Políticas de Backups y Restores de SQL Server.
También debe tenerse en cuenta, que determinadas configuraciones requieren del Modo de Recuperación Completo para su funcionamiento. Por ejemplo, si deseamos implementar Database Mirroring (disponible de SQL Server 2005), será necesario utilizar el Modo de Recuperación Completo.
T-SCRIPT
DECLARE @path VARCHAR(256) -- path for backup files
SET @path = 'C:\BACKUPS\'
DECLARE @DBName varchar(255)
DECLARE @DATABASES_Fetch int
DECLARE DATABASES_CURSOR CURSOR FOR
select
DATABASE_NAME = db_name(s_mf.database_id)
from
sys.master_files s_mf
where
-- ONLINE
s_mf.state = 0
-- Only look at databases to which we have access
and has_dbaccess(db_name(s_mf.database_id)) = 1
-- Not master, tempdb or model
and db_name(s_mf.database_id) not in ('Master','tempdb','model','msdb')
group by s_mf.database_id
order by 1
OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
declare @DBFileName varchar(256)
set @DBFileName = CAST(DATEPART(yyyy, getdate()) AS VARCHAR(4)) + '-' + CAST(DATEPART(mm, getdate()) AS VARCHAR(2)) + '-' + CAST(DATEPART(dd, getdate()) AS VARCHAR(2)) + '-' +
replace(replace(@DBName,':','_'),'\','_') + '.bak'
PRINT '-- Making DATA Backup ' + @path + @DBFileName
exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = N''' + @path + @DBFileName + '''')
-- check the recuperation mode, for no error if is simple
IF(SELECT recovery_model_desc FROM sys.databases WHERE name = @DBName) != 'SIMPLE'
BEGIN
set @DBFileName = CAST(DATEPART(yyyy, getdate()) AS VARCHAR(4)) + '-' + CAST(DATEPART(mm, getdate()) AS VARCHAR(2)) + '-' + CAST(DATEPART(dd, getdate()) AS VARCHAR(2)) + '-' +
replace(replace(@DBName,':','_'),'\','_') + '-LOG.bak'
PRINT '-- Making LOG Backup ' + @path + @DBFileName
exec ('BACKUP LOG [' + @DBName + '] TO DISK = N''' + @path + @DBFileName + '''')
END
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END
CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR