Generate schedule table

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'backupschedule')
DROP TABLE DBO.backupschedule

CREATE TABLE DBO.backupschedule (
backupdate DATETIME
,backupweekday VARCHAR(9)
,backuptype CHAR(1)
,database_name VARCHAR(200)
,backupstartdate DATETIME
,backupenddate DATETIME)

WITH ctedates
AS
(
SELECT CAST('20121112' AS DATETIME) AS [DATE]
UNION ALL
SELECT DATEADD(DD, 1, [DATE])
FROM ctedates
WHERE DATEADD(DD, 1, [DATE]) <= '20131231'
)
INSERT INTO DBO.backupschedule(backupdate, Backupweekday, database_name)
SELECT [date], DATENAME(DW,[date]) , b.name
FROM ctedates AS A
CROSS JOIN sys.databases AS B
WHERE name = 'tempdb'
OPTION (MAXRECURSION 0)

--
WITH ctebackupdays
AS(
SELECT backupdate, backupweekday, ROW_NUMBER() OVER(PARTITION BY backupweekday ORDER BY backupdate) weekdayrank
FROM backupschedule
WHERE backupweekday = 'monday')

UPDATE A
SET backuptype = 'F'
FROM backupschedule AS A
INNER JOIN ctebackupdays AS b
ON a.backupdate = b.backupdate
WHERE b.weekdayrank%2 <> 0

UPDATE backupschedule
SET backuptype = 'D' WHERE backuptype IS NULL

SELECT * FROM backupschedule

;WITH cteTally (N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS APPLY (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
CROSS APPLY (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n))

--INSERT INTO DBO.backupschedule(backupdate, Backupweekday, database_name, backuptype)

SELECT DATEADD(DD,N,'2012-11-12') AS backupdate,
DATENAME(DW,DATEADD(DD,N,'2012-11-12')) AS Backupweekday,
'MyDatabase' AS database_name,
CASE WHEN N % 14 = 0 THEN 'F' ELSE 'D' END AS backuptype
FROM cteTally AS A
WHERE N < 366

Abu Dina (11/12/2012)

Very good! I have much to learn.... Tongue

I'm glad you asked about what I'm doing.

Let me try to explain, I have:

1 SQL Server Instance with 25 databases (I actually have 18 instances but I if I can explain what I'm trying to do with 1 then whatever I come up with I can apply the same to the rest).

The current backup schedule is fortnightly FULL backups and daily DIFFERENTIALS. This is accomplished with two sprocs and two scheduled SQL Agent jobs. Works great-ish

I want to create a new process so that when somebody creates a new database, a schedule is created (Fortnightly FULLS + Daily DIFFS). Then I will have just one sproc that goes through the list if databases in the above table and performs the backup (logging the start and end date + backup size) in this table.

My DBA repository job will then gather this information into a central database for reporting and monitoring.

My server is used by 8-12 users who regularly create staging databases for their work. I was just worried that if somebody creates a database in the middle of the backup cycle, the daily diff job will fail as the new database will not have a full backup.

Hope this is enough to explain what I'm dealing with here.

I'm open to suggestion.

Thanks.

CREATE TRIGGER [DDLTriggerCreateDatabaseBackupSchedule]
ON ALL SERVER
FOR CREATE_DATABASE
AS

declare @data xml
declare @DBName varchar(256)

set @data = eventdata()
set @DBName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')

INSERT INTO master.dbo.backupschedule(backupdate, Backupweekday, database_name, BackupType)

SELECT DATEADD(DD,N,dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))) AS backupdate,
DATENAME(DW,DATEADD(DD,N,dateadd(dd, 0, DATEDIFF(dd, 0, getdate())))) AS Backupweekday
,b.name
,CASE WHEN N % 14 = 0 THEN 'F' ELSE 'D' END AS backuptype
FROM dbo.Tally AS a
CROSS JOIN sys.databases as b
WHERE N < 780
and name = @DBName
GO

CREATE PROCEDURE [dbo].[sp_QbaseBackup] @BackupFolderPath varchar(400)

AS

SET NOCOUNT ON

DECLARE DatabaseList CURSOR
FOR SELECT database_name, BackupType
FROM backupschedule
WHERE backupdate = dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))
and backupstartdate is null and backupenddate is null
ORDER BY database_name ASC

DECLARE @DBName VARCHAR(255)
DECLARE @BackupTtype CHAR(1)

DECLARE @SQL VARCHAR(max)

OPEN DatabaseList

FETCH NEXT FROM DatabaseList INTO @DBName, @BackupTtype

WHILE @@FETCH_STATUS = 0

BEGIN

-- log the start time of the backup
update dbo.backupschedule
set backupstartdate = GETDATE()
where database_name = @DBName
and backupdate = dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))

IF @BackupTtype = 'F'

BEGIN
SET @SQL = 'BACKUP DATABASE [' + @DBName + '] TO DISK = N' + CHAR(39) + @BackupFolderPath + @DBName +
'.complete'' WITH NAME = N' + char(39) + @DBName + ' Complete Backup' + char(39) + ', CHECKSUM'
EXEC(@SQL)
END
ELSE IF @BackupTtype = 'D'
BEGIN
SET @SQL = 'BACKUP DATABASE [' + @DBName + '] TO DISK = N' + CHAR(39) + @BackupFolderPath + @DBName +
'.Differential'' WITH DIFFERENTIAL, NAME = N' + char(39) + @DBName + ' differential backup' + char(39) + ', CHECKSUM'
EXEC (@SQL)
END

-- log the end time of the backup
update dbo.backupschedule
set backupenddate = GETDATE()
where database_name = @DBName
and backupdate = dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))

-- Let's also get the backup size
update a
set backupSizeMB = b.backup_size / 1048576
from master.dbo.backupschedule as a
inner join msdb.dbo.backupset as b
on a.database_name = b.database_name
where a.database_name = @DBName
and backupdate = dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))
and b.backup_set_id = (select MAX(backup_set_id)
from msdb.dbo.backupset as c
where b.database_name = c.database_name)

FETCH NEXT FROM DatabaseList INTO @DBName, @BackupTtype

END
CLOSE DatabaseList
DEALLOCATE DatabaseList

RETURN 0

CREATE TRIGGER [DDLTriggerRemoveDatabaseBackupSchedule]
ON ALL SERVER
FOR DROP_DATABASE
AS

declare @data xml
declare @DBName varchar(256)

set @data = eventdata()
set @DBName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')

delete master.dbo.backupschedule
where database_Name = @DBName

SELECT DATEADD(DD,N,dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))) AS backupdate,
DATENAME(DW,DATEADD(DD,N,dateadd(dd, 0, DATEDIFF(dd, 0, getdate())))) AS Backupweekday
,@DBName
,CASE WHEN N % 14 = 0 THEN 'F' ELSE 'D' END AS backuptype
FROM dbo.Tally AS a
WHERE N < 780