LOG Backup and Remote Recovery - Msg 4330

Hi,

We have remote offices running SQL 2000, which are on a small bandwidth pipeline, so we implemented an incremental backup schema where they back up their LOGs overnight and send it to our main office. After an initial full backup, they just keep sending **incremental** LOG backups.

In the main office we restore the full backup of each remote office and from then on do a restore of each night's LOG file (with standby option), so that we keep in sync with them. The issue we have now is that we cannot get regular full backups from the remote (and sometimes independent) offices on a regular basis - maybe only once a *quarter*.

If we use differential backups, the size of each one grows each day, until we get a full backup. Incremental backups thru LOGs seemed to be the only way to go. *HOWEVER*, we now have multiple LOG files sitting on our server in the main office and since the databases that we restored to is in warm-standby and read-only mode, we cannot make a backup of them in-house, without breaking the LOG restore chain.

That also means that we would not be able to easily re-create the environment locally, should something go wrong - we would have to apply all of the LOG restores for (potentially) the whole quarter!

I even tried to create two restored databases - one where I keep it in standby state and ready to receive more incremental LOG backups and another where I would take it out of standby mode (and into recovery mode). At that point I am then able to make a full backup locally of this second database. *BUT*, when I then try to restore the next day's LOG backup to this second database, it fails with an error

- Msg 4330 - Level 16, State 4, Line 1
- The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.

How do I do incremental backups and restores, while maintaining the ability to make a new full backup of the restored database, before the restore sequence is done?

Help !

You can restore the full backup with standby option and subsequent tlogs using with standby option so that the db will be in read-only mode..........

OR you can also restore the full backup and Tlog using with norecovery mode (i.e leave db nonoperational but apply tlogs)

you need to use any of the above 2 options to perform log shipping...........i think you are using the option "Leave db operational so that no tlogs could be applied" or the log backups are not being restored sequentially

Thanxx

Deepak