1

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...

  • 1
    Can != should. Put everything to its own file (one backup per file, not multiple backups per file) – Nic Aug 30 '17 at 15:28
  • ahhh.. the answer seemed so simple. what are the dangers of having everything in a single file? I will have multiple copies of that file – Zach Smith Aug 30 '17 at 15:29
  • 1
    If you put them in a single file, one piece of corruption and you have lost all of your backups, and cannot recover anything. Different files for each and every backup reduces your risk significantly. – Nic Aug 30 '17 at 15:36
  • 1
    The DBA that comes after you will curse you for not following the industry standard of one file per backup with datetime in the file name. :) – Kevin3NF Aug 30 '17 at 16:51
  • 1
    It would sound reasonable, but it is a recipe for disaster. I would recommend going with one file per backup. There are multiple options out there to help you do this (for free), just to make life simpler for you. – Nic Aug 30 '17 at 17:20
2

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.

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.