Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Full Backup after changing Changing recovery models


Full Backup after changing Changing recovery models

Author
Message
VastSQL
VastSQL
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3281 Visits: 5067
Hi Experts,

I know we have to run a full backup if we change the recovery model from full--> simple-->full. My question is do we need to run full backup if changed from full--> bulk logged-->full?



TIA
Steve Hall
Steve Hall
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2204 Visits: 11340
No you don't, but you should run a log backup again, to re-gain point-in-time recovery.

http://msdn.microsoft.com/en-us/library/ms190203(v=sql.105).aspx

Steve Hall
Linkedin
Blog Site
VastSQL
VastSQL
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3281 Visits: 5067
Thanks Donor:-D
Perry Whittle
Perry Whittle
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10832 Visits: 16801
Ratheesh.K.Nair (10/9/2012)
Hi Experts,

I know we have to run a full backup if we change the recovery model from full--> simple-->full.

No, you can just run a differential to restart the log chain, no need for a full

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
VastSQL
VastSQL
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3281 Visits: 5067
Thanks Perry
VastSQL
VastSQL
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3281 Visits: 5067
Thanks Everyone.

Do we need to take any kind of backup when changing database recovery model from SIMPLE-->BULK LOGGED-->SIMPLE


Also please let know whether its necessary to take these backups after changing recovery model for those changes to come in or is it as part of recovery??
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54336 Visits: 44637
Ratheesh.K.Nair (11/20/2012)
Thanks Everyone.

Do we need to take any kind of backup when changing database recovery model from SIMPLE-->BULK LOGGED-->SIMPLE


My first question would be why you're changing the recovery model to bulk-logged for a short period.

If you want to initialise the log chain so that you can take log backups, you need to take a full or diff backup after switching to bulk-logged, but the fact that you're switching back to simple suggests you don't want log backups, so I fail to see the point of changing to bulk logged.

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


VastSQL
VastSQL
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3281 Visits: 5067
Thanks Gail for the reply.

We have a proc from developers to delete around 700 millions records from a table,this caused database log files to grow very high that the disk space reduced to 100GB from 1.9TB. The database is in SIMPLE recovery mode and i hope changing it BULK LOGGED RECOVERY model will help.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54336 Visits: 44637
No, it'll make it worse or no change at all.

Simple recovery - minimal logging, log truncated on checkpoint
Bulk-logged recovery - minimal logging, log truncated on log backup (if a full or diff backup has been taken since switching)

So why would you switch to bulk-logged and have the log be harder to reuse with no other gain?

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


VastSQL
VastSQL
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3281 Visits: 5067
Thanks Gail.

I was under the impression that only BULK LOG=minimal logging
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search