SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup Device


Backup Device

Author
Message
tew
tew
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 318
Would someone tell why I'd use a disk backup device instead of backup directly to an O/S file ?

merci
SQLBill
SQLBill
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11283 Visits: 1085
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



Ian Yates
Ian Yates
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4446 Visits: 445
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.



WILLIAM MITCHELL
WILLIAM MITCHELL
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3802 Visits: 3083
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.
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23746 Visits: 13698
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.

---------------------------------------------------------------------
tew
tew
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 318
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 !
Mistens
Mistens
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 214
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.


MTNBigGrin
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23746 Visits: 13698
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!
BigGrin

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search