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...
asked Aug 30, 2017 at 15:09
-
1Can != should. Put everything to its own file (one backup per file, not multiple backups per file)– NicAug 30, 2017 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 SmithAug 30, 2017 at 15:29
-
1If 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.– NicAug 30, 2017 at 15:36
-
1The 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. :)– Kevin3NFAug 30, 2017 at 16:51
-
1It 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.– NicAug 30, 2017 at 17:20
|
Show 3 more comments
1 Answer
You can write all 3 backup types to a backup device, since a backup device is simply a logical location pointer: https://learn.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.
