Optimum Backup Strategy

Question

Post reply

Optimum Backup Strategy

leonp

SSCommitted

Points: 1962
More actions
December 6, 2008 at 8:53 pm

#383529

Hi,

We have a situation at work where the optimal backup and recovery scheme for an application database is under question. Here are the specifics -

The database has 2 kinds of tables -

tables with application reference data (T_r) and

tables with application processed data (T_a)

T_r changes rarely (if at all); while T_a changes every month. The thing to note about T_a data is while most of it is overwritten every month, a small group of tables get appended to every month; so the change is cumulative for some while that of overwriting for most

The database caters to reporting applications for a small group and it seems the recovery time does not have strict time limits to comply with. If I have take to take a guess - 6 hours should provide an upper limit

We are looking at a 50GB database here

Apparently, the current backup and recovery is set to FULL RECOVERY - which I believe means the database has a FULL BACKUP once in a while and there are transaction log/differential backups more often.

The reason for the recent questions on the recovery and backup model have risen owing to a concern the DBA had on an application that inserted a lot of data to the database and the transaction log grew by a lot during this time.

When I review the application, it issues a "Truncate Table" before it starts to clear the table it will load. Once the table is cleaned, it issues Inserts in batches of 10000 rows for the course of the input data. The only way I think the application can cut down on the log size is by issuing bulk-inserts instead of regular inserts. This cannot be done without a lot of changes as the application is on UNIX and the ODBC (the only way of issuing transactions) does not support Bulk inserts.

My Question (FINALLY!!!) -

Is changing the database recovery model from "FULL RECOVERY" to "SIMPLE RECOVERY" model an option? This would ensure the DB truncates the log at the checkpoint. I realize this would change the backup frequency/strategy (and application performance ?) accordingly; but in the light of the facts I presented before about the database, isn't this a reasonable proposal?

You guys have always been a great resource in the past! Thanks in advance!

Leon

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Are you saying that [font="Arial Black"]all [/font]the T_R tables are Truncated before data is added? If so, then, yeah, you could go to SIMPLE recovery mode. Keyword is ALL unless you don't really care about "Point in time" recovery.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

The question you should ask yourself in this situation is the following.

"If the DB fails 10 hours after the last full/diff backup, is the loss of all changes made in those 10 hours acceptable?"

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

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

I think it is better to change the recovery model to Bulk-logged whenever there are bulk inserts which would minimally log the data inserted and also allows you for point in time restore of the subsequent log backups. But since, your application does not allow bulk inserts you can switch the recovery model to SIMPLE but keep in mind that you cannot perfrom log backups and point time restore.

Like Gail Asked:

Is your business ready to lose the data after the last full backup, if any disaster occurs?

leonp SSCommitted Points: 1962 More actions · Answer 4

Thanks a lot you all!

To Jeff's question -

T_r data is never truncated - it has a few DMLs issued very infrequently

To Gail/Krishna's question -

As I understand, since data is changing only when the batch applications run once a month, apart from the 10 days at the end of the month; the remaining 20 days data is the same -

so the answer to the question during the first 20 days is YES

the answer changes during the last 10 days - any data lost can be regenerated through a re-run of the application that generated the data; however, it would be great if we could reduce the downtime of the database for recoveries if need be. I guess point-in-time recovery doesn't mean a lot to our users for this DB.

Knowing only one recovery model can be put in effect, does Simple recovery model with complete backups once in a while and differential backups more often sound reasonable now?

Thanks again!

Leon

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

leonp (12/7/2008)

Thanks a lot you all!

To Jeff's question -

T_r data is never truncated - it has a few DMLs issued very infrequently

To Gail/Krishna's question -

As I understand, since data is changing only when the batch applications run once a month, apart from the 10 days at the end of the month; the remaining 20 days data is the same -

so the answer to the question during the first 20 days is YES

the answer changes during the last 10 days - any data lost can be regenerated through a re-run of the application that generated the data; however, it would be great if we could reduce the downtime of the database for recoveries if need be. I guess point-in-time recovery doesn't mean a lot to our users for this DB.

Knowing only one recovery model can be put in effect, does Simple recovery model with complete backups once in a while and differential backups more often sound reasonable now?

Thanks again!

Leon

That's what I get for posting without coffee... I meant to ask if all T_A is always truncated. If so, then "Simple" would work fine.

I'd likely us the "Bulk-Logged" mode for something like this but, as you stated, bulk loads aren't really an option here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

leonp (12/7/2008)

I guess point-in-time recovery doesn't mean a lot to our users for this DB.

Have you asked them?

I can't tell you what the best backup type is. Only the people who use the system or the management can tell you what's acceptable.

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

maechismo_8514 SSCoach Points: 17510 More actions · Answer 7

Yes, now you can go ahead and change the recovery model to simple if the database is stable most of the time. You can retrieve the data you want from the latest differential and full backups.

How is your backup strategy now? this seems to be interesting because if the data does not change in the first 20 days then you don't have to worry taking backups during that period and just take a full backup on the 20th/21st day and then subsequent differential backups and then stop taking backups on the 1st day ? and cycle through this strategy every month? is that right guys? I am just wondering........

cheers !!!!

leonp SSCommitted Points: 1962 More actions · Answer 8

Jeff,

Not all of T_a is truncated - most of it is - the final reporting tables that house all of the enterprises business for a year have data rolled off the for the 13th month in the past - every month ----- All other T_a is truncated so it could enrich these final reporting tables with the current months data.

Gail/Krishna,

I realize I spoke for the 'users' when I stated - 'the users do not really care for point-in-time recovery" ... but that was while placing myself in their position - I am certain of the audience for the database, the reporting needs of these users and the time constraints that apply. As long as there is the ability for the database to be recovered to the state it was in before the monthly processing started and failed, the users know they could request a re-run of the application for the current month and regenerate what was lost due to the failure in the monthlies. Anything that can restore us closer to point of failure quicker would be a bonus - I say that out of having worked with the business community closely for a while now; I could be wrong and then surprised!

I am inclined to believe what Krishna offered was a very reasonable strategy - but I also want to understand where Jeff and Gail were going with their queries before I draw any conclusions

Please excuse my inexperience! I am clearly the slow kid on the block

Leon

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

leonp (12/7/2008)

but I also want to understand where Jeff and Gail were going with their queries before I draw any conclusions

What I'm trying to get at is whether the users/management will consider restoring the pre-month end backup and rerunning the end of month stuff an acceptable recovery strategy. From what you've said, it will recover the data. The question now is whether the time taken is acceptable.

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

leonp SSCommitted Points: 1962 More actions · Answer 10

leonp

SSCommitted

Points: 1962

December 8, 2008 at 5:36 pm

#909404

Gail,

Thanks again for getting back! I will check with the users - this could be interesting to know since for as long as I have been here, we have never had to do this.

I will let you guys know.

Leon