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 ;).