Full Backup after changing Changing recovery models

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Full Backup after changing Changing recovery...

18 posts, Page 1 of 2

1 2 »»

Full Backup after changing Changing recovery models

Rate Topic

Display Mode

Topic Options

Author

Message

Ratheesh.K.Nair

Posted Tuesday, October 09, 2012 4:42 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 2,415, Visits: 3,375

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

Post #1370300

BrainDonor

Posted Tuesday, October 09, 2012 4:54 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 1,400, Visits: 6,894

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

BrainDonor
Linkedin

Post #1370311

Ratheesh.K.Nair

Posted Tuesday, October 09, 2012 5:05 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 2,415, Visits: 3,375

Thanks Donor BigGrin

Post #1370315

Perry Whittle

Posted Tuesday, October 09, 2012 5:42 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:53 AM
Points: 5,204, Visits: 11,158

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" Wink

Post #1370339

Ratheesh.K.Nair

Posted Tuesday, October 09, 2012 6:00 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 2,415, Visits: 3,375

Thanks Perry

Post #1370344

Ratheesh.K.Nair

Posted Tuesday, November 20, 2012 1:14 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 2,415, Visits: 3,375

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??

Post #1386718

GilaMonster

Posted Tuesday, November 20, 2012 1:55 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

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 2008, MVP
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

Post #1386731

Ratheesh.K.Nair

Posted Tuesday, November 20, 2012 1:59 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 2,415, Visits: 3,375

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.

Post #1386736

GilaMonster

Posted Tuesday, November 20, 2012 2:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

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 2008, MVP
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

Post #1386737

Ratheesh.K.Nair

Posted Tuesday, November 20, 2012 2:05 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 2,415, Visits: 3,375

Thanks Gail.

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

Post #1386739

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions