Emergency Friday afternoon Backups

I had a situation where a VMWare upgrade stopped the backup software from working across the board (mmm, a little notice would have been nice).

To avoid the possibility of a full log crashing an application over the weekend I created two Jobs to do full backups every 10pm and transaction backups every 3 hours and deployed them.

The script (and scripted-out job) had to be compatible with all versions of SQL Server. The only prep I had to do was creating a folder for each server (_instance) at the backup destination.

The code I downloaded from http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/ evolved into this …

--backup_all.sql

	DECLARE @name VARCHAR(50) -- database name  
	DECLARE @path VARCHAR(256) -- path for backup files  
	DECLARE @fileName VARCHAR(256) -- filename for backup 
	DECLARE @fileDate VARCHAR(20) -- added to file name
	DECLARE @sname varchar(100) -- server name
	DECLARE @DeleteDate DATETIME -- purge date ..
	SET @DeleteDate = getdate()-14 -- .. two weeks
	
-- get server name
	select @sname = replace(@@servername, '\', '_')
 
-- specify backup directory
	SET @path = '\\SomeServerName\BACKUPS\' + @sname + '\'

-- specify filename format
	SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
 
-- setup cursor
	DECLARE db_cursor CURSOR FOR  
	SELECT name FROM master.dbo.sysdatabases
	WHERE name NOT IN ('model', 'tempdb')  -- exclude these databases
 	OPEN db_cursor
	FETCH NEXT FROM db_cursor INTO @name   
 
 --loop through databases backing them up
	WHILE @@FETCH_STATUS = 0   
	BEGIN   
		SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
		BACKUP DATABASE @name TO DISK = @fileName  
		FETCH NEXT FROM db_cursor INTO @name
	END
	
-- close cursor
	CLOSE db_cursor   
	DEALLOCATE db_cursor   

-- purge old backups (but manually delete SQL2K)
	if @@version not like '%2000%' 
	exec master.sys.xp_delete_file 0, @path, 'BAK', @DeleteDate, 0

… and this for the log backups …

--backup_all_t.sql

	DECLARE @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 @sname varchar(100) -- server name
	DECLARE @DeleteDate DATETIME -- purge date ..
	SET @DeleteDate = getdate()-7 -- .. one weeks  

-- get server name
	select @sname = replace(@@servername, '\', '_')

-- specify backup directory
	SET @path = '\\SomeServerName\BACKUPS\' + @sname + '\' 

-- specify filename format
	SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)  
	   + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','') 

-- setup cursor
	DECLARE db_cursor CURSOR FOR   
	SELECT name FROM master.dbo.sysdatabases  
	WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
	AND DATABASEPROPERTYEX(name, 'Recovery') NOT IN ('SIMPLE') -- exclude Simple dbs
	OPEN db_cursor    
	FETCH NEXT FROM db_cursor INTO @name    

 --loop through databases, backing them up 
	WHILE @@FETCH_STATUS = 0    
	BEGIN    
		SET @fileName = @path + @name + '_' + @fileDate + '.TRN'   
		BACKUP LOG @name TO DISK = @fileName  
		FETCH NEXT FROM db_cursor INTO @name    
	END
	
-- close cursor
	CLOSE db_cursor    
	DEALLOCATE db_cursor    

-- purge old backups (but manually delete SQL2K)
	if @@version not like '%2000%' 
	exec master.sys.xp_delete_file 0, @path, 'TRN', @DeleteDate, 0

I heavily commented it as I was near the end of my contract, and knew “temporary solutions” can persist for a long time ;).