Sql-server – Any potential complications from ms sql backup db to disk = ‘nul’, disk = ‘nul’, …

backupnullsql-server-2008

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.

Best Answer

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.

Related Question