1

Our development team has a tool that notifies us of issues with our DB backups, but they have not yet given us the ability to exclude certain databases from the alerts - DBs that we do not want backups of are alerting us.

The only way I've found to resolve this are backups to the windows NUL device, however that takes much more time than I expected, and seems to still use processor time, and probabaly take RAM and I/O.

In attempting to speed up the process I tried several sets of backup options, collected data about time, and found that the more backup files I allocate as NUL, the faster it went. SQL 2008 R2 capped me at 64 files, i.e.

BACKUP DATABASE <db_name> TO DISK = 'NUL', DISK = 'NUL', ... 64 disk = 'NUL' being a clear winner for fastest option.

I'm worried about the server resources though, and am now wondering about striking a balance between 'speed' and 'not hogging all resources while this is happening'.

Any advice on resources or other ideas would be awesome. Thanks.

  • If there's reason to believe that you will eventually be able to ignore "missing" backups on DBs you don't want to back up, are the alerts so onerous that you can't just ignore them? Not that being in the habit of ignoring alerts is a good thing, but.... – RDFozz Aug 17 '17 at 20:25
  • Probably a silly question, but what's the point of having the databases in the first place if you aren't going to back them up? – Nic Aug 17 '17 at 20:44
  • @Nic Having a "disposable" database in QA or something that can easily be rebuilt from another environment is pretty common. – BradC Aug 17 '17 at 22:20
  • @BradC it's always easier to restore a backup than rebuild from somewhere else. – Nic Aug 18 '17 at 4:05
  • @RDFozz - The alert tool reports to a web application that shows a big red X and makes the dozens of people involved not trust the alert application. I'm trying to get the application to be trust worthy and only report things that people actually need to address. This application and its development is a cumbersome work in progress and updating it to my current needs is not a type priority for the developers at the moment. – Dave Goldsmith Aug 18 '17 at 23:17
0

So... you are getting alerted when a backup doesn't occur, and you want to suppress these alerts for certain databases, that you already know you don't need to back up?

If this is the case, it is very likely the alert system is reading the contents of msdb.dbo.backupset and other associated tables.

If you don't have the ability to exclude those in the alerts (and you can't just ignore them or make an email rule to delete them), then why don't you fake out the alert system by manually inserting those records yourself?

WARNING: THIS IS PROBABLY DEFINITELY A BAD IDEA, BUT FRANKLY NOT AS BAD AS RUNNING UNNECESSARY HOURS-LONG CPU AND IO-INTENSIVE BACKUPS THAT GO TO NUL AND ULTIMATELY DO THE EXACT SAME THING

So I'm not going to craft the INSERT statements for you (see my note above about this being a bad idea) but you'll probably need correctly-formed and properly associated records in some or all of the following tables:

  • msdb.dbo.backupfile
  • msdb.dbo.backupfilegroup
  • msdb.dbo.backupmediafamily
  • msdb.dbo.backupmediaset
  • msdb.dbo.backupset

I'd run a trace while running a backup to see what exactly it writes.

Good luck, but fixing the alert system is by far the best idea.

  • Yes. The alert systems reads from msdb.dbo.backupset and one other table that is not a SQL system table. I was unaware of the other system backup tables you mention, hence why I was worried hacking msdb.dbo.backupset may be a bad idea. You say definitely a bad idea, but then recommend it? Trace while running the backup is a good idea...thanks. – Dave Goldsmith Aug 18 '17 at 23:22
  • @DaveGoldsmith Clearly the best idea is adjusting the alerts or ignoring the false warnings. But think about what exactly is happening when you run that backup to NUL? A whole lot of unnecessary IO and CPU, and then adding records to the msdb backup tables. That's literally all you are doing now. I'm proposing cutting a corner and doing it directly. Yes, it's a bad idea, but its just slightly less bad than what you are doing now. – BradC Aug 19 '17 at 2:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.