What are the implications of backing up a database more than once to the same file

Question

What are the implications of backing up a database more than once to the same file

SQLUserC

SSCrazy

Points: 2345
More actions
June 1, 2012 at 2:47 pm

#257241

Hey all,
At the firm I work at, we use a little stock query to create and name backups using the backup command. One thing that I've often wondered is what are the implications of backing up the same database more than once to the same file? I see that the size of the backup doubles, but when I restore from that double-backup file, it seems like the original database restores fine.
That's good, but I was wondering if there are any other issues that could come up when restoring from a backup that has been backed up multiple times to the same file. This concern sometimes comes up and I'm wondering if I should be worried about it or if it isn't a big deal aside from the additional space used.
Also, what if I backup a database in the morning, then back it up to the same file in the evening after some changes have been made? Which version of the database would get restored? I'd appreciate any information anyone can provide. Thanks.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

If you are talking about appending subsequent backups to the same file, the problem arises if the file becomes corrupt. If this happens, you lose all the backups contained in the backup file. This is why you see experienced DBA's running each backup to individual files, and then most likely copying them to multiple locations (disk and tape usually).

SQLUserC SSCrazy Points: 2345 More actions · Answer 2

But if you write multiple backups to the same file, will SQL know to restore the most recent one in time?

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

SQLUserC (6/1/2012)
But if you write multiple backups to the same file, will SQL know to restore the most recent one in time?

When you're doing the restore, particularly through the GUI, you select which of those backups in the file are to be restored, so you'll have to pay attention when doing the restore.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

SQLUserC SSCrazy Points: 2345 More actions · Answer 4

Ah, I see. If you restore using the restore command, will you automatically restore the most recent backup?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

SQLUserC (6/1/2012)
Ah, I see. If you restore using the restore command, will you automatically restore the most recent backup?

Nope, you have to specify which file to use using the FILE = clause of the RESTORE command.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

IIRC, it defaults to restoring the first file if not specified, which makes sense as you normally don't use the FILE = 1 when there is only one file to restore from.

SQLUserC SSCrazy Points: 2345 More actions · Answer 7

I see. I like to have people backup databases using a stock query because it ensures that they follow certain naming conventions. I don't want it to ever backup two times to the same file. Is there a way to have it check if the given name already exists? If there is, I'll bet I can make something that can tag something on to the end of the name.

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

Easiest way to to append the datetime to the filename.

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

SQLUserC SSCrazy Points: 2345 More actions · Answer 9

Good idea. We depend the date ATM and so it was causing some issues. Thanks.

Edit: Actually, the one issue with that is that the restore query we use also requires that the date be specified; specifying a full datetime may be onerous for some. If there is a way to check if a file exists in SQL please let me know.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

Let SQL Server do the work for you.

Just automate the whole thing using a Maintenance Plan to make a full backup at night using a SQL Agent job and automatically delete backups older than a certain number of days. The Maintenance Plan will automatically append a date/time stamp to the file name, and will create a new backup file each time it runs.

Also, use a Maintenance Plan to schedule a transaction log backup to run every 15 minutes so that you can recover to any point in time.

ScottPletcher SSC Guru Points: 101120 More actions · Answer 11

As for the restore, it's very easy to create a script to have SQL restore the last backup of a db.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".