Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Full Backup after changing Changing recovery models Expand / Collapse
Author
Message
Posted Tuesday, October 9, 2012 4:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:40 AM
Points: 2,567, Visits: 3,799
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
Posted Tuesday, October 9, 2012 4:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:01 AM
Points: 1,539, Visits: 8,133
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
Blog Site
Post #1370311
Posted Tuesday, October 9, 2012 5:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:40 AM
Points: 2,567, Visits: 3,799
Thanks Donor
Post #1370315
Posted Tuesday, October 9, 2012 5:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 6,182, Visits: 13,329
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"
Post #1370339
Posted Tuesday, October 9, 2012 6:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:40 AM
Points: 2,567, Visits: 3,799
Thanks Perry
Post #1370344
Posted Tuesday, November 20, 2012 1:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:40 AM
Points: 2,567, Visits: 3,799
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
Posted Tuesday, November 20, 2012 1:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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
Posted Tuesday, November 20, 2012 1:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:40 AM
Points: 2,567, Visits: 3,799
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
Posted Tuesday, November 20, 2012 2:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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
Posted Tuesday, November 20, 2012 2:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:40 AM
Points: 2,567, Visits: 3,799
Thanks Gail.

I was under the impression that only BULK LOG=minimal logging
Post #1386739
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse