using one backup device for multiple databases

Question

using one backup device for multiple databases

mstephens1754

SSCommitted

Points: 1650
More actions
January 20, 2014 at 10:21 am

#288616

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

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)

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

mstephens1754 SSCommitted Points: 1650 More actions · Answer 2

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.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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, MVP, M.Sc (Comp Sci)
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

mstephens1754 SSCommitted Points: 1650 More actions · Answer 5

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/