September 18, 2009 at 3:26 pm
This is more of a statement than a question. Today I took a small test database, in full recovery mode, and did a full backup followed by a log backup. Then I set it in simple recovery, then back to full recovery.
It appears that you have if you still have a full bak on disk, you can do just a differential backup before resuming log backups. I wanted to check this since we need to set our production database in simple recovery during schema updates, etc to avoid having large transactions fill up the log.
September 21, 2009 at 4:46 am
Indianrock (9/18/2009)
This is more of a statement than a question. Today I took a small test database, in full recovery mode, and did a full backup followed by a log backup. Then I set it in simple recovery, then back to full recovery.It appears that you have if you still have a full bak on disk, you can do just a differential backup before resuming log backups. I wanted to check this since we need to set our production database in simple recovery during schema updates, etc to avoid having large transactions fill up the log.
Just tried it, if you restore the full, diff and a log backup in 1 operation, it works. after doing what you have done and switching recovery models.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 21, 2009 at 5:47 am
ah, thanks Silverfox. I should have done a restore like that to be absolutely sure everything worked. Our full backups ( and restores ) take several hours so avoiding the need to do a full backup after switching recovery modes is a big help since the differentials only take about an hour on average.
Now if the schema or other database update was significant, who knows how long the differential might take. I just thought of something else, some of the upcoming updates are going to involve significant partitioning into multiple data files so we might want to do a full backup afterwards anyway, although one would think the differential covers it.
September 21, 2009 at 7:44 am
The diff changes the LSN, so I'd expect this to work. The Diff would pick up all changes done in simple mode and then you could resume log backups.
I think the process is that after coming out of simple mode, you need a full or diff backup to get things square. If you make massive updates, the size difference between them might not be large. I'd tend to just take a full after coming out of simple mode, but that's me.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply