March 15, 2010 at 4:29 am
I am trying to design a backup schedule whereby I have 12 databases and each of these DB’s will require a custom type of backup, so for example, highly transactional databases will be backed up every 15 minutes (transaction log backup) and maybe differential once or twice a day and a full backup once a day.
When I have written stored procedures to do this, what I have had to do is hardcode the time that I want the backup to run within the stored procedure, and I had the job running every 15 minutes or so and within the stored procedure, it had IF statements which looked at the hour of the day, for example if the hour of the day is 15, then I want to run a differential backup. I also had If statements to check for the day of the week, so I can choose to backup on a Saturday and not backup on a Sunday etc.
I am thinking that there must be a better way of doing this rather than hard coding the values within a stored procedure, a lookup table springs to mind but I was wondering if another person had done something similar in a better fashion, I’m using native SQL backup here.
March 15, 2010 at 6:01 am
We have some similar needs. Instead of a single catch-all procedure, we have three different scripts, one for full backups, one for differentials and one for logs. Then we set up schedules in Agent to run the scripts. Because we simply use an include list in the procedure, we can define which databases get backed up when using the appropriate script. It seems to work. Generally, most of the time, on most servers, it's fairly common to set all the databases to the same schedule (daily full backup, 15 minute log backup), but we have the flexibility to change as needed by simply creating another schedule and calling the appropriate script.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 15, 2010 at 6:05 am
Why aren't you just scheduling the correct sqlagent jobs to do your backups ?
btw I don't have dynamic backup jobs (meaning generated on a day by day basis) because you have to plan every DBs SLA and DRP anyway.
If you insist on having databases with different backup intervals, IMO it may be advisable to document that in a proprietary table and generate your backup jobs using that info.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 15, 2010 at 6:18 am
Why dont you use Maintenance Plans?
March 15, 2010 at 6:28 am
jshailendra (3/15/2010)
Why dont you use Maintenance Plans?
There is nothing wrong with maintenance plans, but frequently more precise control can be excercised when writing your own scripts.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 15, 2010 at 6:37 am
I generally dont like maintenance plans, I like the idea of having script and customising to business needs etc.
What I had was a stored procedure that can do all 3 different kinds of backups, depending on what parameter you pass to it, i.e L,D,F etc.
Would having 3 different procs still be better or makes no difference.
March 15, 2010 at 6:43 am
I don't want to say it'll be better, but it'll be less complicated. That usually makes things better. I'd handle the scheduling through SQL Agent. That's what it's there for. Again, it just makes something that's a lot of complicated work just a little bit less complicated.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply