Manual log shipping

Hello:
I've implemented a stand-by server solution, where the
tran log backup from the primary server gets restored to
the secondary server at every 15-min interval.
I understand that there are some limitations with this
approach (could not implement MS SLS as our business unit
could not afford to purchase the Ent. Ed.), and was
wondering if anyone has encountered any other issues or
observations when implementing a similar manual log
shipping process, other than my own observations listed
below:
Log Shipping will fail if...
- ...there are any open connections to the database where
the transaction log files are restored to; though querying
tables using the fully qualified name is possible from
another database connection or via a linked server
connection.
I've also had one incident where my log shipping process
failed due to a LSN out of sync issue. This happened when
I ran a BCP IN operation. Other times, both BCP and BULK
INSERT operations ran successfully, funnelling changes to
the secondary server's database as expected.
Thank you for all your responses.
Regards,
- Rob.Your observations are correct. Log shipping will fail if there are =users connected to the database. I am wondering if perhaps someone =changed the dboptions when you had the log shipping fail after a BCP =import.
And no, I have not experienced any other issues. The custom log =shipping approach works very well.
You can find a script that will kill any connections to the specified =database here:
http://sqlguy.home.comcast.net/logship.htm
-- Keith
"Rob" <anonymous@.discussions.microsoft.com> wrote in message =news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
> Hello:
> > I've implemented a stand-by server solution, where the > tran log backup from the primary server gets restored to > the secondary server at every 15-min interval.
> > I understand that there are some limitations with this > approach (could not implement MS SLS as our business unit > could not afford to purchase the Ent. Ed.), and was > wondering if anyone has encountered any other issues or > observations when implementing a similar manual log > shipping process, other than my own observations listed > below:
> > Log Shipping will fail if...
> > - ...there are any open connections to the database where > the transaction log files are restored to; though querying > tables using the fully qualified name is possible from > another database connection or via a linked server > connection.
> > I've also had one incident where my log shipping process > failed due to a LSN out of sync issue. This happened when > I ran a BCP IN operation. Other times, both BCP and BULK > INSERT operations ran successfully, funnelling changes to > the secondary server's database as expected.
> > Thank you for all your responses.
> > Regards,
> > - Rob.|||Rather than doing a KILL command on each SPID, a cleaner
way to do it is to put the database in single user mode
with rollback immediate for the duration of the log
restore and then put it back in multi user mode. This
works very well. Here's an example:
alter database database_name set SINGLE_USER with rollback
immediate
restore log database_name from disk
= 'c:\database_name_log.bak' with standby
= 'c:\standby\database_name.bak'
alter database database_name set MULTI_USER
>--Original Message--
>Your observations are correct. Log shipping will fail if
there are users connected to the database. I am wondering
if perhaps someone changed the dboptions when you had the
log shipping fail after a BCP import.
>And no, I have not experienced any other issues. The
custom log shipping approach works very well.
>You can find a script that will kill any connections to
the specified database here:
>http://sqlguy.home.comcast.net/logship.htm
>--
>Keith
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
>> Hello:
>> I've implemented a stand-by server solution, where the
>> tran log backup from the primary server gets restored
to
>> the secondary server at every 15-min interval.
>> I understand that there are some limitations with this
>> approach (could not implement MS SLS as our business
unit
>> could not afford to purchase the Ent. Ed.), and was
>> wondering if anyone has encountered any other issues or
>> observations when implementing a similar manual log
>> shipping process, other than my own observations listed
>> below:
>> Log Shipping will fail if...
>> - ...there are any open connections to the database
where
>> the transaction log files are restored to; though
querying
>> tables using the fully qualified name is possible from
>> another database connection or via a linked server
>> connection.
>> I've also had one incident where my log shipping
process
>> failed due to a LSN out of sync issue. This happened
when
>> I ran a BCP IN operation. Other times, both BCP and
BULK
>> INSERT operations ran successfully, funnelling changes
to
>> the secondary server's database as expected.
>> Thank you for all your responses.
>> Regards,
>> - Rob.
>.
>|||Agreed. I need to update the web page.
-- Keith
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message =news:13bbf01c3f7d4$804fb2a0$a001280a@.phx.gbl...
> Rather than doing a KILL command on each SPID, a cleaner > way to do it is to put the database in single user mode > with rollback immediate for the duration of the log > restore and then put it back in multi user mode. This > works very well. Here's an example:
> > alter database database_name set SINGLE_USER with rollback > immediate
> > restore log database_name from disk > =3D 'c:\database_name_log.bak' with standby > =3D 'c:\standby\database_name.bak'
> > alter database database_name set MULTI_USER
> > >--Original Message--
> >Your observations are correct. Log shipping will fail if > there are users connected to the database. I am wondering > if perhaps someone changed the dboptions when you had the > log shipping fail after a BCP import. > >
> >And no, I have not experienced any other issues. The > custom log shipping approach works very well.
> >
> >You can find a script that will kill any connections to > the specified database here:
> >http://sqlguy.home.comcast.net/logship.htm
> >
> >-- > >Keith
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in > message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
> >> Hello:
> >> > >> I've implemented a stand-by server solution, where the > >> tran log backup from the primary server gets restored > to > >> the secondary server at every 15-min interval.
> >> > >> I understand that there are some limitations with this > >> approach (could not implement MS SLS as our business > unit > >> could not afford to purchase the Ent. Ed.), and was > >> wondering if anyone has encountered any other issues or > >> observations when implementing a similar manual log > >> shipping process, other than my own observations listed > >> below:
> >> > >> Log Shipping will fail if...
> >> > >> - ...there are any open connections to the database > where > >> the transaction log files are restored to; though > querying > >> tables using the fully qualified name is possible from > >> another database connection or via a linked server > >> connection.
> >> > >> I've also had one incident where my log shipping > process > >> failed due to a LSN out of sync issue. This happened > when > >> I ran a BCP IN operation. Other times, both BCP and > BULK > >> INSERT operations ran successfully, funnelling changes > to > >> the secondary server's database as expected.
> >> > >> Thank you for all your responses.
> >> > >> Regards,
> >> > >> - Rob.
> >.
> >|||But even in single user mode, there could be multiple
connections to the database, which can cause manual log
shipping failures. In this case, I find killing all user
connections more effective, to ensure no connections
exists prior to restoring either the full backup and/or
the tran log.
Thanks.
>--Original Message--
>Rather than doing a KILL command on each SPID, a cleaner
>way to do it is to put the database in single user mode
>with rollback immediate for the duration of the log
>restore and then put it back in multi user mode. This
>works very well. Here's an example:
>alter database database_name set SINGLE_USER with
rollback
>immediate
>restore log database_name from disk
>= 'c:\database_name_log.bak' with standby
>= 'c:\standby\database_name.bak'
>alter database database_name set MULTI_USER
>>--Original Message--
>>Your observations are correct. Log shipping will fail
if
>there are users connected to the database. I am
wondering
>if perhaps someone changed the dboptions when you had the
>log shipping fail after a BCP import.
>>And no, I have not experienced any other issues. The
>custom log shipping approach works very well.
>>You can find a script that will kill any connections to
>the specified database here:
>>http://sqlguy.home.comcast.net/logship.htm
>>--
>>Keith
>>
>>"Rob" <anonymous@.discussions.microsoft.com> wrote in
>message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
>> Hello:
>> I've implemented a stand-by server solution, where the
>> tran log backup from the primary server gets restored
>to
>> the secondary server at every 15-min interval.
>> I understand that there are some limitations with this
>> approach (could not implement MS SLS as our business
>unit
>> could not afford to purchase the Ent. Ed.), and was
>> wondering if anyone has encountered any other issues
or
>> observations when implementing a similar manual log
>> shipping process, other than my own observations
listed
>> below:
>> Log Shipping will fail if...
>> - ...there are any open connections to the database
>where
>> the transaction log files are restored to; though
>querying
>> tables using the fully qualified name is possible from
>> another database connection or via a linked server
>> connection.
>> I've also had one incident where my log shipping
>process
>> failed due to a LSN out of sync issue. This happened
>when
>> I ran a BCP IN operation. Other times, both BCP and
>BULK
>> INSERT operations ran successfully, funnelling changes
>to
>> the secondary server's database as expected.
>> Thank you for all your responses.
>> Regards,
>> - Rob.
>>.
>.
>|||Putting it in 'single user mode with rollback immediate'
will disconnect any currnet connections to the db and then
put it in single user mode for the process to restore the
log. Since it's in single user mode, only the process
that is restoring the log can connect. Once the restore
is done, just put it back into multi user mode.
>--Original Message--
>But even in single user mode, there could be multiple
>connections to the database, which can cause manual log
>shipping failures. In this case, I find killing all user
>connections more effective, to ensure no connections
>exists prior to restoring either the full backup and/or
>the tran log.
>Thanks.
>>--Original Message--
>>Rather than doing a KILL command on each SPID, a cleaner
>>way to do it is to put the database in single user mode
>>with rollback immediate for the duration of the log
>>restore and then put it back in multi user mode. This
>>works very well. Here's an example:
>>alter database database_name set SINGLE_USER with
>rollback
>>immediate
>>restore log database_name from disk
>>= 'c:\database_name_log.bak' with standby
>>= 'c:\standby\database_name.bak'
>>alter database database_name set MULTI_USER
>>--Original Message--
>>Your observations are correct. Log shipping will fail
>if
>>there are users connected to the database. I am
>wondering
>>if perhaps someone changed the dboptions when you had
the
>>log shipping fail after a BCP import.
>>And no, I have not experienced any other issues. The
>>custom log shipping approach works very well.
>>You can find a script that will kill any connections to
>>the specified database here:
>>http://sqlguy.home.comcast.net/logship.htm
>>--
>>Keith
>>
>>"Rob" <anonymous@.discussions.microsoft.com> wrote in
>>message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
>> Hello:
>> I've implemented a stand-by server solution, where
the
>> tran log backup from the primary server gets restored
>>to
>> the secondary server at every 15-min interval.
>> I understand that there are some limitations with
this
>> approach (could not implement MS SLS as our business
>>unit
>> could not afford to purchase the Ent. Ed.), and was
>> wondering if anyone has encountered any other issues
>or
>> observations when implementing a similar manual log
>> shipping process, other than my own observations
>listed
>> below:
>> Log Shipping will fail if...
>> - ...there are any open connections to the database
>>where
>> the transaction log files are restored to; though
>>querying
>> tables using the fully qualified name is possible
from
>> another database connection or via a linked server
>> connection.
>> I've also had one incident where my log shipping
>>process
>> failed due to a LSN out of sync issue. This happened
>>when
>> I ran a BCP IN operation. Other times, both BCP and
>>BULK
>> INSERT operations ran successfully, funnelling
changes
>>to
>> the secondary server's database as expected.
>> Thank you for all your responses.
>> Regards,
>> - Rob.
>>.
>>.
>.
>