When taking backups from databases, is it "good practice" to use logical backup device for backup location? What is the benefit of using backup devices?
Imagine that you may have lot of backup-jobs. Full backups, differential backups, transaction log backups... Now imagine that you need to move all your backups from local disk to SAN. if you use backup devices, you just need to re-create the devices with paths to new disk. But if your backup scripts are written to use full paths, you need to rewrite every job, every job-step to change these paths.
But there can be scenario when you need no to use the backup devices, for example, when you use some custom scenario where you dynamically changes the backup path or file name (for example, adding date/time to folder or file name).
I prefer one file per backup
I don't want to:
- query a backup device to see what is stored there
- copy an entire backup device to get one database
- FTP or otherwise shift more I need around the place
If you have TB size database, partitions, filegroups and want to do partial backups/restores then these problems are multiplied. In a DR situation, I want everything simple and clear.
Most (large and small) shops I've been in do this: it is simpler to deal with self describing files.
The "device" concept goes back to Sybase, SQL Server 6.5 and earlier. Here you had disk devices too Separate MDFs and LDFs were introduce with SQL Server 7
A logical backup device is just an abstraction from the physical device.
This simplifies switching between physical devices, as you can change the logical device definition rather than have to edit your backup scripts or maintenance packages.
I used a logical backup device for a reporting system where we wanted to perform multiple restores and didn't want to script out the full path. We could easily change the definition of the dump device without having to change our scripts.
I believe the same concept applies to using a logical dump device for tape backups, although we never dumped straight to tape.
Those are the only benefits that I am aware of right now.
While they have their uses I prefer to stick with one full, differential or transaction log backup per file, as it makes it easier to run clean up scripts to delete old backups and prevent the backup drives from filling up.
To my understanding, the benefits to using a backup device are simplifying scripts, making it easier, if you have to move drives or devices and, the most useful, in my opinion; being able to have a uniform backup script across multiple servers where the backup location can be defined on a server by server basis, but the backup device can remain the same across all servers. For example "Primary_Backup_Location".
Besides what has already been written above, the only other reason I could see using backup devices for is if you are space constrained. If you have a 100 GB DB, and 2 drives with 60 GB of free space each, you can create a backup device on each drive and backup across both. The caveat is that you then need both devices to restore.
--of course you could write to multiple backup files instead of the devices too..