Sql-server – Can a SQL Server backup device hold both data and log backups

sql server

If I create a backup device – i.e. using the SP:

EXEC sp_addumpdevice 'disk', @bakName, @bakPath;

Can I write both data and log backups to it? Or is it better practice to have different devices for data and log files…

Best Answer

You can write all 3 backup types to a backup device, since a backup device is simply a logical location pointer: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/define-a-logical-backup-device-for-a-disk-file-sql-server. Full, Differential, and T-Log backups can all be written to the device. Think of it as an "alias" or "shortcut" to the backup path.

Further info based on the comments...

The backup device is really just an alias, so you don't have to type out "\server\drive\folder\subfolder...". Now, the different types of backups,

  • full = .bak

  • diff = .bak

  • t-log = .trn

    would likely (NOTE: LIKELY) be in different folders/subfolders. You can store them all in the same folder, but I have found in the past that trying to differentiate between a full and a diff can be problematic, even if you name them differently. I always create the following folder structure for backups:

  • System

  • Full

  • Differential

  • TransactionLog

This way, I can easily write the backups to the correct location, and even more importantly, I can move/copy/purge these files easily based on this file structure. It's much easier in a restore situation to know where all the pertinent files will be located, rather than have to search through hundreds (or thousands) of files in a single folder.

So, in conclusion, for ease of administration, I would highly recommend creating a backup device for full backups (up to you whether you want to separate these out between system and user databases), one for differential backups and one for transaction log backups.

Also, I know it's common (even Ola's Maintenance Solution does this) to create a top level folder based on the instance name, then next level folder is the database name, and under that is each type of backup. This is a pet peeve of mine (yes, personal bias) because then traversing this structure is unnecessarily complicated. But, make sure to create something that will best fit your needs--and remember that the reason for backups is to recover from a problem...so, don't add to that problem by struggling to find the backups you need.

Related Question