
/****************************************************************************/
/* This stored procedure will get all the databases that need to be dumped
   at the time of inquiry */
/****************************************************************************/
if exists (select * from sysobjects where name = "sp_MSbackup_now" and sysstat & 7 = 4)
begin
	drop procedure sp_MSbackup_now
end
go

print ""
print "Creating sp_MSbackup_now"
print ""
go
create procedure sp_MSbackup_now as
declare @now datetime, @dayofweek smallint, @hour smallint, @minute smallint,
@elapsed int, @monthyear varchar(30)

set nocount on

/* */
/* Get the current date and time. */
/* Parse out the relevant parts of the date */
/* */

select @now = getdate()
select @dayofweek = datepart(weekday,@now)
select @monthyear = substring(convert(varchar(12),getdate()),1,12)

/* */
/* Create a temporary table that holds data on what needs to be dumped */
/* */

create table #spdumptab
(
id	       int	       Not Null,       /* Unique identifier */
name	       varchar(30)     Not Null,       /* Name of database to be dumped */
owner	       varchar(30)     Not Null,       /* Name of the database owner */
ddump	       varchar(30)     Null,	       /* Database dump device */
ldump	       varchar(30)     Null,	       /* Log dump device */
datacntrltype  smallint        Null,           /* Control type */
logcntrltype   smallint        Null            /* Control type */
)

/* */
/* Check all the databases that are dumped daily, weekly, and biweekly */
/* Note: The dump can only occur if the start time(HH:MM) is > the last dump <= now */
/* */

insert into #spdumptab
select Event_id, Database_name, Database_owner,Database_dump,Log_dump,NULL,NULL
from MSscheduled_backups
where Enabled = 1					     /* Dump turned on */
      and ((convert(smallint,Day) = @dayofweek) or Frequency = 1)		     /* Dump today or Daily */
      and Frequency <= 14				     /* Freq daily, weekly, or biweekly */
      and datediff(day, Last_dump, @now) >= convert(smallint,Frequency)	     /* Freq time has elapsed */
      and @now >= convert(datetime, @monthyear + Start_time)
      and datediff(hour, Last_dump, @now) >= convert(smallint,Frequency)*24    /* Freq time has elapsed */
      and datediff(minute, Last_dump, @now) >= convert(smallint,Frequency)*24*60    /* Freq time has elapsed */

/* */
/* Check all the databases that are dumped monthly */
/* Note: First we get this week number, then do the same criteria as the
	 Daily, weekly, bi-weekly dump.
	 The dump can only occur if the start time(HH:MM) is > the last dump <= now */
/* */

declare @rundate datetime, @weekno smallint     /* Get this week number */
select @rundate = @now
select @weekno = 1
while datepart(month,dateadd(day,-7,@rundate)) = datepart(month,@now)
begin
    select @weekno = @weekno + 1
    select @rundate = dateadd(day,-7,@rundate)
end

insert into #spdumptab
select Event_id, Database_name, Database_owner,Database_dump,Log_dump,NULL,NULL
from MSscheduled_backups
where Enabled = 1					     /* Dump turned on */
      and (convert(smallint,Day) = @dayofweek)				     /* Dump today */
      and Frequency >= 31				     /* Freq monthly */
      and (convert(smallint,Frequency) - 30) = @weekno			     /* Week of month */
      and datediff(day, Last_dump, @now) >= 30		     /* Freq time has elapsed */
      and @now >= convert(datetime, @monthyear + Start_time)
      and datediff(hour, Last_dump, @now) >= convert(smallint,Frequency)*24    /* Freq time has elapsed */
      and datediff(minute, Last_dump, @now) >= convert(smallint,Frequency)*24*60    /* Freq time has elapsed */


update #spdumptab set datacntrltype = (select cntrltype from master..sysdevices s where
#spdumptab.ddump = s.name)

update #spdumptab set logcntrltype = (select cntrltype from master..sysdevices s where
#spdumptab.ldump = s.name)

update #spdumptab set datacntrltype = (select 5 from MSscheduled_backups s where
#spdumptab.id = s.Event_id and s.Database_dump like '%+')

update #spdumptab set logcntrltype = (select 5 from MSscheduled_backups s where
#spdumptab.id = s.Event_id and s.Log_dump like '%+')


set nocount off

/* */
/* Output the values to the daemon */
/* */

select id = id, name = name, owner = owner, ddump = ddump, ldump = ldump,
dcntrl = datacntrltype, lcntrl = logcntrltype from #spdumptab
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
dump tran master with truncate_only
go
/************* END DUMP THE TRANSACTION LOG **********************************/

