venders backup plan - Backup, Recovery and Disaster Recovery - LearnSqlServer.com's SQL Server Forums

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » SQL Server Database Administration » Backup, Recovery and Disaster Recovery » venders backup plan

venders backup plan

Options

Previous Topic · Next Topic

steve15

Posted: Wednesday, June 06, 2007 8:11:40 PM

Rank: Newbie

Joined: 10/3/2006
Posts: 4
Points: 21
Location: new york

We have a daily backup job that has these steps.

the database is in full recovery

1. backup log with truncate_only
2. DUMP DATABASE ''dbname TO dbname_data_dump WITH INIT "this happens at 1:00am"

Then there is a plan setup to backup the transaction log every hour

I think this is wrong. the recovery windows is only good to the last full backup.

Scott Whigham

Posted: Friday, June 08, 2007 9:14:50 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

There are a few critical pieces missing, Steve.

How frequently does the database change? In other words, what is the volatility of the data?
What is the size of the database?
What is the size of the log?

Once we know those, I think we can help you determine a proper strategy.

Also, you write that "the recovery windows is only good to the last full backup." but previously you write, "...here is a plan setup to backup the transaction log every hour" - those two contradict each other. Your absolute worst-case scenario is to have a one-hour window of lost data if you are backing up the log once per hour, not until the last full backup. Since you are in FULL recovery model, you are able to backup (and subsequently restore) the logs.

Here are a few questions you can ask the vendor:

Why are they truncating the log when you are already backing it up once per hour?
The DUMP DATABASE command was depracted NINE years ago - why are they still using it?

steve15

Posted: Friday, June 08, 2007 9:36:39 AM

Rank: Newbie

Joined: 10/3/2006
Posts: 4
Points: 21
Location: new york

This database changes hourly.
The database is 1gig in size
The Log is 1.5 gig in size

You are right on the recovery window

Thanks

Scott Whigham

Posted: Friday, June 08, 2007 9:54:07 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

The backup strategy seems fine to me except for the log truncation which just is illogical. Also, why is the log 1.5GB and the database 1GB? For a 1GB database, the log *generally* would be about 150-400MB. Is it mostly free space?

steve15

Posted: Monday, June 11, 2007 11:22:55 AM

Rank: Newbie

Joined: 10/3/2006
Posts: 4
Points: 21
Location: new york

Scott,

Thanks there install script created the log to be 1.5gig not sure why I asked with no answer yet.

Thanks Again

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.