Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have vendor production backup that needs to be restored onsite daily. The most frustrating part is that their installation is on SQL 2008 but the DB mode is SQL 2000. sigh!!!

The database in our site will primarily be readonly. The vendor runs any required updates/data changes to the DB on their site during the day and will send the backups to us on a daily basis. It is a smaller bak file now but will grow over time. The goal is to restore incremental updates only.

Possible workaround that I could think of are the following:

Keep the DB in our side in read only mode (after full backup is restored) and request the vendor to give us the diff backups every day. Of course if they take a full backup , we are back to square one.

Partition the database and request the vendor to give the partition group backups. This may be difficult because not all tables may be qualified for partition.

Replication could be an option but the vendor is not ready for this.

Bulk insert for individual objects could be an option but again the vendor is not ready for it.

CDC feature could possibly help here but again not in SQL 2000.

If you can share any other ideas that you could think of, that would be very helpful. (I agree the first step should be to asking them to upgrade)

1 Answer
1

If you could stay readonly than a good alternative to replication
could be log shipping. The vendor would create transaction log
backups. And send those backup to you. You then apply those
backups.You can't restore differential only, you would still need to
reply a full first. Another benefit of (delayed) logshipping is that, since the source is at the vendor (where you have no control over what they do with it, you might want to build in some buffer (in time) that when they make a huge mistake, you simply pause the logshipping redo, so you still have a good copy)

For restoring filegroups/files you need transaction log restores as well. It can work but it highly depends on the growth rate vs the transaction log rate if I would prefer this above option 1, simply log shipping. Furhtermore, partitioning your database and table just for the sole purpose of solving your replica problem wouldn't be the route I would pick first but I have some questions for you later.

Replication sounds like a very good option if you think the restoring of transaction log backups becomes to much work.

What prohibits sql 2000 from doing Bulk Inserts? However, if you think logshipping is tedious, keeping track of updating every single object sounds like a potentially more troublesome strategy.

I have no experience with cdc. somebody else has to help out on that.

Couple of questions for you:

How big is the initial database size?

What will be the daily, monthly growth rate?

What will be the daily growth rate of the transaction log?

is it possible to have both locations in the same domain?

Is it possible to build a secure connection between the two locations?

What is the bandwidth between the to locations? what is the latency?

If we have some numbers, it's easier to provide you with a better advise.

Thanks. Even with Tran log, you will need a full backup to be applied first. I will update my original question to be clearer that a full backup is already done initially followed by diff back. I prefer restoring diff backup as compared to tran log(s). Like I said, it is a small backup for now and so we will be doing a full restore for sometime now. I am looking for a long term solution.
–
DBAuserAug 29 '12 at 20:11

1

The problem is, the diff backup is based on the differential bitmap of the preceding full backup. So every time you get a new diff it will be bigger then the previous diff. Since it will have all changes since the last full backup. Not just the changes since the last diff backup. De trans log backups however will only contain the latest transactions.
–
Edward DortlandAug 29 '12 at 20:24

Yes and in that case, it is as good as doing a full restore :). Unless they create a media set for tran logs, it will be be tedious to restore at our end too..I am not sure if they are even ready to keep the database in full recovery mode to do these backups :-).
–
DBAuserAug 29 '12 at 20:40

Your question: What prohibits sql 2000 from doing Bulk Inserts? However, if you think logshipping is tedious, keeping track of updating every single object sounds like a potentially more troublesome strategy Answer: Nothing. The comment of SQL 2000 was just to state that they are NOT ready with upgrading yet due to cumbersome effort, and so backup of individual tables is definetly not an option. Also, they have fareign key enforced on each tables and so copying each table by itself is not a reliable/optimal option.
–
DBAuserAug 30 '12 at 13:21

~100MB *The project manager stated it will grow(?) over the month and so he is looking for all the options out there.This is really in a premature state as we just got the product a week back. * That was one of the quesiton I had for them. I will know more soon *No *No. We thought of linked servers etc but our business units want this in-house "only". *FYI:They wil run the daily batch on their site and send us the EOD data for us. Currently they are sending as .bak as the DB size is very small. Like I said, we are planning for the future.I really appreciate your time and input on this
–
DBAuserAug 30 '12 at 13:53