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]