Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup Device Expand / Collapse
Author
Message
Posted Tuesday, February 05, 2008 4:12 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 04, 2012 11:40 AM
Points: 89, Visits: 318
Would someone tell why I'd use a disk backup device instead of backup directly to an O/S file ?

merci
Post #451941
Posted Tuesday, February 05, 2008 5:01 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:24 PM
Points: 3,240, Visits: 493
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



Post #451958
Posted Tuesday, February 05, 2008 9:11 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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.



Post #451989
Posted Thursday, February 07, 2008 7:40 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 12:35 PM
Points: 1,496, Visits: 2,634
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.
Post #452739
Posted Thursday, February 07, 2008 7:50 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:25 PM
Points: 5,849, Visits: 12,586
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.


---------------------------------------------------------------------

Post #452749
Posted Thursday, February 07, 2008 9:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 04, 2012 11:40 AM
Points: 89, 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 !
Post #452815
Posted Thursday, February 07, 2008 11:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 2:16 PM
Points: 13, Visits: 157
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
Post #452856
Posted Thursday, February 07, 2008 11:22 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:25 PM
Points: 5,849, Visits: 12,586
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!
:D


---------------------------------------------------------------------

Post #452864
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse