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

log

I get an error message:
"Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'My_db' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column
in sys.databases"
The collumn log_reuse_wait_desc says: LOG_BACKUP
There is not much free space on the file server. How can I force or
set the option so that I can run the query? I don't need any backup
(data or log) either.
Thanks.On Jun 6, 4:35 pm, SB <othell...@.yahoo.com> wrote:
> I get an error message:
> "Msg 9002, Level 17, State 2, Line 1
> The transaction log for database 'My_db' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column
> in sys.databases"
> The collumn log_reuse_wait_desc says: LOG_BACKUP
> There is not much free space on the file server. How can I force or
> set the option so that I can run the query? I don't need any backup
> (data or log) either.
> Thanks.
I did try to shrink the log (and data) but the problem persists
(LOG_BACKUP).|||SB wrote:
> On Jun 6, 4:35 pm, SB <othell...@.yahoo.com> wrote:
> I did try to shrink the log (and data) but the problem persists
> (LOG_BACKUP).
>
Hi,
You'll need to backup the log file in irder to free up some space within
the file. If you run the BACKUP LOG command with the NO_LOG option, it
will truncate your logfile and free up some space.
Once you've done that, I suggest that you either do a regular backup of
the log or set the database in SIMPLE recovery mode. In that case it
will truncate the log automatically.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

log

I get an error message:
"Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'My_db' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column
in sys.databases"
The collumn log_reuse_wait_desc says: LOG_BACKUP
There is not much free space on the file server. How can I force or
set the option so that I can run the query? I don't need any backup
(data or log) either.
Thanks.On Jun 6, 4:35 pm, SB <othell...@.yahoo.com> wrote:
> I get an error message:
> "Msg 9002, Level 17, State 2, Line 1
> The transaction log for database 'My_db' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column
> in sys.databases"
> The collumn log_reuse_wait_desc says: LOG_BACKUP
> There is not much free space on the file server. How can I force or
> set the option so that I can run the query? I don't need any backup
> (data or log) either.
> Thanks.
I did try to shrink the log (and data) but the problem persists
(LOG_BACKUP).|||SB wrote:
> On Jun 6, 4:35 pm, SB <othell...@.yahoo.com> wrote:
>> I get an error message:
>> "Msg 9002, Level 17, State 2, Line 1
>> The transaction log for database 'My_db' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column
>> in sys.databases"
>> The collumn log_reuse_wait_desc says: LOG_BACKUP
>> There is not much free space on the file server. How can I force or
>> set the option so that I can run the query? I don't need any backup
>> (data or log) either.
>> Thanks.
> I did try to shrink the log (and data) but the problem persists
> (LOG_BACKUP).
>
Hi,
You'll need to backup the log file in irder to free up some space within
the file. If you run the BACKUP LOG command with the NO_LOG option, it
will truncate your logfile and free up some space.
Once you've done that, I suggest that you either do a regular backup of
the log or set the database in SIMPLE recovery mode. In that case it
will truncate the log automatically.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

log

I get an error message:
"Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'My_db' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column
in sys.databases"
The collumn log_reuse_wait_desc says: LOG_BACKUP
There is not much free space on the file server. How can I force or
set the option so that I can run the query? I don't need any backup
(data or log) either.
Thanks.
On Jun 6, 4:35 pm, SB <othell...@.yahoo.com> wrote:
> I get an error message:
> "Msg 9002, Level 17, State 2, Line 1
> The transaction log for database 'My_db' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column
> in sys.databases"
> The collumn log_reuse_wait_desc says: LOG_BACKUP
> There is not much free space on the file server. How can I force or
> set the option so that I can run the query? I don't need any backup
> (data or log) either.
> Thanks.
I did try to shrink the log (and data) but the problem persists
(LOG_BACKUP).
|||SB wrote:
> On Jun 6, 4:35 pm, SB <othell...@.yahoo.com> wrote:
> I did try to shrink the log (and data) but the problem persists
> (LOG_BACKUP).
>
Hi,
You'll need to backup the log file in irder to free up some space within
the file. If you run the BACKUP LOG command with the NO_LOG option, it
will truncate your logfile and free up some space.
Once you've done that, I suggest that you either do a regular backup of
the log or set the database in SIMPLE recovery mode. In that case it
will truncate the log automatically.
Regards
Steen Schlter Persson
Database Administrator / System Administrator