use [master]
go
alter procedure proc_backup_database
 @pi_path varchar(256)
,@pi_count int
,@pi_verify bit
as
  declare 
  @db_name varchar(50),			  -- database name
  @file_name varchar(256),		-- filename for backup
  @file_date varchar(20),		-- used for file name
  @backup_count int
  ,@status_backup varchar(200)
  ,@flag int
  ,@current_backup int

  /*
  --membuat tabel hanya sekali di eksekusi
  create table [dbo].daily_backup (
  intid int identity (1, 1), name varchar(200), file_date varchar(8), start_date_time datetime, end_date_time datetime, 
  flag int,
  int_count int
  )
  */

  -- set @path = @pi_path +'\'+ cast(serverproperty('machinename') as nvarchar(64)) + '-'
  -- set @file_date = convert(varchar(20), getdate(), 112) + '_' + replace(convert(varchar(20), getdate(), 108), ':', '')
  
  --
  set @file_date = convert(varchar(20), getdate(), 112) 
  delete from [dbo].daily_backup where file_date=@file_date

  insert into [dbo].daily_backup (name, file_date, flag, int_count)
  select name,@file_date, 0, 0
  from master.dbo.sysdatabases
  where name not in ('master','model','msdb','tempdb', 'reportserver', 'reportservertempdb', 'x_db_middleware_bas_simulasi','tmpinsurance')
  --where upper(left(name,4)) in ('boss')
  
  
  declare nomor_cursor cursor for
  select intid from [dbo].daily_backup where flag=0 and int_count<@pi_count
  open nomor_cursor;
  fetch next from nomor_cursor into @current_backup;
  while @@fetch_status = 0
  begin
		select
		@db_name = name, @file_name = @pi_path +'\'+ @file_date+ '_'+name + '.bak' -- hanya tanggal
		from [dbo].daily_backup
		where intid = @current_backup

		-- simpan tanggal dan jam awal proses
		update [dbo].daily_backup set start_date_time=getdate()
		where intid = @current_backup
		
		-- tidak menimpa file yang ada
		-- backup database @db_name to disk = @file_name with  retaindays = 30, noformat, noinit, skip, norewind, nounload,  stats = 10
		-- menimpa file yang ada
		backup database @db_name to disk = @file_name with init, compression  
		if @@error=0 set @flag=1 else set @flag=0

		-- menyimpan tanggal dan jam selesai dan counter backup
		update [dbo].daily_backup set flag=@flag, end_date_time=getdate()
		where intid = @current_backup
	
		-- verifikasi data setelah selesai backup
		declare @backupsetid as int
		select @backupsetid = position from msdb..backupset where database_name=@db_name and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@db_name )
		if @backupsetid is null 
		begin 
				raiserror('verify failed. backup information for database @db_name not found.', 16, 1) 
				update [dbo].daily_backup set flag=0, end_date_time=getdate(), int_count=int_count+1
				where intid = @current_backup
		end
		if @pi_verify=1 
		begin
			restore verifyonly from  disk = @file_name with  file = @backupsetid,  nounload,  norewind
		end
		update [dbo].daily_backup set int_count=int_count+1
		where intid = @current_backup
	
		fetch next from nomor_cursor into @current_backup;
	end;
	close nomor_cursor;
	deallocate nomor_cursor;
   
	select *, durasi=datediff(second, start_date_time, end_date_time) from [dbo].daily_backup
	if exists(select 1 from [dbo].daily_backup where flag=0) 
	set @status_backup='proses backup tidak berhasil.'
	else
	set @status_backup='proses backup berhasil.'

	--drop table [dbo].daily_backup
    exec msdb..sp_send_dbmail
		@profile_name = 'datacenterprofile',
		@recipients = 'jaenul.waton@equity.co.id',
		@subject = 'data center equity life indonesia',
		@body = 'backup database mulai diproses. this is a test e-mail sent from database server equity-db'

    exec msdb..sp_send_dbmail
		@profile_name = 'datacenterprofile',
		@recipients = 'irvan.santoso@equity.co.id',
		@subject = 'data center equity life indonesia',
		@body = 'backup database mulai diproses. this is a test e-mail sent from database server equity-db'

    exec msdb..sp_send_dbmail
      @profile_name = 'datacenterprofile',
      @recipients = 'noor.setiadi@equity.co.id',
      @subject = 'data center equity life indonesia',
      @body = 'backup database mulai diproses. this is a test e-mail sent from database server equity-db'

exec msdb..sp_send_dbmail
      @profile_name = 'datacenterprofile',
      @recipients = 'rizky.faried@equity.co.id',
      @subject = 'data center equity life indonesia',
      @body = 'backup database mulai diproses. this is a test e-mail sent from database server equity-db'

    exec msdb..sp_send_dbmail
      @profile_name = 'datacenterprofile',
      @recipients = 'jaenul.waton@equity.co.id',
      @subject = 'data center equity life indonesia',
      @body = @status_backup --+n'. this is a test e-mail sent from database server equity-db'

    exec msdb..sp_send_dbmail
      @profile_name = 'datacenterprofile',
      @recipients = 'noor.setiadi@equity.co.id',
      @subject = 'data center equity life indonesia',
      @body = @status_backup--+'. this is a test e-mail sent from database server equity-db'

exec msdb..sp_send_dbmail
      @profile_name = 'datacenterprofile',
      @recipients = 'rizky.faried@equity.co.id',
      @subject = 'data center equity life indonesia',
      @body = @status_backup--+'. this is a test e-mail sent from database server equity-db'
go
--exec proc_backup_database 'f:\daily backup' ,3 ,1

ALTER PROCEDURE proc_daily_backup_database
as
exec proc_backup_database 'f:\daily backup' ,3 ,1
GO