SQL Server: why to use a backup device?

I prefer one file per backup

• DB_FULL_yyyymmddhhnn.bak
• DB_DIFF_yyyymmddhhnn.bak
• DB_LOG_yyyymmddhhnn.bak

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

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

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.