when a checkpoint was last performed

Question

when a checkpoint was last performed

Viewing 8 posts - 16 through 22 (of 22 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I stand by what I said earlier. What happens if shortly after the full backup you find that a table something has messed up data and you need to restore back to a few hours earlier?

Do you need point in time recovery on this database?

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

sgambale SSCommitted Points: 1709 More actions · Answer 2

I see your point.

I would like point in time recovery. As it is now, they only do a full backup daily. Then loads run and if we have to recover from that backup, they would have to rerun the loads.

We don't want to run the backup after most of the loads are completed because users start to log on soon after.

Suppose I had a job that changes the log backup filename to another name.

I have a Monday log backup file, a Tue, Wed, etc this way I do not overwrite it daily?

Steve

Paul White SSC Guru Points: 150467 More actions · Answer 3

Have you considered using the Maintenance Plan feature built-in to Management Studio? This allows you to 'set and forget' your backups (to a very great extent anyway). It will handle full backups and transaction lo backups with one file per backup as Gail (and I would) recommend. It can also be set to auto-delete old backups after a configurable period of time.

Backing up several times to the same file has no particular advantages that I can think of, and always runs the risk of a failed backup corrupting the whole file...

Take a look at the Maintenance Plan Wizard. See http://msdn.microsoft.com/en-us/library/ms189036(SQL.90).aspx

edit: fixed a spelling mistake and added the link at the end

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Why overwrite at all? Each log backup to an individual file, each full backup to an individual file. Then there's no chance of overwriting the wrong file, they can be becked up themselves based on file date and deleted based on file date when no longer required.

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

sgambale SSCommitted Points: 1709 More actions · Answer 5

yes, you're both right.

I am of the old school, including work with Sybase, and have not used maintenance plans too often.

I am familiar with coding scripts to do this tye of work.

I will take your advice.

Thanks for the quick replies!

Steve

charle-850922 Grasshopper Points: 10 More actions · Answer 6

Gail,

Thanks for all of the great explanations! I am a reluctant DBA and find your posts invaluable. You helped me explain why our transaction logs were filling up this weekend during a large data load

Thanks again!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Pleasure.

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

AppSup_dba SSCrazy Points: 2215 More actions · Answer 8

Question: Why i landed at this thread?

Answer: checkpoint was blocking my differential backup so i wanted to know when did checkpoint start and history.

Cheers