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

Change in Recovery Model Expand / Collapse
Author
Message
Posted Tuesday, March 26, 2013 1:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:23 AM
Points: 9, Visits: 68
Hi,

In my Datawarehouse project(implemented 2 yrs back) we want to change the Recovery Model from Simple to Full.

What will be the impact? How the performance will be affacted. What things I need to take care of?

Thanks,
Anupam
Post #1435285
Posted Tuesday, March 26, 2013 2:11 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: Yesterday @ 3:21 PM
Points: 42,495, Visits: 35,566
You'll need to schedule log backups. Frequency should be based on the RPO of the database mostly and on the volume of log records second.


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 #1435304
Posted Tuesday, March 26, 2013 4:43 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:10 AM
Points: 112, Visits: 1,207
Simple recovery model allows restore database only up to the last full backup, while in full recovery model, you can get point in time recovery.

You can change recovery model from simple to full as it is desirable in any production enviornment.

however, you would have to take a full backup immediately after changing recovery model from simple to full. Also, as suggested you need to perform periodic transactiong log backup based on your workload.


SQL Database Recovery Expert
Post #1435379
Posted Friday, April 19, 2013 10:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:32 AM
Points: 6,196, Visits: 13,354
prettsons (3/26/2013)
Simple recovery model allows restore database only up to the last full backup

Up to the last differential backup actually, as long as you're taking diff backups of course


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1444502
Posted Friday, April 19, 2013 10:30 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 23,089, Visits: 31,635
prettsons (3/26/2013)
Simple recovery model allows restore database only up to the last full backup, while in full recovery model, you can get point in time recovery.

You can change recovery model from simple to full as it is desirable in any production enviornment.

however, you would have to take a full backup immediately after changing recovery model from simple to full. Also, as suggested you need to perform periodic transactiong log backup based on your workload.


Is this really true?


You can change recovery model from simple to full as it is desirable in any production enviornment.


There may be environments where using the FULL recovery model may not be needed. I may not use it in a data warehouse environment. It would depend on the size of the data warehouse database, how often that data is loaded. I may go for a weekly full backup during a slow time for data warehouse access (perhaps on a weekend) and then run differentials after each load. Small enough database maybe a full backup after every load.

What this means is FULL recovery model may not be desirable in any production evironment. Look at your RPO and RTO requirements and your databases.



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 #1444509
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse