November 12, 2012 at 8:44 am
I'm trying to generate a backup schedule table where I do FULL backups starting tonight and then do another FULL backup in two weeks’ time on Monday again.
Please see my attempt below:
I'm interested to know of alternatives?
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_nameVARCHAR(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
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
November 12, 2012 at 11:39 am
well to generate the table here is a faster and more direct way. it uses a tally table to generate the dates (posted as a cte Tally Table). of course since it just uses a modulo function on N it will kinda break tomorrow so you will just have to adjust it slightly if you run it tomorrow.
;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
As far as why you are generating this calendar table i would like some more info on that.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 12, 2012 at 12:25 pm
Very good! I have much to learn.... 😛
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.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
November 13, 2012 at 3:22 am
Anyone care to comment about my solution of creating a schedule table to control the backups on a SQL instance?
Is this a bad idea?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
November 13, 2012 at 10:33 am
Abu Dina (11/12/2012)
Very good! I have much to learn.... 😛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.
I like it if it works in your environment. the benefit i see is that if you need to change a database to nightly fulls or weekly fulls you just have to modify the table and the new schedule takes effect with out having to change allot of jobs. the problem i see is if some one creates a database and it does not get entered into the table it wont be backed up.
I would love to see the code from your SP that handles the backups.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 13, 2012 at 10:42 am
capnhector (11/13/2012) the problem i see is if some one creates a database and it does not get entered into the table it wont be backed up.
Ah but in my utility instance I created the following trigger:
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
I would love to see the code from your SP that handles the backups.
It's not that great to be honest. I'm sure with your SQL skills you can do better but here goes anyway:
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
Feel free to have a go at me for such bad coding practice lol!
I'm testing this but I'm not really sure how well it's going to work.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
November 13, 2012 at 11:10 am
Forgot to mention that I also have another trigger for when a database is removed:
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
We have a strange setup here where we don't have any need for Point in Time disaster recovery so I have made sure that all the DBs are in SIMPLE recovery. The backup schedule is what was setup originally before I joined the company three months ago and I'm happy with it.
If there is a need to perform T-Log backups in future then obvioulsy I will have to modify my sproc to handle T-log backups too.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
November 13, 2012 at 1:33 pm
Nice DDL Trigger on the create database however you can avoid the cross join to sys.databases by using the following query:
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
Makes no difference in the long run im just personally a fan of shortening code.
As far as the stored procedure goes, most people around here cringe when they see DECLARE CURSOR however i think those who do will also agree this is one of the few cases where you have to use the cursor. The other thing i may look into is a TRY CATCH block for error handling so if one backup fails it does not take out the rest and you can log the failure into a table or have an email alert generated by using something like sp_send_dbmail. It also looks like you went and read Jeff Moden's article on tally tables, if you did not find it here is the link http://www.sqlservercentral.com/articles/T-SQL/62867/ Its a really good read.
The one thing i would suggest though is to move the backup schedule out of the master database and into a utility or tools database. again personal pet peve of any sort of user generated data in the system databases.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 14, 2012 at 2:27 am
capnhector (11/13/2012)
Nice DDL Trigger on the create database however you can avoid the cross join to sys.databases by using the following query:Makes no difference in the long run im just personally a fan of shortening code.
Ah yes of cousre. That's just lazy coding on my part. The cross join is a left over from a previous query where I generated the original backup schedule for all existing user databases.
capnhector (11/13/2012)
As far as the stored procedure goes, most people around here cringe when they see DECLARE CURSOR however i think those who do will also agree this is one of the few cases where you have to use the cursor. The other thing i may look into is a TRY CATCH block for error handling so if one backup fails it does not take out the rest and you can log the failure into a table or have an email alert generated by using something like sp_send_dbmail. It also looks like you went and read Jeff Moden's article on tally tables, if you did not find it here is the link http://www.sqlservercentral.com/articles/T-SQL/62867/ Its a really good read.The one thing i would suggest though is to move the backup schedule out of the master database and into a utility or tools database. again personal pet peve of any sort of user generated data in the system databases.
Yup... all valid points. Will make the suggested changes.
Thanks very much for your input.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply