/* Katherine Villyard, posted 12/22/12
http://www.katherinevillyard.com/2012/12/22/generic-backups/
*/

Declare @DBname varchar(255),
		@RowsToProcess int,
		@CurrentRow int,
		@SQL nvarchar(max), 
		@today nvarchar(50),
		@chour nvarchar(50),
		@servername nvarchar(50)

set @today = convert(nvarchar,GETDATE(),12)  
set @chour = replace(CONVERT(nvarchar,GETDATE(),108),':','')
set @servername = REPLACE(@@SERVERNAME,'\','-')

CREATE TABLE #maintenancetemp (RowID int not null primary key identity(1,1), dbname nvarchar(max) )  
INSERT into #maintenancetemp (dbname) select name from sys.databases where name <> 'tempdb' and name <> 'model' and recovery_model IN (1, 2)
SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT 
        @DBname=dbname
        FROM #maintenancetemp
        WHERE RowID=@CurrentRow
				set @SQL='BACKUP LOG [' + @DBName + '] TO  DISK = N''S:\backups\' + @servername + '-' + @DBName + '-' + @today + '-' + @chour + '-LOG.bak'' WITH FORMAT, INIT,  NAME = N''Hourly ' + @DBName + ' Log Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
				--print @SQL
				EXEC SP_EXECUTESQL @SQL				
END

drop table #maintenancetemp


