logon

DECLARE @databasename AS VARCHAR(2048)
DECLARE @backupfile AS VARCHAR(2048)
DECLARE @backupname AS VARCHAR(2048)
DECLARE @backup AS VARCHAR(2048)
DECLARE @recipients AS VARCHAR(2048)
 
--- SETTINGS VARIABLE
---
SET @databasename = 'BW_DEMO'
SET @backupfile = 'E:\BackupSQL\BACKUP_BW_DEMO.BAK'
SET @backupname = @databaseName + '-Full'
SET @recipients = 'info@domena.cz'
 
BACKUP DATABASE @databasename TO DISK = @backupfile WITH NOFORMAT, INIT,  NAME = @backupname, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
 
DECLARE @nowdate AS VARCHAR(2048)
SELECT @nowdate = CONVERT(VARCHAR(10), GETDATE(), 120) + '%'
 
USE msdb
 
DECLARE @resultcount AS INT
SELECT @resultcount = COUNT(*) FROM dbo.backupset WHERE CONVERT(VARCHAR(10),backup_start_date,120) LIKE @nowdate AND name = @backupname AND database_name = @databasename 
 
DECLARE @emailbody AS VARCHAR(2048)
DECLARE @subject AS VARCHAR(256)
 
IF @resultcount=0
	BEGIN
		SET @subject = 'BACKUP ' + @databasename + ': FAILED.'
		SET @emailbody = 'BACKUP ' + @databasename + ' IS FAILED.';
	END
ELSE
	BEGIN
		Declare @result table
		(
			ID INT IDENTITY(1,1),
			backup_size int,
			backup_start_date DATETIME,
			backup_finish_date DATETIME
		)
		insert into @result(backup_size,backup_start_date,backup_finish_date) 
		select backup_size,backup_start_date,backup_finish_date FROM dbo.backupset WHERE CONVERT(VARCHAR(10),backup_start_date,120) LIKE @nowdate AND name = @backupname AND database_name = @databasename 
 
		DECLARE @id AS INT
		DECLARE @text AS VARCHAR(2048)
		DECLARE @set AS VARCHAR(2048)
		DECLARE @start AS DATETIME
		DECLARE @stop AS DATETIME
 
		SELECT @set = ''
		SELECT @id = 1
 
		WHILE @id < = @resultcount
		BEGIN
			SELECT @text = backup_size FROM @result WHERE id=@id
			SELECT @set = @set + 'Size backup: ' + @text + 'B<br>'
			SELECT @start = backup_start_date FROM @result WHERE id=@id
			SELECT @set = @set + 'Start time: ' + CONVERT(VARCHAR(20),@start,13) + '<br />'
			SELECT @stop = backup_finish_date FROM @result WHERE id=@id
			SELECT @set = @set + 'Total time: ' + CONVERT(VARCHAR(100),DATEDIFF (second,@start,@stop),8) + ' seconds' + '<br />'
			SELECT @set = @set + '<br />'
			SELECT @id = @id + 1
		END	
		SET @subject = 'BACKUP ' + @databasename + ': SUCCESSFUL' 
		SET @emailbody = 'BACKUP <b>' + @databaseName + '</b> IS SUCCESSFUL.<br />'
		SET @emailbody = @emailbody + 'File backup: ' + @backupfile + '<br /><br />More information:<br />' + @set
	END
 
EXEC spSendMail @recipients = @recipients, @subject = @subject, @from = 'sql@domena.cz', @body = @emailbody
 
GO