Shipping 2

14 Sep 2006 10:42 PM

CLM

"[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4306: [Microsoft][ODBC SQL
Server Driver][SQL Server]The preceding restore operation did not specify
WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying
WITH NORECOVERY or WITH STANDBY for all but the final step.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally."

I set up my database on the secondary server by restoring from a backup that
I took from the primary (with recovery btw). I then put this database into
"Read Only" mode.

Does anyone have any idea what I am doing wrong? â€

So then I got a response saying that I need to restore with NORECOVERY.
Well, that makes sense so I ran the following script:

"RESTORE DATABASE temcmtest2
    FROM DISK = '\\s3\bulkload\temcmtest2_091406.bak' WITH NORECOVERY,
    MOVE 'temcmtest2_Data' TO 'h:\data\temcmtest2_Data.MDF',
    MOVE 'temcmtest2_Log' TO 'h:\data\temcmtest2_Log.LDF'
GO"

But now when I am running the Wizard to build Log Shipping on the
â€œdatabase temcmtest2 is not in standby mode. Temcmtest2 must be in standby
mode for logging shipping to workâ€.

But when I try to put it in standby mode, via the following script, I canâ€™t
do it because the database is still recovering.

"alter database temcmtest2 set read_only"

Now what am I doing wrong?

15 Sep 2006 12:34 AM

Dan Guzman

> "RESTORE DATABASE temcmtest2
>    FROM DISK = '\\s3\bulkload\temcmtest2_091406.bak' WITH NORECOVERY,
>    MOVE 'temcmtest2_Data' TO 'h:\data\temcmtest2_Data.MDF',
>    MOVE 'temcmtest2_Log' TO 'h:\data\temcmtest2_Log.LDF'
> GO"
>
> But now when I am running the Wizard to build Log Shipping on the
> "database temcmtest2 is not in standby mode. Temcmtest2 must be in
> standby
> mode for logging shipping to work".
>
> But when I try to put it in standby mode, via the following script, I can't
> do it because the database is still recovering.

You need to specify the STANDBY option and file name instead of NORECOVERY.
This will temporarily recover the database and leave it in read-only mode.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quoteHide quote

"CLM" <C**@discussions.microsoft.com> wrote in message
news:93FF5CD3-4E29-4931-9746-7B4D43F129B7@microsoft.com...
> "[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4306: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]The preceding restore operation did not specify
> WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying
> WITH NORECOVERY or WITH STANDBY for all but the final step.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally."
>
> I set up my database on the secondary server by restoring from a backup
> that
> I took from the primary (with recovery btw). I then put this database
> into
> "Read Only" mode.
>
> Does anyone have any idea what I am doing wrong? "
>
> So then I got a response saying that I need to restore with NORECOVERY.
> Well, that makes sense so I ran the following script:
>
> "RESTORE DATABASE temcmtest2
>    FROM DISK = '\\s3\bulkload\temcmtest2_091406.bak' WITH NORECOVERY,
>    MOVE 'temcmtest2_Data' TO 'h:\data\temcmtest2_Data.MDF',
>    MOVE 'temcmtest2_Log' TO 'h:\data\temcmtest2_Log.LDF'
> GO"
>
> But now when I am running the Wizard to build Log Shipping on the
> "database temcmtest2 is not in standby mode. Temcmtest2 must be in
> standby
> mode for logging shipping to work".
>
> But when I try to put it in standby mode, via the following script, I can't
> do it because the database is still recovering.
>
> "alter database temcmtest2 set read_only"
>
> Now what am I doing wrong?
>