SQL: Backup SQL Server Databases to Nowhere Immediately After Creation (suggestion)

Most companies have some sort of ongoing maintenance processes that perform periodic backups of databases. They also have log backups scheduled for any databases that are online and in full recovery model. However, when you first create a database, it might be in full recovery model yet never have had a full backup performed. If your scheduled jobs then try to create a log backup prior to the first full backup, the backup will fail, and that might make your job fail.

One option to avoid this is to always create a backup of a database immediately, as part of the database creation script. You aren’t needing the backup, you just want to avoid the next log backup failing if that happens before the next full backup.

The easiest way to do that is a backup to nowhere ie: the NUL device. You can do that via a command like this:

BACKUP DATABASE [MyNewDB] TO DISK = ‘NUL’;

It might be worth adding that to your database creation scripts for full recovery databases, to avoid throwing errors in your log backup jobs.

2018-09-03