STANDBY mode

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

someone@js.com - 03 Feb 2010 19:16 GMT

Hi how to put the database to standby mode for log shipping:
*** Error: The restore operation cannot proceed
because the secondary database 'mydatabase' is not in NORECOVERY/STANDBY
mode.(Microsoft.SqlServer.Management.LogShipping)

Thanks...

Reply to this Message

Tibor Karaszi - 03 Feb 2010 19:48 GMT

You can't. I.e., you need to re-do a prior database restore and get going
from there.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hi how to put the database to standby mode for log shipping:
> *** Error: The restore operation cannot proceed
> because the secondary database 'mydatabase' is not in NORECOVERY/STANDBY
> mode.(Microsoft.SqlServer.Management.LogShipping)
>
> Thanks...

Reply to this Message

someone@js.com - 03 Feb 2010 19:57 GMT

Thanks Tibor,

In this case, I'm going to do a full backup and restore from there, but for
restoring log, I have few days logs in folder, is it the SQL server will
handle that or I have to sepcify from which log file to begin?

> You can't. I.e., you need to re-do a prior database restore and get going
> from there.
[quoted text clipped - 5 lines]
> >
> > Thanks...

Reply to this Message

Tibor Karaszi - 03 Feb 2010 20:05 GMT

I don't know, I'm afraid since I use my own home-brew code for log shipping.
Easiest would be, methinks, to setup a test system a give it a spin.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Thanks Tibor,
>
[quoted text clipped - 13 lines]
>> >
>> > Thanks...

Reply to this Message

someone@js.com - 03 Feb 2010 20:54 GMT

Hi Tibor,
How to put database in standby mode when restoring from a full back?
Thanks.

> You can't. I.e., you need to re-do a prior database restore and get going
> from there.
[quoted text clipped - 5 lines]
> >
> > Thanks...

Reply to this Message

Tibor Karaszi - 03 Feb 2010 21:40 GMT

Use the STANDBY option of the RESTORE command (along with a file to be
created by the restore process where to keep the info needed in order to
undo the UNDO performed by the recovery process). Or, you can also use
NORECOVERY. What you can't use is ECOVERY (or none of these options).

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hi Tibor,
> How to put database in standby mode when restoring from a full back?
[quoted text clipped - 10 lines]
>> >
>> > Thanks...

Reply to this Message

someone@js.com - 05 Feb 2010 15:14 GMT

Thanks Tibor,

I restore as:
RESTORE DATABASE cashx
FROM disk ='C:\Database\SecondaryData\DatabaseBackup\mydb.bak'
WITH
STANDBY='C:\Database\SecondaryData\DatabaseBackup\ROLLBACK_UNDO_DisasterData
base.BAK'

The old log files system will handle: Skipped log backup file. Secondary DB:
xxx

and the database is Standby/Read-Only, but store log failed:
Message
2010-02-05 00:45:26.50 *** Error: Could not apply log backup file
'\\Database\SecondaryData\Logs\x_20100205011501.trn' to secondary database
'cashx'.(Microsoft.SqlServer.Management.LogShipping) ***
2010-02-05 00:45:26.50 *** Error: During startup of warm standby database
'x' (database ID 7), its standby file
('\\X-Database\SecondaryData\Logs\x_20100205054523.tuf') was inaccessible to
the RESTORE statement. The operating system error was '5(Access is
denied.)'. Diagnose the operating system error, correct the problem, and
retry startup.
RESTORE LOG is terminating abnormally.
Processed 0 pages for database 'x', file 'x_Data' on file 1.
Processed 841 pages for database 'x', file 'x_Log' on file 1.(.Net SqlClient
Data Provider) ***

I checked, I don't have tuf file. Can you please help here?

> Use the STANDBY option of the RESTORE command (along with a file to be
> created by the restore process where to keep the info needed in order to
[quoted text clipped - 15 lines]
> >> >
> >> > Thanks...

Reply to this Message

Tibor Karaszi - 08 Feb 2010 07:48 GMT

There is something strange going on here. You say you do STANDBY with below
and undo file name:

STANDBY='C:\Database\SecondaryData\DatabaseBackup\ROLLBACK_UNDO_DisasterDatabase.BAK'

But the error message from SQL Server tells a very different story.
According to the error message, the prior restore was done with below as
standby file:

\\X-Database\SecondaryData\Logs\x_20100205054523.tuf'

And, according to the error message, above file doesn't exist. So, the
process seems messed up. Or perhaps above is the standby file for a
following log restore? Ah, yes, I now see that in the error message. So,
probably the first log restore using the standby file (the one you created)
was OK. But a following log restore failed, because a prior standby file was
inaccessible (network drive not available, somebody deleted the file or
something like that).

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Thanks Tibor,
>
[quoted text clipped - 53 lines]
>> >> >
>> >> > Thanks...

Reply to this Message

someone@js.com - 08 Feb 2010 20:09 GMT

Thanks Tibor,

Using NORECOVERY mode, is it possbile to run some query to simple check the
secondary data, for example, how many records in clients table compare with
primary database.

> There is something strange going on here. You say you do STANDBY with below
> and undo file name:

STANDBY='C:\Database\SecondaryData\DatabaseBackup\ROLLBACK_UNDO_DisasterData
base.BAK'

> But the error message from SQL Server tells a very different story.
> According to the error message, the prior restore was done with below as
[quoted text clipped - 16 lines]
> > FROM disk ='C:\Database\SecondaryData\DatabaseBackup\mydb.bak'
> > WITH

STANDBY='C:\Database\SecondaryData\DatabaseBackup\ROLLBACK_UNDO_DisasterData

> > base.BAK'
> >
[quoted text clipped - 46 lines]
> >> >> >
> >> >> > Thanks...

Reply to this Message

Tibor Karaszi - 08 Feb 2010 20:44 GMT

Are you asking of one can get to a database restore using NORECOVERY? If so,
the answer is no. That is what STANDBY is for (or, of course, RECOVERY).

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Thanks Tibor,
>
[quoted text clipped - 95 lines]
>> >> >> >
>> >> >> > Thanks...

Reply to this Message

someone@js.com - 08 Feb 2010 21:23 GMT

Thansk Tibor,

After swith with RECOVERY, how to make it restoring, ready for restoring
log again? I'm thinking sometimes need to check the data integrity.

> Are you asking of one can get to a database restore using NORECOVERY? If so,
> the answer is no. That is what STANDBY is for (or, of course, RECOVERY).
[quoted text clipped - 10 lines]
> > below
> >> and undo file name:

STANDBY='C:\Database\SecondaryData\DatabaseBackup\ROLLBACK_UNDO_DisasterData

> > base.BAK'
> >>
[quoted text clipped - 20 lines]
> >> > FROM disk ='C:\Database\SecondaryData\DatabaseBackup\mydb.bak'
> >> > WITH

STANDBY='C:\Database\SecondaryData\DatabaseBackup\ROLLBACK_UNDO_DisasterData

> >> > base.BAK'
> >> >
[quoted text clipped - 56 lines]
> >> >> >> >
> >> >> >> > Thanks...

Reply to this Message

Kalen Delaney - 08 Feb 2010 23:46 GMT

Once you have run RECOVERY, you cannot restore any more logs.

It is exactly the purpose of STANDBY to let access the db to check values
and integrity, and then to keep restoring logs.
As Tibor has said already "That is what STANDBY is for"!

Signature

HTH
Kalen
----------------------------------------
Kalen Delaney
SQL Server MVP
www.SQLServerInternals.com

> Thansk Tibor,
>
[quoted text clipped - 119 lines]
>> >> >> >> >
>> >> >> >> > Thanks...

Reply to this Message

someone@js.com - 09 Feb 2010 16:21 GMT

Thanks Kalen,

I'm thinking put it to recovery and then apply next transation log file with
NOCOVERY, can I do that?

I still can't make standby mode works, actually there are no tuf files in
primary/secondary, it related to restore process, and also with nocovery
mode, copy/restore works fine.
How to find out why and fix for stanndby mode?

> Once you have run RECOVERY, you cannot restore any more logs.
>
[quoted text clipped - 26 lines]
> >> > below
> >> >> and undo file name:

STANDBY='C:\Database\SecondaryData\DatabaseBackup\ROLLBACK_UNDO_DisasterData

> >> > base.BAK'
> >> >>
[quoted text clipped - 24 lines]
> >> >> > FROM disk ='C:\Database\SecondaryData\DatabaseBackup\mydb.bak'
> >> >> > WITH

STANDBY='C:\Database\SecondaryData\DatabaseBackup\ROLLBACK_UNDO_DisasterData

> >> >> > base.BAK'
> >> >> >
[quoted text clipped - 63 lines]
> >> >> >> >> >
> >> >> >> >> > Thanks...

Reply to this Message

Tibor Karaszi - 09 Feb 2010 16:31 GMT

> I'm thinking put it to recovery and then apply next transation log file
> with
> NOCOVERY, can I do that?

As already stated: No.

> I still can't make standby mode works, actually there are no tuf files in
> primary/secondary, it related to restore process, and also with nocovery
> mode, copy/restore works fine.

I'm sorry, but above sentence is hard to make heads and tails out of.
Standby work just fine. Probably you are using some tool whcih constructs
the restore commands for you and possibly copies the undo file to some other
location - which is what is causing your problems. You need to troubleshoot
this tool - that is where the problem is, not with the RESTORE and STANDBY
process. Possibly the tool is the log shipping functionality which is
shipped with the product.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Thanks Kalen,
>
[quoted text clipped - 158 lines]
>> >> >> >> >> >
>> >> >> >> >> > Thanks...

Reply to this Message

someone@js.com - 10 Feb 2010 18:44 GMT

Thanks for the help. Works now. It's permission related.

> > I'm thinking put it to recovery and then apply next transation log file
> > with
[quoted text clipped - 65 lines]
> >> >> > below
> >> >> >> and undo file name:

STANDBY='C:\Database\SecondaryData\DatabaseBackup\ROLLBACK_UNDO_DisasterData

> >> >> > base.BAK'
> >> >> >>
[quoted text clipped - 28 lines]
> >> >> >> > FROM disk ='C:\Database\SecondaryData\DatabaseBackup\mydb.bak'
> >> >> >> > WITH

STANDBY='C:\Database\SecondaryData\DatabaseBackup\ROLLBACK_UNDO_DisasterData

> >> >> >> > base.BAK'
> >> >> >> >
[quoted text clipped - 71 lines]
> >> >> >> >> >> >
> >> >> >> >> >> > Thanks...

Reply to this Message

Kalen Delaney - 09 Feb 2010 18:05 GMT

I thought this was pretty clear in my last answer:

>> Once you have run RECOVERY, you cannot restore any more logs.

But apparently not. Let me try again:

>> Once you have restored a log WITH RECOVERY, you cannot restore _any_ more
>> logs WITH RECOVERY, WITH NORECOVERY or WITH STANDBY.

>> Once you have restored a log WITH RECOVERY, you are done restoring!

Signature

HTH
Kalen
----------------------------------------
Kalen Delaney
SQL Server MVP
www.SQLServerInternals.com

> Thanks Kalen,
>
[quoted text clipped - 158 lines]
>> >> >> >> >> >
>> >> >> >> >> > Thanks...

Reply to this Message