SQL Server Backup Question:

[Apollo21]

Good Morning.

The search function is non-operational at the moment, so I do not know if this question has already been asked.

We are running 2003 OS with SQL Server 2000(SP4).
There is a 90 Gig database that needs to be backed up on a daily basis. In the past I had the backups scheduled at 2:30 A.M. to do a Regular backup then a transaction log backup. I was out for a week and while I was away a co-worker decided that it might be a good idea if we maintained the backup schedule indicated above except that we do the transaction log backup first then the database backup.
I can find nothing in books online that supports that type of schedule. I had always thought that the backup of the data then the log was what constituted the entire backup and that to mess with the sequence could possibly cause some problems down the road.

Just to be clear, there are no other log backups or Differential backups done during the day, just the 1 data and 1 log backup a day. Yes, I am in the process of lobbying for more backups to get done for the day, primarily log backups.

My question is: Can you execute the transaction log backup and then the data backup without negative results occurring? (By this I mean will we have problems restoring the information?)

Thank you for your time in reading this.
I look forward to your answer(s).

Apollo21

 

[onpnt]

I would say no. You'll have to restore the prior backup and then the log file. meaning you can't apply a previous tran backup to a db backup that was done at a later time.

To be honest that just doesn't amke any sense to me at all. That sceanrio has never been brought up that I know of and I don't remember ever covering it. It just doesn't fit into a disaster/recovery model if you ask me.

But...I'm off to try it.

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[onpnt]

As I remember the trans backup is attached to the last backup state so you can't even select it. Unless there is a way to force it by file selection which doesn't sound stable

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[ptheriault]

My question is: Can you execute the transaction log backup and then the data backup without negative results occurring? (By this I mean will we have problems restoring the information?)

The whole purpose of a Tlog backup is that it runs after your full backup and you should run tlog backups throughout the day. This allows you to recover to a point in time. If you have to restore your database you restore the full backup first then roll the db forward by applying the log backups. The tlog backup before the full backup will be useless.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[onpnt]

I should alter my comments slightly and not say the schema is useless (well it is) but you will be able to restore the previous backup and then you'll have to apply that entire trans log to it. So you could recover but it would be ugly

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[Apollo21]

Good Morning:

I understand that the Tran backup must be attached to the previous bakup, but I you apply the tran backup of the next morning to the data backup of the day before will that not give you point in time recovery for all of the previous day?

Just asking. I am trying to come up with a case why it should be done the normal way as opposed to what I have outlined above.

Thank you for your thoughts.
More thoughts/ opinions/facts are always valued.

 

[onpnt]

yes that will work to the log backup point in time which will be an entire day and is not that great

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[Apollo21]

Thank you all for your input.
This will help me in my meeting in about 30 minutes.

Apollo21.

 

[ptheriault]

If you're only doing one tlog backup that is scheduled for just before your full data backup, then you are not able to recover to a point in time.

What happens if at 12:00 PM you lost your server and had to recover? All you would have is your full db backup to apply!
Now if you had run tlog backups every 30 min after your full backup you would be able to recover to 11:30.

See my point!

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[onpnt]

I think Paul worded that much better than I so it was clearer

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[Apollo21]

Yes, I understand concerning the point in time recovery.
When I was reading Books Online the first thing it told me to do in the event of a problem is to back up the tran log.
So if I had a problem at 12:00 P.M. and did a backup of the tran log at that time, could I not recover to 11:30 A.M. anyway. (I am just playing Devils Advocate here).

The only reason I am challenging this is because my bosses are under the impression that 1 tran log backup a day is good enough.

Thanks Everyone!

 

[chrissie1]

If you do them only once a day then you will loose a days work at most. If you do them every 30 minutes then you will loose only 30 minutes at most. What do you prefer?

Christiaan Baes
Belgium

"My old site" - Me

 

[ptheriault]

So if I had a problem at 12:00 P.M. and did a backup of the tran log at that time, could I not recover to 11:30 A.M. anyway.

??? absolutely NOT

You're assuming the server is even running! What if you've lost the array where the .ldf file is..
You should be running 1 full daily backup (get a copy off the server when it completes.)
Then incremental log backup. (And copy each one or your log backup device off of the server after each backup.)

You don't want to leave anything to chance.



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

The only reason I am challenging this is because my bosses are under the impression that 1 tran log backup a day is good enough.

No offense...but your boss doesn't know anything about best practices for data recovery if he thinks one log backup is good.

I would recommend that you try the following.

Get a test server...
Install SQL and try to recover your database to a point in time using his theory of 1 log backup a day!

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Apollo21]

No Offense taken.

I am going to do just what you have mentioned. I procured the space for this yesterday, so I will be moving forward.

Thanks,
Apollo21

 

[ptheriault]

Make sure after you've set up your backups you test a restore to any point in time.

- Paul
- If at first you don't succeed, find out if the loser gets anything.