Script en dos pasos para respaldar todas las bases de usuario, FULL + DIFF
Una vez por semana…
Modificar: SET @path = ‘C:\ARPETA\FULL\’ a lo que corresponda
/*============================================================================ Script: Backup_User_databases Objetivo: Realiza un backup de todas las bases de datos de user ============================================================================*/ -- Declara Variables DECLARE @db_name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name DECLARE @commandLine char(500) -- Seteo de variables SET @path = 'C:\ARPETA\FULL\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) -- Comienzo -- ------------------------------------ --PRINT 'Cargando el cursor...' ------------------------------------ DECLARE db_cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM sys.databases WHERE name NOT IN ('master','model','msdb', 'tempdb') and state = 0 --Status is Online OPEN db_cursor FETCH NEXT FROM db_cursor INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @db_name + '_' + @fileDate + '.BAK' SET @commandLine = 'BACKUP DATABASE [' + @db_name + '] to disk = ''' + @fileName + ''' WITH FORMAT' ------------------------------------ --PRINT 'Backup de DB: ' + @db_name ------------------------------------ --print @commandLine exec(@commandLine) FETCH NEXT FROM db_cursor INTO @db_name END CLOSE db_cursor DEALLOCATE db_cursor -- final --
Diariamente…
Modificar: SET @path = ‘C:\ARPETA\DIFF\’
/*============================================================================ Script: Backup_User_databases Objetivo: Realiza un backup de todas las bases de datos de user (diferencial) ============================================================================*/ -- Declara Variables DECLARE @db_name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name DECLARE @commandLine char(500) -- Seteo de variables SET @path = 'C:\ARPETA\DIFF\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) -- Comienzo -- ------------------------------------ --PRINT 'Cargando el cursor...' ------------------------------------ DECLARE db_cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM sys.databases WHERE name NOT IN ('master','model','msdb', 'tempdb') and state = 0 --Status is Online OPEN db_cursor FETCH NEXT FROM db_cursor INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @db_name + '_diff_' + @fileDate + '.BAK' SET @commandLine = 'BACKUP DATABASE [' + @db_name + '] to disk = ''' + @fileName + '''' + ' WITH DIFFERENTIAL' ------------------------------------ --PRINT 'Backup de DB: ' + @db_name ------------------------------------ --print @commandLine exec(@commandLine) FETCH NEXT FROM db_cursor INTO @db_name END CLOSE db_cursor DEALLOCATE db_cursor -- final --
Y para hacerla completa, el respaldo de los logs…, cada hora o dos.
Modificar: SET @path = ‘C:\ARPETA\LOG\’
/*============================================================================ Script: Backup_log_User_databases Objetivo: Realiza un backup log de todas las bases de datos de usuario ============================================================================*/ -- Declara Variables DECLARE @db_name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name DECLARE @commandLine char(500) -- Seteo de variables SET @path = 'C:\ARPETA\LOG\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) -- Comienzo -- ------------------------------------ --PRINT 'Cargando el cursor...' ------------------------------------ DECLARE db_cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM sys.databases WHERE name NOT IN ('master','model','msdb', 'tempdb') and state = 0 --Status is Online and recovery_model_desc like 'FULL' OPEN db_cursor FETCH NEXT FROM db_cursor INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @db_name + '_' + @fileDate + '.TRN' SET @commandLine = 'BACKUP LOG [' + @db_name + '] to disk = ''' + @fileName + '''' + 'WITH FORMAT' ------------------------------------ --PRINT 'Backup de DB: ' + @db_name ------------------------------------ --print @commandLine exec(@commandLine) FETCH NEXT FROM db_cursor INTO @db_name END CLOSE db_cursor DEALLOCATE db_cursor -- final --
Actualizacion 2012-07-06: Los scripts antes mencionados, tuneados y en scripts que generan los jobs de SQL Back Up SQL