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

Will changing the recovery model on the fly prevent ability to restore properly Expand / Collapse
Author
Message
Posted Tuesday, April 1, 2014 7:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 7:38 AM
Points: 15, Visits: 52
We have a very large import job on a critical database. At the start of the job we set the recovery model to simple and at the end of the job we set it back to full. Will this prevent us from being able to restore the database if a failure happends prior to the next full backup?
Post #1556988
Posted Tuesday, April 1, 2014 7:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 5,308, Visits: 9,700
Yes. Either switch to Bulk Logged instead of Simple, or take a full backup as soon as you switch back to Full.

John
Post #1556990
Posted Tuesday, April 1, 2014 7:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 7:38 AM
Points: 15, Visits: 52
Thank you for your quick response John. I want to be sure I understand. If the database is set to Full recovery model (or Simple) and we change it to Bulk Logged prior to running our import then back to Full (or Simple)...we would need to perform a full backup to ensure recovery integrity? Or did you mean if I was just going from Simple to Bulk Logged then back to Simple it would be fine?
Post #1556999
Posted Tuesday, April 1, 2014 8:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 5,308, Visits: 9,700
If Simple is involved anywhere then your backup log chain is going to be broken.

Full -> Simple -> Full: take full backup immediately after reverting to Full
Full -> Bulk Logged -> Full : no extra backup required. Note that certain operations may make point-in-time recovery during the time you're in Bulk Logged mode impossible.

Search this site (or the internet) for articles on "Managing Transaction Logs" for more information.

John
Post #1557006
Posted Tuesday, April 1, 2014 8:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 7:38 AM
Points: 15, Visits: 52
Thanks again!
Post #1557011
Posted Tuesday, April 1, 2014 8:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 5,308, Visits: 9,700
Oops - double post!
Post #1557018
Posted Tuesday, April 1, 2014 10:27 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 @ 3:00 AM
Points: 42,774, Visits: 35,871
The mentioned article: http://www.sqlservercentral.com/articles/Administration/64582/


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 #1557101
Posted Tuesday, April 1, 2014 11:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
John Mitchell-245523 (4/1/2014)

...
Full -> Simple -> Full: take full backup immediately after reverting to Full
...
John


IIRC a differential backup after switching back to Full Recovery model works as well.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1557124
Posted Wednesday, April 2, 2014 2:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 5,308, Visits: 9,700
Lynn Pettis (4/1/2014)

IIRC a differential backup after switching back to Full Recovery model works as well.

Yes, good point. Thanks Lynn.

John
Post #1557357
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse