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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.