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


Backup Device


Backup Device

Author
Message
tew
tew
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
Points: 519 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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19480 Visits: 1087
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
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8032 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
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5999 Visits: 3128
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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44460 Visits: 13705
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
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
Points: 519 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-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44460 Visits: 13705
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