Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question

6 Answers

up vote 4 down vote accepted

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

share|improve this answer
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
up vote 5 down vote

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

share|improve this answer
up vote 4 down vote

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.

share|improve this answer
up vote 4 down vote

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.

share|improve this answer
up vote 3 down vote

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.

share|improve this answer
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
up vote 1 down vote

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.

-chris

--of course you could write to multiple backup files instead of the devices too..

share|improve this answer
+1 on the space issue. – StanleyJohns Oct 21 '11 at 20:23
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.