LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » 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
Where do you live?: 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: 466
Points: 1,020
Where do you live?: 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
Where do you live?: 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: 466
Points: 1,020
Where do you live?: 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
Where do you live?: 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

viji

Posted: Friday, April 17, 2009 4:59:22 AM

Rank: Newbie

Joined: 4/17/2009
Posts: 0
Points: 3
Where do you live?: bangalore

How much time will take 1gb database backup ?

Please let me know I have just started learning DBA. I would like to know the real time operation times.

Scott Whigham

Posted: Friday, April 17, 2009 9:07:03 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 466
Points: 1,020
Where do you live?: Dallas, TX

I think a 1GB db backup could take as little as 5 seconds to as much as 3 mins, depending on disk speed, CPU availability, and how much actual data is in that 1GB database.

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.