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

using one backup device for multiple databases Expand / Collapse
Author
Message
Posted Monday, January 20, 2014 10:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:50 PM
Points: 43, Visits: 142
Guys I would like to bring up backup devices. When you guys do backups do you create one backup device for every database you want to backup or do you backup multiple databases using one backup device? Also is there a down side to backing up multiple databases at the same time to one backup device. I would assume you would get better performance if you created a separate backup device for each database.
Post #1532747
Posted Monday, January 20, 2014 11:11 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 5,974, Visits: 12,878
never, never backup multiple database to one device. Would make finding the correct backup for a restore a nightmare. Also if you lose that device you lose all your backups.

I don't use backup devices period (hangover from SQL6.5 IMHO), backup to a file. (seperate file(s)per database)


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

Post #1532757
Posted Monday, January 20, 2014 12:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:50 PM
Points: 43, Visits: 142
Thanks for the info I've been backing our db's up to a network share I create one backup device per database. I was just curious if it could be done that way thanks for getting me straight on this topic. Also I need to research back up to a file I never looked at that option.
Post #1532767
Posted Monday, January 20, 2014 3:18 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 15,517, Visits: 27,897
Same here. One database backup on an individual file. No devices. I have done log backups to a single file, but that's still a single file for each database, just one with a whole day's worth of log backups.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1532800
Posted Tuesday, January 21, 2014 12:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
To be honest, I think backup devices are a leftover from tape days (as in when backups were done to tape directly). There's little use I see for them with disk files and they give an extra level of obfuscation (where exactly are my backups going).

Backups to disk, no devices, each backup to its own file with a timestamp as part of the file name



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1532874
Posted Tuesday, January 21, 2014 4:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:50 PM
Points: 43, Visits: 142
Ok thanks for all the info I will have to research backing up to a file instead of using backup devices. BTW Gail I used one of your articles to trouble shoot a tempdb problem this past weekend thanks for writing such a great article.




https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/
Post #1532965
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse