Hi,
I am implementing manual log shipping between a production server and a
standby server.
I have done the follwing:
1. On prod server created 2 devices for db and log.
2. created 2 jobs which will backup db/log,move to network share and restore
to standby server using something like this
BACKUP LOG newnorthwind TO backup_log WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'
3. my problem is that when scheduling the log every 15 mins the log will
still have the same file name and even i tried having datetime stamp but the
backup time may differ from one log to other and the restore has to have some
kind of dynamic settings that will find the last log backup time.
Can anyone suggest as how i am do that and i dont want to erase the log file.
Mnay thanks
Anup
Keep a table that keeps track of which log files you've already "Shipped"
Greg Jackson
PDX, Oregon
|||But How do i get that info when I am restoring the logs to the standby table
"pdxJaxon" wrote:
> Keep a table that keeps track of which log files you've already "Shipped"
>
> Greg Jackson
> PDX, Oregon
>
>
|||you name the t-logs with a datetime in the name
DBNAME_TLOG_200506080800.trn
have a process that copies all the trn logs over to the target server
on the target server read al files in the directory and put them in a temp
table sorted by name
this table is (LogsToApply)
walk the records one at a time and apply them in order IF THEY are not
already listed in the LogsApplied Table.
once you've applied a log, add it's name to the LogsApplied table.
that's it.
I could probably dig the script up for you, but it's been over 2 years since
I did this.
It works great.
GAJ
Showing posts with label shipping. Show all posts
Showing posts with label shipping. Show all posts
Monday, March 19, 2012
Manual Log Shipping Restore Log Problems
Hi,
I am implementing manual log shipping between a production server and a
standby server.
I have done the follwing:
1. On prod server created 2 devices for db and log.
2. created 2 jobs which will backup db/log,move to network share and restore
to standby server using something like this
BACKUP LOG newnorthwind TO backup_log WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'
3. my problem is that when scheduling the log every 15 mins the log will
still have the same file name and even i tried having datetime stamp but the
backup time may differ from one log to other and the restore has to have som
e
kind of dynamic settings that will find the last log backup time.
Can anyone suggest as how i am do that and i dont want to erase the log file
.
Mnay thanks
AnupKeep a table that keeps track of which log files you've already "Shipped"
Greg Jackson
PDX, Oregon|||But How do i get that info when I am restoring the logs to the standby table
"pdxJaxon" wrote:
> Keep a table that keeps track of which log files you've already "Shipped"
>
> Greg Jackson
> PDX, Oregon
>
>|||you name the t-logs with a datetime in the name
DBNAME_TLOG_200506080800.trn
have a process that copies all the trn logs over to the target server
on the target server read al files in the directory and put them in a temp
table sorted by name
this table is (LogsToApply)
walk the records one at a time and apply them in order IF THEY are not
already listed in the LogsApplied Table.
once you've applied a log, add it's name to the LogsApplied table.
that's it.
I could probably dig the script up for you, but it's been over 2 years since
I did this.
It works great.
GAJ
I am implementing manual log shipping between a production server and a
standby server.
I have done the follwing:
1. On prod server created 2 devices for db and log.
2. created 2 jobs which will backup db/log,move to network share and restore
to standby server using something like this
BACKUP LOG newnorthwind TO backup_log WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'
3. my problem is that when scheduling the log every 15 mins the log will
still have the same file name and even i tried having datetime stamp but the
backup time may differ from one log to other and the restore has to have som
e
kind of dynamic settings that will find the last log backup time.
Can anyone suggest as how i am do that and i dont want to erase the log file
.
Mnay thanks
AnupKeep a table that keeps track of which log files you've already "Shipped"
Greg Jackson
PDX, Oregon|||But How do i get that info when I am restoring the logs to the standby table
"pdxJaxon" wrote:
> Keep a table that keeps track of which log files you've already "Shipped"
>
> Greg Jackson
> PDX, Oregon
>
>|||you name the t-logs with a datetime in the name
DBNAME_TLOG_200506080800.trn
have a process that copies all the trn logs over to the target server
on the target server read al files in the directory and put them in a temp
table sorted by name
this table is (LogsToApply)
walk the records one at a time and apply them in order IF THEY are not
already listed in the LogsApplied Table.
once you've applied a log, add it's name to the LogsApplied table.
that's it.
I could probably dig the script up for you, but it's been over 2 years since
I did this.
It works great.
GAJ
Manual Log Shipping Restore Log Problems
Hi,
I am implementing manual log shipping between a production server and a
standby server.
I have done the follwing:
1. On prod server created 2 devices for db and log.
2. created 2 jobs which will backup db/log,move to network share and restore
to standby server using something like this
BACKUP LOG newnorthwind TO backup_log WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'
3. my problem is that when scheduling the log every 15 mins the log will
still have the same file name and even i tried having datetime stamp but the
backup time may differ from one log to other and the restore has to have some
kind of dynamic settings that will find the last log backup time.
Can anyone suggest as how i am do that and i dont want to erase the log file.
Mnay thanks
AnupKeep a table that keeps track of which log files you've already "Shipped"
Greg Jackson
PDX, Oregon|||But How do i get that info when I am restoring the logs to the standby table
"pdxJaxon" wrote:
> Keep a table that keeps track of which log files you've already "Shipped"
>
> Greg Jackson
> PDX, Oregon
>
>|||you name the t-logs with a datetime in the name
DBNAME_TLOG_200506080800.trn
have a process that copies all the trn logs over to the target server
on the target server read al files in the directory and put them in a temp
table sorted by name
this table is (LogsToApply)
walk the records one at a time and apply them in order IF THEY are not
already listed in the LogsApplied Table.
once you've applied a log, add it's name to the LogsApplied table.
that's it.
I could probably dig the script up for you, but it's been over 2 years since
I did this.
It works great.
GAJ
I am implementing manual log shipping between a production server and a
standby server.
I have done the follwing:
1. On prod server created 2 devices for db and log.
2. created 2 jobs which will backup db/log,move to network share and restore
to standby server using something like this
BACKUP LOG newnorthwind TO backup_log WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'
3. my problem is that when scheduling the log every 15 mins the log will
still have the same file name and even i tried having datetime stamp but the
backup time may differ from one log to other and the restore has to have some
kind of dynamic settings that will find the last log backup time.
Can anyone suggest as how i am do that and i dont want to erase the log file.
Mnay thanks
AnupKeep a table that keeps track of which log files you've already "Shipped"
Greg Jackson
PDX, Oregon|||But How do i get that info when I am restoring the logs to the standby table
"pdxJaxon" wrote:
> Keep a table that keeps track of which log files you've already "Shipped"
>
> Greg Jackson
> PDX, Oregon
>
>|||you name the t-logs with a datetime in the name
DBNAME_TLOG_200506080800.trn
have a process that copies all the trn logs over to the target server
on the target server read al files in the directory and put them in a temp
table sorted by name
this table is (LogsToApply)
walk the records one at a time and apply them in order IF THEY are not
already listed in the LogsApplied Table.
once you've applied a log, add it's name to the LogsApplied table.
that's it.
I could probably dig the script up for you, but it's been over 2 years since
I did this.
It works great.
GAJ
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.
>>.
>>.
>.
>
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.
>>.
>>.
>.
>
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. =20
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
--=20
Keith
"Rob" <anonymous@.discussions.microsoft.com> wrote in message =
news:1332101c3f7bf$64a958e0$a301280a@.phx
.gbl...
> Hello:
>=20
> I've implemented a stand-by server solution, where the=20
> tran log backup from the primary server gets restored to=20
> the secondary server at every 15-min interval.
>=20
> I understand that there are some limitations with this=20
> approach (could not implement MS SLS as our business unit=20
> could not afford to purchase the Ent. Ed.), and was=20
> wondering if anyone has encountered any other issues or=20
> observations when implementing a similar manual log=20
> shipping process, other than my own observations listed=20
> below:
>=20
> Log Shipping will fail if...
>=20
> - ...there are any open connections to the database where=20
> the transaction log files are restored to; though querying=20
> tables using the fully qualified name is possible from=20
> another database connection or via a linked server=20
> connection.
>=20
> I've also had one incident where my log shipping process=20
> failed due to a LSN out of sync issue. This happened when=20
> I ran a BCP IN operation. Other times, both BCP and BULK=20
> INSERT operations ran successfully, funnelling changes to=20
> the secondary server's database as expected.
>=20
> Thank you for all your responses.
>=20
> Regards,
>=20
> - 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...
to
unit
where
querying
process
when
BULK
to
>.
>|||Agreed. I need to update the web page.
--=20
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=20
> way to do it is to put the database in single user mode=20
> with rollback immediate for the duration of the log=20
> restore and then put it back in multi user mode. This=20
> works very well. Here's an example:
>=20
> alter database database_name set SINGLE_USER with rollback=20
> immediate
>=20
> restore log database_name from disk=20
> =3D 'c:\database_name_log.bak' with standby=20
> =3D 'c:\standby\database_name.bak'
>=20
> alter database database_name set MULTI_USER
>=20
> there are users connected to the database. I am wondering=20
> if perhaps someone changed the dboptions when you had the=20
> log shipping fail after a BCP import. =20
> custom log shipping approach works very well.
> the specified database here:
> message news:1332101c3f7bf$64a958e0$a301280a@.phx
.gbl...
> to=20
> unit=20
> where=20
> querying=20
> process=20
> when=20
> BULK=20
> to=20|||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
>
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.
>custom log shipping approach works very well.
>the specified database here:
>message news:1332101c3f7bf$64a958e0$a301280a@.phx
.gbl...
>to
>unit
or
listed
>where
>querying
>process
>when
>BULK
>to
>.
>|||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.
>
>rollback
>if
>wondering
the
the
this
>or
>listed
from
changes
>.
>
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. =20
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
--=20
Keith
"Rob" <anonymous@.discussions.microsoft.com> wrote in message =
news:1332101c3f7bf$64a958e0$a301280a@.phx
.gbl...
> Hello:
>=20
> I've implemented a stand-by server solution, where the=20
> tran log backup from the primary server gets restored to=20
> the secondary server at every 15-min interval.
>=20
> I understand that there are some limitations with this=20
> approach (could not implement MS SLS as our business unit=20
> could not afford to purchase the Ent. Ed.), and was=20
> wondering if anyone has encountered any other issues or=20
> observations when implementing a similar manual log=20
> shipping process, other than my own observations listed=20
> below:
>=20
> Log Shipping will fail if...
>=20
> - ...there are any open connections to the database where=20
> the transaction log files are restored to; though querying=20
> tables using the fully qualified name is possible from=20
> another database connection or via a linked server=20
> connection.
>=20
> I've also had one incident where my log shipping process=20
> failed due to a LSN out of sync issue. This happened when=20
> I ran a BCP IN operation. Other times, both BCP and BULK=20
> INSERT operations ran successfully, funnelling changes to=20
> the secondary server's database as expected.
>=20
> Thank you for all your responses.
>=20
> Regards,
>=20
> - 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...
to
unit
where
querying
process
when
BULK
to
>.
>|||Agreed. I need to update the web page.
--=20
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=20
> way to do it is to put the database in single user mode=20
> with rollback immediate for the duration of the log=20
> restore and then put it back in multi user mode. This=20
> works very well. Here's an example:
>=20
> alter database database_name set SINGLE_USER with rollback=20
> immediate
>=20
> restore log database_name from disk=20
> =3D 'c:\database_name_log.bak' with standby=20
> =3D 'c:\standby\database_name.bak'
>=20
> alter database database_name set MULTI_USER
>=20
> there are users connected to the database. I am wondering=20
> if perhaps someone changed the dboptions when you had the=20
> log shipping fail after a BCP import. =20
> custom log shipping approach works very well.
> the specified database here:
> message news:1332101c3f7bf$64a958e0$a301280a@.phx
.gbl...
> to=20
> unit=20
> where=20
> querying=20
> process=20
> when=20
> BULK=20
> to=20|||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
>
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.
>custom log shipping approach works very well.
>the specified database here:
>message news:1332101c3f7bf$64a958e0$a301280a@.phx
.gbl...
>to
>unit
or
listed
>where
>querying
>process
>when
>BULK
>to
>.
>|||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.
>
>rollback
>if
>wondering
the
the
this
>or
>listed
from
changes
>.
>
Manual Log shipping
Interested in creating a manual standby database.Will like to
implement this with Sql Server standard Edition available
Any ideas or recommendations
implement this with Sql Server standard Edition available
Any ideas or recommendations
Thanks
TYHi
There was a couple of articles on this in SQL Server Professional Magazine
http://tinyurl.com/4ez3l
http://tinyurl.com/6rdth
You may also want to check out the simple log shipping solution in the
resource kit.
John
"Tyler Smith Watu" <watuni2000@.yahoo.co.nz> wrote in message
news:2f3438a4.0410010907.249cc962@.posting.google.c om...
> Interested in creating a manual standby database.Will like to
> implement this with Sql Server standard Edition available
> Any ideas or recommendations
>
> Thanks
> TY
Friday, March 9, 2012
Manifold now shipping GIS product for SQL Server 2008 Spatial (Katmai)
I thought readers of this forum would be interested to know commercial products are now supporting Katmai.
Manifold Release 8 started shipping last Tuesday, 28 August 2007, to the general public with direct, native support for SQL Server 2008 (Katmai) Spatial built in.
Links...
Home page (SQL Server 2008 featured prominently):
http://www.manifold.net/
News page overview of Release 8:
http://www.manifold.net/info/news.shtml
Spatial DBMS user manual topic:
http://demo.manifold.net/doc/spatial_dbms.htm
The spatial DBMS user manual topic is long because it introduces the general ideas behind spatial DBMS for users new to this part of GIS. SQL Server 2008 users will probably want to skip right down to the "Supported Spatial DBMS" section of that topic, as well as the "Projections and Spatial DBMS" section, which discusses use of EPSG codes within Katmai data stores.
From the user manual page:
"Manifold supports SQL Server 2008 spatial today for those Microsoft employees, partners, beta testers and independent developers who have been given access to SQL Server 2008 spatial, and will continue to support SQL Server 2008 spatial when it is released to the public."
Note that Manifold Enterprise Edition ($395) is required to work with native Katmai connections and native Katmai geometry types. This is true of SQL Server 2008 as it is of the other spatial DBMS's supported by Manifold (Oracle, IBM DB2 with Spatial Extender, PostgreSQL/PostGIS). If you have the Katmai spatial CTP you don't need to get anything than the standard Release 8 production edition of Manifold. It will automatically recognize SQL Server 2008 spatial data stores upon connection using the new, dedicated SQL Server data source. For example, once you connect (you can connect to many different types of spatial DBMS at once) you can copy data from Oracle Spatial or legacy storage like ArcSDE and paste it into Katmai. The Database Console in Manifold also works with Katmai, which provides a useful way of crafting and executing queries.
By the way, we'd like to compliment the Katmai team on a job well done. We've had a lot of experience with spatial DBMS and GIS and this is clearly a world-class entry from Microsoft.
Ah, almost forgot: if you work for Microsoft, Manifold is free for you. Drop me an email and I'll arrange a free license for you.
Regards to all,
Clearing open "question".
Manifold now shipping GIS product for SQL Server 2008 Spatial (Katmai)
I thought readers of this forum would be interested to know commercial products are now supporting Katmai.
Manifold Release 8 started shipping last Tuesday, 28 August 2007, to the general public with direct, native support for SQL Server 2008 (Katmai) Spatial built in.
Links...
Home page (SQL Server 2008 featured prominently):
http://www.manifold.net/
News page overview of Release 8:
http://www.manifold.net/info/news.shtml
Spatial DBMS user manual topic:
http://demo.manifold.net/doc/spatial_dbms.htm
The spatial DBMS user manual topic is long because it introduces the general ideas behind spatial DBMS for users new to this part of GIS. SQL Server 2008 users will probably want to skip right down to the "Supported Spatial DBMS" section of that topic, as well as the "Projections and Spatial DBMS" section, which discusses use of EPSG codes within Katmai data stores.
From the user manual page:
"Manifold supports SQL Server 2008 spatial today for those Microsoft employees, partners, beta testers and independent developers who have been given access to SQL Server 2008 spatial, and will continue to support SQL Server 2008 spatial when it is released to the public."
Note that Manifold Enterprise Edition ($395) is required to work with native Katmai connections and native Katmai geometry types. This is true of SQL Server 2008 as it is of the other spatial DBMS's supported by Manifold (Oracle, IBM DB2 with Spatial Extender, PostgreSQL/PostGIS). If you have the Katmai spatial CTP you don't need to get anything than the standard Release 8 production edition of Manifold. It will automatically recognize SQL Server 2008 spatial data stores upon connection using the new, dedicated SQL Server data source. For example, once you connect (you can connect to many different types of spatial DBMS at once) you can copy data from Oracle Spatial or legacy storage like ArcSDE and paste it into Katmai. The Database Console in Manifold also works with Katmai, which provides a useful way of crafting and executing queries.
By the way, we'd like to compliment the Katmai team on a job well done. We've had a lot of experience with spatial DBMS and GIS and this is clearly a world-class entry from Microsoft.
Ah, almost forgot: if you work for Microsoft, Manifold is free for you. Drop me an email and I'll arrange a free license for you.
Regards to all,
Clearing open "question".
Subscribe to:
Posts (Atom)