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

  • You mean you don't have UDF or such so paths are in one place? And store it on a variable? – gbn Oct 21 '11 at 4:15
  • I never told about how I do the backups :) I just answered on questions "why it is good practice" and "benefit of using backup devices"... In my company, we use different scenarios, because for most systems backup paths, file names and content are described in support instructions. Your scenario with datetime stamps we use too. – Alex_L Oct 21 '11 at 4:34

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


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.

  • 1
    I cannot see why using different files for different bacup files conflicts with using of backup devices. You may have three backup devices for every type of backup. The tasks "delete old backups" and "prevent the backup files from filling" is just question when to use the WITH INIT and WITH NOINIT. – Alex_L Oct 21 '11 at 2:01
  • 1
    Which involves setting RETAINDAYS=X or EXPIREDATE=X on backups to the device and using INIT with NOSKIP (which default with INIT I think). Not as easy to keep track of for cleanups as "delete all files older than X". IMO. – briancarrig Oct 21 '11 at 7:38

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

  • To add: you can even split across different physical disks to speed up the backup times, while using minimal TSQL. – StanleyJohns Oct 21 '11 at 20:23

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.