Boot.ini file: standby

Showing posts with label standby. Show all posts

Thursday, February 16, 2012

Bringing Standby server online when in "no recovery mode"

Hi,

I am trying to set up Log Shipping. Though I have the following problem...
I want to set up LS without any user being able to connect. As I understand, this means I have to select the "No recovery mode" in the "Add Destination Database" window when creating the Log Shipping Maintenance Plan.
This all works fine. I set this up, no user can connect to the database (it shows status: (Loading) ), perfect.

However, the problem I encounter is when I want to bring the Standby server online.
I run the 'sp_change_primary_role' SP on the primary server... works fine.
I run the 'sp_change_secondary_role' on the standby server... errors:

Server: Msg 927, Level 14, State 2, Line 1
Database 'Northwind' cannot be opened. It is in the middle of a restore.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Server: Msg 14440, Level 16, State 1, Procedure sp_change_secondary_role, Line 49
Could not set single user mode.

So what am I doing wrong? Am I forgetting something? I think i must do. The only way to get the secondary (standby) online is to do a "detach - attach", as far as I know.
I can not check its Properties or do a restore or backup of this database either.

Any help would be very much appreciated.

Thanks in advance and kind regards,

Jan V.Run the following command, to open the database:

restore database (name of database) with recovery

This will trigger a recovery of the database, and reset the database to "open" from "loading"

Bring up the standby server in sql 2k

Hi,
How do I bring up the standby server online in sql2k by restoring the last
log backup(backed up with noinit).
This log file has been restored with standby clause. can I restore again
with recovery?
Thanks,Hi
Yes.
RESTORE <dbname> WITH RECOVERY
will bring the DB into a usable state.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uAtveOg9FHA.2264@.tk2msftngp13.phx.gbl...
> Hi,
> How do I bring up the standby server online in sql2k by restoring the last
> log backup(backed up with noinit).
> This log file has been restored with standby clause. can I restore again
> with recovery?
> Thanks,
>|||Thanks,
But when I doing restore with recovery give me error says Server: Msg 4326,
Level 16, State 1, Line 11
The log in this backup set terminates at LSN 6791000023918900001, which is
too early to apply to the database. A more recent log backup that includes
LSN 6794000015810000001 can be restored.
I don't know why?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:um6tXTg9FHA.252@.TK2MSFTNGP15.phx.gbl...
> Hi
> Yes.
> RESTORE <dbname> WITH RECOVERY
> will bring the DB into a usable state.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uAtveOg9FHA.2264@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > How do I bring up the standby server online in sql2k by restoring the
last
> > log backup(backed up with noinit).
> > This log file has been restored with standby clause. can I restore again
> > with recovery?
> >
> > Thanks,
> >
> >
>|||Can you post that exact RESTORE command you are executing?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:u4ki7ag9FHA.1332@.tk2msftngp13.phx.gbl...
> Thanks,
> But when I doing restore with recovery give me error says Server: Msg 4326,
> Level 16, State 1, Line 11
> The log in this backup set terminates at LSN 6791000023918900001, which is
> too early to apply to the database. A more recent log backup that includes
> LSN 6794000015810000001 can be restored.
> I don't know why?
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:um6tXTg9FHA.252@.TK2MSFTNGP15.phx.gbl...
>> Hi
>> Yes.
>> RESTORE <dbname> WITH RECOVERY
>> will bring the DB into a usable state.
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:uAtveOg9FHA.2264@.tk2msftngp13.phx.gbl...
>> > Hi,
>> >
>> > How do I bring up the standby server online in sql2k by restoring the
> last
>> > log backup(backed up with noinit).
>> > This log file has been restored with standby clause. can I restore again
>> > with recovery?
>> >
>> > Thanks,
>> >
>> >
>>
>|||This is the script I excuted with error.
restore log LDNPROD from disk = 'path.bak' with recovery
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ObMuJOn9FHA.3660@.TK2MSFTNGP09.phx.gbl...
> Can you post that exact RESTORE command you are executing?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
news:u4ki7ag9FHA.1332@.tk2msftngp13.phx.gbl...
> > Thanks,
> > But when I doing restore with recovery give me error says Server: Msg
4326,
> > Level 16, State 1, Line 11
> > The log in this backup set terminates at LSN 6791000023918900001, which
is
> > too early to apply to the database. A more recent log backup that
includes
> > LSN 6794000015810000001 can be restored.
> >
> > I don't know why?
> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > news:um6tXTg9FHA.252@.TK2MSFTNGP15.phx.gbl...
> >> Hi
> >>
> >> Yes.
> >>
> >> RESTORE <dbname> WITH RECOVERY
> >> will bring the DB into a usable state.
> >>
> >> --
> >> --
> >> Mike Epprecht, Microsoft SQL Server MVP
> >> Zurich, Switzerland
> >>
> >> IM: mike@.epprecht.net
> >>
> >> MVP Program: http://www.microsoft.com/mvp
> >>
> >> Blog: http://www.msmvps.com/epprecht/
> >>
> >> "mecn" <mecn2002@.yahoo.com> wrote in message
> >> news:uAtveOg9FHA.2264@.tk2msftngp13.phx.gbl...
> >> > Hi,
> >> >
> >> > How do I bring up the standby server online in sql2k by restoring the
> > last
> >> > log backup(backed up with noinit).
> >> > This log file has been restored with standby clause. can I restore
again
> >> > with recovery?
> >> >
> >> > Thanks,
> >> >
> >> >
> >>
> >>
> >
> >|||If you just want to make the database accessible (fail over), try executing (as suggested by Mike):
RESTORE DATABASE LDNPROD WITH RECOVERY
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:eRuvPeo9FHA.1224@.TK2MSFTNGP12.phx.gbl...
> This is the script I excuted with error.
> restore log LDNPROD from disk = 'path.bak' with recovery
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ObMuJOn9FHA.3660@.TK2MSFTNGP09.phx.gbl...
>> Can you post that exact RESTORE command you are executing?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:u4ki7ag9FHA.1332@.tk2msftngp13.phx.gbl...
>> > Thanks,
>> > But when I doing restore with recovery give me error says Server: Msg
> 4326,
>> > Level 16, State 1, Line 11
>> > The log in this backup set terminates at LSN 6791000023918900001, which
> is
>> > too early to apply to the database. A more recent log backup that
> includes
>> > LSN 6794000015810000001 can be restored.
>> >
>> > I don't know why?
>> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> > news:um6tXTg9FHA.252@.TK2MSFTNGP15.phx.gbl...
>> >> Hi
>> >>
>> >> Yes.
>> >>
>> >> RESTORE <dbname> WITH RECOVERY
>> >> will bring the DB into a usable state.
>> >>
>> >> --
>> >> --
>> >> Mike Epprecht, Microsoft SQL Server MVP
>> >> Zurich, Switzerland
>> >>
>> >> IM: mike@.epprecht.net
>> >>
>> >> MVP Program: http://www.microsoft.com/mvp
>> >>
>> >> Blog: http://www.msmvps.com/epprecht/
>> >>
>> >> "mecn" <mecn2002@.yahoo.com> wrote in message
>> >> news:uAtveOg9FHA.2264@.tk2msftngp13.phx.gbl...
>> >> > Hi,
>> >> >
>> >> > How do I bring up the standby server online in sql2k by restoring the
>> > last
>> >> > log backup(backed up with noinit).
>> >> > This log file has been restored with standby clause. can I restore
> again
>> >> > with recovery?
>> >> >
>> >> > Thanks,
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>

Thursday, February 16, 2012

Bringing Standby server online when in "no recovery mode"

Bring up the standby server in sql 2k

Bring up the standby server in sql 2k

Bring up the standby server in sql 2k

Boot.ini file

Blog Archive

About Me