Daily backup of full logged databases with log backup.

Proc_BackupDatabaseDailyWithLog.sql

if not OBJECT_ID('dbo.Proc_BackupDatabaseDailyWithLog') is null 
  Drop Proc dbo.Proc_BackupDatabaseDailyWithLog
go

Create Proc dbo.Proc_BackupDatabaseDailyWithLog(
	@DbName sysname,
	@TargetPath nvarchar(1000)) as



	Declare @FileName nvarchar(1000)
	Declare @DirPath nvarchar(1000)
	Declare @Sql nvarchar(2000)
	Declare @Exists int
	Select @DirPath = @TargetPath + '\' + @DbName  + '\' +  
		cast(datepart(yyyy,getdate()) as nvarchar(10)) + 
		(Right('00' + cast(DATEPART(mm, getdate()) as nvarchar(20)),2)) + 
		(Right('00' + cast(DATEPART(dd, getdate()) as nvarchar(20)),2)) 
	
	Select @FileName = @DirPath + '\' + @DbName + '.bak'


	exec xp_create_subdir @DirPath
	exec xp_fileexist @FileName, @Exists OUT

	
	if (@Exists = 0)
		Select @Sql = 'backup database ' + QUOTENAME(@DbName) + ' to disk=''' + @FileName + ''''
	else
	begin	
		Select @FileName = @DirPath + '\' + @DbName + 'log_' + 
			right('00' + cast(datepart(HOUR,getdate()) as nvarchar(10)),2) + 
			right('00' + cast(DATEPART(MINUTE, getdate()) as nvarchar(20)),2) + 
			right('00' + cast(DATEPART(SECOND, getdate()) as nvarchar(20)),2)  + 
			'.bak'
		Select @Sql = 'backup log' + QUOTENAME(@DbName) + ' to disk=''' + @FileName + ''''
	end

	EXEC sp_executesql @Sql
go