• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

Backup policy question

Hi experts,

I need some help with a backup policy.

I've got a 153GB database.
I need to recover data if there is data loss at least for the passed hour.
Is there anyway to do it without backing up 153GB every hour?

I assume there is something with the transaction log that can be done.

If so, how can I recover the data of the passed hour from it?

Thanks in advance.


Leandro Nuñez.

PS: sorry about my english.
  • 4
  • 2
  • 2
  • +2
3 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please start reading here:

in short, you cannot recover without having to restore from a full backup first, and then applying the transaction log backups.
you can try setting up a full backup and a differential backup jobs. your full backup for example is done daily then your differential will be per hour. if you need to restore, you can use the daily backup at a starting point then apply the diferrentials to the point of time that you need.
Shaun KlineLead Software EngineerCommented:
Here is one option:
Start with a full backup from the prior weekend.
Run Differential backups nightly. (Or you can run full backups every night, if you wish.)
Run Transactional backups hourly.

To restore, start with the last full backup.
Then apply the last differential backup.
Then apply all of the transactional backups up until the point that you need.

There is also the capability to roll forward transactions from the transaction backup to a point in time, but I've never had experience performing this feature.

As angelIII stated, you must at least start with a full backup.
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

LeandronnAuthor Commented:
Ok, thank you all for the answers.

Cleaning up:


Once there is a failure, I should

2nd RESTORE EVERY LOG BACKUP..... HERE: should I use any option in case I only need to restore ONE table? Is that possible?

Thanks again.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, that is correct.
note that for all the restores, except the last one, you use WITH NORECOVERY, to be able to continue restoring
usually, you restore the db under another name, if you just want to restore "some" data

and no, you cannot restore just a single table.
What I suggest is to add a differential backup as well. This way you do only one FULL per week and one DIFF per day, overall the backups will take less time:

1. One weekly FULL backup
2. One daily DIFFerential backup (except for the day when you make the FULL backup)
3. One hourly (or even more often if you like) TRANsaction log backup.

To recover:

1. Restore from the LAST FULL backup WITH NO RECOVERY
2. Restore from the LAST DIFF backup WITTH NO RECOVERY
3. Restore from the ALL transactional backups for the day since the last DIFF backup until the last transaction log backup:

So let's say you have:
 - FULL backup on Monday 12 AM once a week
 - DIFF backup Tue-Sun 12 AM daily
 - TRAN backup hourly every day between 1:00 AM - 11:59:59 PM

If something goes wrong on Friday 9:45 AM

You do:
1.Restore FULL from Monday
2.Restore DIFF from Friday
3.Restore 9 TRAN backup files from 1:00 AM to 9:00 AM
One more thing:

When you restore the TRAN backups you will do it in order 1 to 8 WITH NO RECOVERY and the last one (9) WITH RECOVERY.

And the best way and easiest at the same time is through a Maintenance Plan. In Management Studio(2008) expand server node > Management > Maintenance > right click New Maintenance Plan > enter a name and click OK.

In the MP designer you will add 2 Subplans: FULL, DIFF and TRANS and schedule them. The schedule is at the SUbplan level. One subplan for each backup type.

Within the subplan you wil drag and drop a Backup Database Task and then just follow the steps in the window.  In the Backup type you will choose the FULL, Differential or Transaction LOG acordinly and then you will specify what databases to backup. The rest is pretty much self explanatory.
Correction: you will add 3 Subplans:...
LeandronnAuthor Commented:
Thank you so much
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now