Back up de bases de usuario FULL + DIFF + LOG

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