SQL Server Performance Forum – Threads Archive
When do I use a backup device?
I am doing backups of my databases using a maintenance plan but no backup devices appear afterwards. Do I need to create them manually or are they used for a specific purpose? I am a little confused as to when I should use them and for what purpose. I am using SQL 7 standard edition with SP3 I remember on SQL 6.5 having to create a backup device and then backing up into that device – one per db. Now however I can backup to disk without creating a device first – what problems could I face if I don’t create a device? ThanksMaint.plans deals with creating backup to files rather to devices.
Disk backup devices are files on hard disks or other disk storage media and are the same as regular operating system files. Referring to a disk backup device is the same as referring to any other operating system file. HTH _________
Satya SKJ
So how do you decide when to use a maintenance plan and when to backup to a device? Are there pro’s and con’s to each? At the moment I use the maintenance plans but I will want to do differential backups at some point and from what I can tell a maintenance plan will not do this?
If you have a lot of transaction log backups, say you make them every 10 minutes then it can sometimes be nice to but them in a single backup device. We do this in some environments to get rid of long file version lists that will appear in some third party tape backup programs. When it comes to maintenance plans it is correct that you can not specify differential backups. You need to create your own scripts. /Argyle
Refer BOL for BACUP topic… as it The DIFFERENTIAL clause, to specify that only the parts of the database that have changed after the last database backup was created are backed up. …
BACKUP DATABASE MyNwind
TO MyNwind_1
WITH DIFFERENTIAL
_________
Satya SKJ
Pushing all the transaction logs into a single device must mean that backing them up to tape is quicker than lots of small files BUT I can see that if this single file gets corrupted in some way then you will lose ALL the transaction logs…. Also I discovered that you use backup devices to backup to multiple tape drives say.
Its not suggestable to push all Tlog to a single device rather maintain file backups for each set of Tlog. And use those file set to TAPE for another additional set of backup. _________
Satya SKJ
Most of the time, I don’t use backup devices as I am too lazy to create them. The only time I go to the effort to create them is if I have limited disk space and I want my backups to overwrite the previous backup on the backup device. If I don’t create backup devices, then when I use the maintenance plan to create a backup, it won’t delete the old backup until the new one is complete. This can be a problem on some servers that have limited space. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
]]>