Backup Both Full and TLog Database Backup within a single Job

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ Backup Both Full and TLog Database Backup using single Job +
+ Author - Nitin Gupta (SQL DBA- 2000\2005\2008) Noida       +
+ Date - 23/07/2009                                          +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+++++++++++++++++
+-- STEP - 1    +
+++++++++++++++++

USE master

-- Creating table for storing recorsd with Flag
CREATE TABLE master.dbo.BackupDetails(db_Name char(10) unique,db_Backup_Date Date, db_flag bit);


-- Inserting Database details into Table
DECLARE @name VARCHAR(50)
DECLARE db_cursor CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('tempdb','ReportServer','ReportServerTempDB') 

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 
BEGIN 

INSERT INTO master.dbo.BackupDetails VALUES(@name, GETDATE(),0)
FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor 
DEALLOCATE db_cursor 


+++++++++++++++++
+ STEP - 2      +
+++++++++++++++++


Create Procedure [dbo].[GetDetails_Test]
as

DECLARE @Rev_db_Name VARCHAR(50)
DECLARE db_cursor CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('tempdb','ReportServer','ReportServerTempDB') 

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @Rev_db_Name 


WHILE @@FETCH_STATUS = 0 
BEGIN 

DECLARE @dbDate Date; 
DECLARE @dbFlag bit;
DECLARE @databaseName VARCHAR(10), @backupFileName VARCHAR(200), @backupDirectory VARCHAR(100)

SET @backupDirectory = 'F:\Softwares\SQL 2008\Backups\'

SELECT @databaseName = db_name,@dbDate = db_Backup_Date, @dbFlag = db_flag FROM master.dbo.BackupDetails
WHERE db_Name = @Rev_db_Name;

IF @dbFlag <> 1 or @dbDate <> GETDATE()
BEGIN

-- Full Backup Task
SET @backupFileName = @backupDirectory + rtrim(ltrim(@databaseName)) + '_' + rtrim(ltrim(replace(convert(varchar, getdate(),110), '-', '.'))) + '_Full-Backup.BAK' 
Backup Database @Rev_db_Name to disk = @backupFileName

UPDATE master.dbo.BackupDetails
SET db_flag = 1 , db_Backup_Date = GETDATE() 
WHERE db_name = @Rev_db_Name

PRINT 'Full Backup for Database : ' + @Rev_db_Name + ' is done !!!!!!!!'
END
ELSE
BEGIN

-- Log Backup Task
if @Rev_db_Name = 'master'
begin
Print 'No Log Backup for Master Database'
end
else
begin
SET @backupFileName = @backupDirectory + rtrim(ltrim(@databaseName)) + '_' + rtrim(ltrim(replace(convert(varchar, getdate(),110), '-', '.'))) + '_Log-Backup.TRN' 
Backup LOG @Rev_db_Name to disk = @backupFileName

PRINT 'T-Log Backup for Database : ' + @Rev_db_Name + ' is done !!!!!!!!'
end
END

FETCH NEXT FROM db_cursor INTO @Rev_db_Name
END

CLOSE db_cursor 
DEALLOCATE db_cursor 

GO