Change of backup type in backup set

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Change of backup type in backup set

Author

Topic

Katsen
Starting Member

2 Posts

Posted - 2010-08-10 : 10:14:18

We use SQL Server 2005.
I've discovered a problem some time ago. When our QA created one of the backups of the database, he chose the "Full" backup type by mistake instead of "Differential", so this full backup was added to the backup set. Now we have following order of backups (e.g.):
1. Full
2. Differential
3. Diff.
4. Full
5. Diff.
That's a problem, because the full backup in the middle of the set caused doubling of backup size, which is not acceptable in our case.
So I wonder if it's possible somehow to change the type of backup in the middle of the set (to reduce its size).

Kristen
Test

22859 Posts

Posted - 2010-08-10 : 10:56:39

Nope, not possible, sorry.

Are you backing up to a single file (appending each time)? If so that's a dangerous practice (generally speaking). It would be better to back up to a brand new file each time, and then copy that file to tape / whatever as a separate exercise.

If you are already backing up to separate files, and provided that backups #1,#2 & #3 are already onto tape, they could be deleted to reclaim the space. A restore, if needed, could be performed with #4 and #5 only - in case that might be a solution to the space problem?

There will be times when you will want to make a Full backup - outside the normal backup routine - so my advice would be to have a system that would allow for that eventuality (whether be accident!! or by design)

Katsen
Starting Member

2 Posts

Posted - 2010-08-11 : 03:06:18

Ok, thanks a lot. Yes, we have a single file with all the backups, and I also find it dangerous (our case is a proof).
We tried to make separate files, but there was a problem with restoring (we solved it by using script, but it's out of my skills in SQL programming)... So we decided just to use single file to restore the database from the context menu. Ok then, we'll be attentive next time on creation of diff. backup.
Thanks, this topic might be closed.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-11 : 04:45:49

It sounds like this is being done manually as well...you really should schedule backups to run automatically so that no one suddenly forgets or it doesn't get done because someone were sick or something.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Kristen
Test

22859 Posts

Posted - 2010-08-11 : 08:20:10

Appended backups dangerous because damage to one is damage to all (plus the size issue you now have, which I hadn't considered as a possible negative until now

)

If you use FULL Recovery Model and TLog backups then you can restore:

Full backup
Full + Diff
or
Full + Optional Diff + All subsequent Tlog backups

For the last one you can use any FULL backup, including an "old one", provided you have all subsequent Tlog backups (and undamaged)

Gives you more options if you find that a Full backup is lost / damaged

Also, FULL backup of a corrupted database will be corrupted too, but usually the Tlog backups are not corrupted - so [with a fair wind] you can restore from earlier FULL backup and all Tlog backups since in order to get around the corruption. You can make a final "Tail" Tlog backup before you start and then you will have zero data loss, even though the original database was corrupted.

Backups = Insurance, and Tlog backups is even-more-insurance - not to mention the option of restoring to a moment-in-time - just before the accidental deletion of half the customers, for example!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-11 : 08:35:51