February 5, 2008 at 4:12 pm
Would someone tell why I'd use a disk backup device instead of backup directly to an O/S file ?
merci
February 5, 2008 at 5:01 pm
What do you mean by a disk backup device? Do you mean a USB device or a removable drive?
If so, one reason would be if you have to supply the backup to another server, regardless of whether it is local or not. Sometimes network connections are slow, in that case it is easier to use a removable hard drive and just connect it to whichever server needs the restore.
//added after posting//
Another reason is what happens if the hard drive crashes on the server? You lost the backup. But if it is a removable drive, it is most likely still good.
-SQLBill
February 5, 2008 at 9:11 pm
Are you asking why you wouldn't create a named backup device in MS SQL and use it to indirectly specify the file name for your backups?
I've never bothered to create a "backup device" in SQL but I can see the benefits
1. You can look at the list of backup devices configured on your sever to see where all of your backups reside.
2. If you need to change the location for a backup you needn't dig into some SQL script, possibly within one or more jobs in SQL Agent. You merely need to change the backup device's location.
I'm not sure what other benefits backup devices provide. Perhaps if you wished to switch to direct tape backups in future (yuk - not recommended) then you could just change your backup device to point to tape rather than a file.
I think the main reasons are 1 & 2 above - the location of your backups has an extra level if indirection which makes it easier to change all of your backup destinations in one easy to access area.
SQLBill's suggestions are still very valid but I think he answered a different question - why would you use a removable disk device (such as a USB drive) rather than backing up to an internal HDD?
You obviously know where to create the backup devices but in case you don't, in SSMS you go to your server, then "server objects" then right-click on backup devices to create a new one.
February 7, 2008 at 7:40 am
Whitey,
Are you talking about using sp_addumpdevice to create a logical backup device? For example, in SS2000 you might use
EXEC sp_addumpdevice 'disk', 'par_pos_1', 'C:\Program Files\Microsoft SQL Server\MSSQL$STATION9\BACKUP\wm_par_pos.bak'
...and then you would backup or restore by referring to the named backup device, instead of having to specify the full path and file name.
It seems to me that it would only be useful for developer backups and restores, because the file name is hard-coded into the device definition.
February 7, 2008 at 7:50 am
If you want to stripe your backups to multiple files you have to use backup devices to do this, you cannot stripe using the 'to disk = 'fi'lename' ' option.
apart from reason above I prefer to backup to OS file directly because its obvious where the backup is going and you dont have the extra level of abstraction using a backup device causes.
---------------------------------------------------------------------
February 7, 2008 at 9:39 am
a sincere thank-you to everyone's responses. It sounds to me like creating a backup device for backups to disk isn't done - named devices are for non-disk backups.
I'm from the Oracle/UNIX realm and am consistently performing a technical translation. Essentially I'm trying to figure out "what SHOULD I do, NOT what CAN I do".
thanks again !
February 7, 2008 at 11:08 am
There have been situations that sql server started to split memory fragmentation errors and the only reason from Microsoft was because there were too many backup devices.
What I would say is you can use them but do so wisely.
MTN:D
February 7, 2008 at 11:22 am
whitey (2/7/2008)
I'm from the Oracle/UNIX realm and am consistently performing a technical translation.!
welcome to a real DBMS where you don't need another database and a week long course to be able to do backups\restores!
😀
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply