use problem... need help

randyv

I was asked to overwrite our production database to a training database so some testing could be done. I ran a full backup of live, restored over training. When asked if I wanted to back up the tail of the tran log I thought "no need, I did a full backup" so I said no and restored the live database as the training database.

Then I got the email below from the users...

Not sure what happened with the copying of live & UPS to training, the data doesn’t look correct.

When I ran the Auto Billing mod in training, some of the orders I show that shipped yesterday(but not yet invoiced in live), did not show on the list to be invoiced. When you look at the order in training, there is not any shipping info. But when you look at the same order in live, the shipping info shows a 1-10-11 date.

We also had an order that didn’t ship until this morning, & it showed on the list to be invoiced.


Can someone tell me what I did wrong? Should I backup the tail end of the tran log? If so, when I restore the full backup, should I restore the tran log first or the backup of live, then the tran log.

Thanks in advance.

randyv

I think I figured out what step I forgot.

I'll post back here if I run into the problem again.

(backup the tran log first, then run the full backup, then restore from the full backup)

Chris Auckland

Thanks for your post.

When you're overwriting a database, backing up the tail of the log prevents you from losing any work that was entered to the database since the full backup you're restoring. I suspect the reason certain records didn't go into the first full backup was because they weren't yet committed to the source database at the time you took the first backup. Taking a t-log backup prior to the full backup doesn't force anything to be committed, so I'm not sure this was the reason the records were present in the second full backup. The full backup will contain all committed data that was present at the time you started the full backup.

Anyway, thanks for letting us know you've solved your issue.

randyv

Chris -

Thanks; yes I agree with your points. Since I was just getting the users a sandbox to test in, I wasn't concerned with 'open' transactions; but after talking with the testers these are precisely the transactions I should have been concerned about.

So I assume I should fetch the last full backup, which I write nightly after close of business, then say yes to backing up the tail. But then what?

I think (but would like you to confirm) that I restore the full database, but leave it non-operational, then after that is done I restore the tail tran log and make it operational? Would that be correct?