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

Image by Tim foster

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.

4 thoughts on “SQL: Backup SQL Server Databases to Nowhere Immediately After Creation (suggestion)”

  1. You could just run a full backup to the actual backup device… be it tape or a network share.

    Result: the automatic log backups won't fail…. and they can be used for recovery.

    1. Agreed, but that's often even a different person or team of people who decide where that will be. It also avoids you having to deal with the actual location into your database creation script.

  2. This is another reason I like using Ola Hallengren maintenance solution for SQL backups– It detects this situation, & either skips the tran-log backup or if specified, can switch to do a full backup.

Leave a Reply

Your email address will not be published. Required fields are marked *