We have a Production Server A thats log shipped to Standby Server B. We want
to simulate some disaster recovery runs .
What are some things to consider. It seems obtainable from A to B.. But how
do I revert back to A again.. Do I have to redo log shipping and this time
from B to A which means I need to restore all dbs/logs in norecovery state
onto A from B using the wizard
Are there better ways to simulate this ? I dont wish for it to be a
nightmare especially since if we are going to test this and we would want to
revert back to A as soon as possible during this run...You don't have to set up reverse log-shipping. You simply backup B & restore
it on A & then re-initialise the log shipping process to continue testing..
Regards,
Greg Linwood
SQL Server MVP
"sql" <sql@.hotmail.com> wrote in message
news:uNMLuPA%23DHA.888@.tk2msftngp13.phx.gbl...
> We have a Production Server A thats log shipped to Standby Server B. We
want
> to simulate some disaster recovery runs .
> What are some things to consider. It seems obtainable from A to B.. But
how
> do I revert back to A again.. Do I have to redo log shipping and this time
> from B to A which means I need to restore all dbs/logs in norecovery state
> onto A from B using the wizard
> Are there better ways to simulate this ? I dont wish for it to be a
> nightmare especially since if we are going to test this and we would want
to
> revert back to A as soon as possible during this run...
>
>|||When we fail to Standby Server B initially and simulate to run for 1 or 2
days before we recover Primary Server A, does that mean my downtime to
revert back to server A would be taking server B offline... doing a total
restore of all dbs to server A and then proceed. So my downtime is equal to
the amount of time it takes to restore the dbs as opposed to the initial
failover from A to B as the only process involved there is recovering the
dbs.. and other minor stuff such as logins,msdb related jobs and DTS
packages,sysmessages..assuming we are good to go here
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:O%23wVThA%23DHA.632@.TK2MSFTNGP12.phx.gbl...
> You don't have to set up reverse log-shipping. You simply backup B &
restore
> it on A & then re-initialise the log shipping process to continue
testing..
> Regards,
> Greg Linwood
> SQL Server MVP
> "sql" <sql@.hotmail.com> wrote in message
> news:uNMLuPA%23DHA.888@.tk2msftngp13.phx.gbl...
> want
> how
time
state
want
> to
>|||Hi Hassan.
That's one way of doing it. Another is to do it in multiple steps - eg leave
B online whilst you perform your full backup restore to A (DTS packages &
whatever else), then restore the remaining log tails to A & your window of
downtime is fairly small. If you want to keep that downtime window even
smaller you could iterate the log tail restores.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23YHO71D%23DHA.132@.TK2MSFTNGP09.phx.gbl...
> When we fail to Standby Server B initially and simulate to run for 1 or 2
> days before we recover Primary Server A, does that mean my downtime to
> revert back to server A would be taking server B offline... doing a total
> restore of all dbs to server A and then proceed. So my downtime is equal
to
> the amount of time it takes to restore the dbs as opposed to the initial
> failover from A to B as the only process involved there is recovering the
> dbs.. and other minor stuff such as logins,msdb related jobs and DTS
> packages,sysmessages..assuming we are good to go here
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:O%23wVThA%23DHA.632@.TK2MSFTNGP12.phx.gbl...
> restore
> testing..
We
But
> time
> state
> want
>
Showing posts with label runs. Show all posts
Showing posts with label runs. Show all posts
Monday, March 26, 2012
Log shipping recovery trials
We have a Production Server A thats log shipped to Standby Server B. We want
to simulate some disaster recovery runs .
What are some things to consider. It seems obtainable from A to B.. But how
do I revert back to A again.. Do I have to redo log shipping and this time
from B to A which means I need to restore all dbs/logs in norecovery state
onto A from B using the wizard
Are there better ways to simulate this ? I dont wish for it to be a
nightmare especially since if we are going to test this and we would want to
revert back to A as soon as possible during this run...You don't have to set up reverse log-shipping. You simply backup B & restore
it on A & then re-initialise the log shipping process to continue testing..
Regards,
Greg Linwood
SQL Server MVP
"sql" <sql@.hotmail.com> wrote in message
news:uNMLuPA%23DHA.888@.tk2msftngp13.phx.gbl...
> We have a Production Server A thats log shipped to Standby Server B. We
want
> to simulate some disaster recovery runs .
> What are some things to consider. It seems obtainable from A to B.. But
how
> do I revert back to A again.. Do I have to redo log shipping and this time
> from B to A which means I need to restore all dbs/logs in norecovery state
> onto A from B using the wizard
> Are there better ways to simulate this ? I dont wish for it to be a
> nightmare especially since if we are going to test this and we would want
to
> revert back to A as soon as possible during this run...
>
>|||When we fail to Standby Server B initially and simulate to run for 1 or 2
days before we recover Primary Server A, does that mean my downtime to
revert back to server A would be taking server B offline... doing a total
restore of all dbs to server A and then proceed. So my downtime is equal to
the amount of time it takes to restore the dbs as opposed to the initial
failover from A to B as the only process involved there is recovering the
dbs.. and other minor stuff such as logins,msdb related jobs and DTS
packages,sysmessages..assuming we are good to go here
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:O%23wVThA%23DHA.632@.TK2MSFTNGP12.phx.gbl...
> You don't have to set up reverse log-shipping. You simply backup B &
restore
> it on A & then re-initialise the log shipping process to continue
testing..
> Regards,
> Greg Linwood
> SQL Server MVP
> "sql" <sql@.hotmail.com> wrote in message
> news:uNMLuPA%23DHA.888@.tk2msftngp13.phx.gbl...
> > We have a Production Server A thats log shipped to Standby Server B. We
> want
> > to simulate some disaster recovery runs .
> >
> > What are some things to consider. It seems obtainable from A to B.. But
> how
> > do I revert back to A again.. Do I have to redo log shipping and this
time
> > from B to A which means I need to restore all dbs/logs in norecovery
state
> > onto A from B using the wizard
> >
> > Are there better ways to simulate this ? I dont wish for it to be a
> > nightmare especially since if we are going to test this and we would
want
> to
> > revert back to A as soon as possible during this run...
> >
> >
> >
>|||Hi Hassan.
That's one way of doing it. Another is to do it in multiple steps - eg leave
B online whilst you perform your full backup restore to A (DTS packages &
whatever else), then restore the remaining log tails to A & your window of
downtime is fairly small. If you want to keep that downtime window even
smaller you could iterate the log tail restores.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23YHO71D%23DHA.132@.TK2MSFTNGP09.phx.gbl...
> When we fail to Standby Server B initially and simulate to run for 1 or 2
> days before we recover Primary Server A, does that mean my downtime to
> revert back to server A would be taking server B offline... doing a total
> restore of all dbs to server A and then proceed. So my downtime is equal
to
> the amount of time it takes to restore the dbs as opposed to the initial
> failover from A to B as the only process involved there is recovering the
> dbs.. and other minor stuff such as logins,msdb related jobs and DTS
> packages,sysmessages..assuming we are good to go here
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:O%23wVThA%23DHA.632@.TK2MSFTNGP12.phx.gbl...
> > You don't have to set up reverse log-shipping. You simply backup B &
> restore
> > it on A & then re-initialise the log shipping process to continue
> testing..
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "sql" <sql@.hotmail.com> wrote in message
> > news:uNMLuPA%23DHA.888@.tk2msftngp13.phx.gbl...
> > > We have a Production Server A thats log shipped to Standby Server B.
We
> > want
> > > to simulate some disaster recovery runs .
> > >
> > > What are some things to consider. It seems obtainable from A to B..
But
> > how
> > > do I revert back to A again.. Do I have to redo log shipping and this
> time
> > > from B to A which means I need to restore all dbs/logs in norecovery
> state
> > > onto A from B using the wizard
> > >
> > > Are there better ways to simulate this ? I dont wish for it to be a
> > > nightmare especially since if we are going to test this and we would
> want
> > to
> > > revert back to A as soon as possible during this run...
> > >
> > >
> > >
> >
> >
>
to simulate some disaster recovery runs .
What are some things to consider. It seems obtainable from A to B.. But how
do I revert back to A again.. Do I have to redo log shipping and this time
from B to A which means I need to restore all dbs/logs in norecovery state
onto A from B using the wizard
Are there better ways to simulate this ? I dont wish for it to be a
nightmare especially since if we are going to test this and we would want to
revert back to A as soon as possible during this run...You don't have to set up reverse log-shipping. You simply backup B & restore
it on A & then re-initialise the log shipping process to continue testing..
Regards,
Greg Linwood
SQL Server MVP
"sql" <sql@.hotmail.com> wrote in message
news:uNMLuPA%23DHA.888@.tk2msftngp13.phx.gbl...
> We have a Production Server A thats log shipped to Standby Server B. We
want
> to simulate some disaster recovery runs .
> What are some things to consider. It seems obtainable from A to B.. But
how
> do I revert back to A again.. Do I have to redo log shipping and this time
> from B to A which means I need to restore all dbs/logs in norecovery state
> onto A from B using the wizard
> Are there better ways to simulate this ? I dont wish for it to be a
> nightmare especially since if we are going to test this and we would want
to
> revert back to A as soon as possible during this run...
>
>|||When we fail to Standby Server B initially and simulate to run for 1 or 2
days before we recover Primary Server A, does that mean my downtime to
revert back to server A would be taking server B offline... doing a total
restore of all dbs to server A and then proceed. So my downtime is equal to
the amount of time it takes to restore the dbs as opposed to the initial
failover from A to B as the only process involved there is recovering the
dbs.. and other minor stuff such as logins,msdb related jobs and DTS
packages,sysmessages..assuming we are good to go here
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:O%23wVThA%23DHA.632@.TK2MSFTNGP12.phx.gbl...
> You don't have to set up reverse log-shipping. You simply backup B &
restore
> it on A & then re-initialise the log shipping process to continue
testing..
> Regards,
> Greg Linwood
> SQL Server MVP
> "sql" <sql@.hotmail.com> wrote in message
> news:uNMLuPA%23DHA.888@.tk2msftngp13.phx.gbl...
> > We have a Production Server A thats log shipped to Standby Server B. We
> want
> > to simulate some disaster recovery runs .
> >
> > What are some things to consider. It seems obtainable from A to B.. But
> how
> > do I revert back to A again.. Do I have to redo log shipping and this
time
> > from B to A which means I need to restore all dbs/logs in norecovery
state
> > onto A from B using the wizard
> >
> > Are there better ways to simulate this ? I dont wish for it to be a
> > nightmare especially since if we are going to test this and we would
want
> to
> > revert back to A as soon as possible during this run...
> >
> >
> >
>|||Hi Hassan.
That's one way of doing it. Another is to do it in multiple steps - eg leave
B online whilst you perform your full backup restore to A (DTS packages &
whatever else), then restore the remaining log tails to A & your window of
downtime is fairly small. If you want to keep that downtime window even
smaller you could iterate the log tail restores.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23YHO71D%23DHA.132@.TK2MSFTNGP09.phx.gbl...
> When we fail to Standby Server B initially and simulate to run for 1 or 2
> days before we recover Primary Server A, does that mean my downtime to
> revert back to server A would be taking server B offline... doing a total
> restore of all dbs to server A and then proceed. So my downtime is equal
to
> the amount of time it takes to restore the dbs as opposed to the initial
> failover from A to B as the only process involved there is recovering the
> dbs.. and other minor stuff such as logins,msdb related jobs and DTS
> packages,sysmessages..assuming we are good to go here
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:O%23wVThA%23DHA.632@.TK2MSFTNGP12.phx.gbl...
> > You don't have to set up reverse log-shipping. You simply backup B &
> restore
> > it on A & then re-initialise the log shipping process to continue
> testing..
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "sql" <sql@.hotmail.com> wrote in message
> > news:uNMLuPA%23DHA.888@.tk2msftngp13.phx.gbl...
> > > We have a Production Server A thats log shipped to Standby Server B.
We
> > want
> > > to simulate some disaster recovery runs .
> > >
> > > What are some things to consider. It seems obtainable from A to B..
But
> > how
> > > do I revert back to A again.. Do I have to redo log shipping and this
> time
> > > from B to A which means I need to restore all dbs/logs in norecovery
> state
> > > onto A from B using the wizard
> > >
> > > Are there better ways to simulate this ? I dont wish for it to be a
> > > nightmare especially since if we are going to test this and we would
> want
> > to
> > > revert back to A as soon as possible during this run...
> > >
> > >
> > >
> >
> >
>
Log shipping reconciliation
I have a database reconciliation script that runs between two databases and
reconciles object counts and table counts etc between the two.
Is there a way to run such a script across a primary and secondary in log
shipping configuration. As the secondary is in RESTORING state it's not
accessible. Therefore is it possible to do something like bring it briefly up
to standby state, run the reconciliation and then take it back to restoring
again? Would this work and are there any gotchas?
Cheers.
Here is an example of how to do it:
there are two databases logshippingtestbak and logshippingtest.
I backup loshippingtest and restore this backup into the database
logshippingtestback with no recovery
backup log logshippingtest to disk='c:\logshippingtestlog.bak'
GO
--restoring my first log
restore log logshippingtestbak from disk='c:\logshippingtestlog.bak' with
norecovery
GO
select * from logshippingtestbak.dbo.sysobjects
Server: Msg 927, Level 14, State 2, Line 1
Database 'logshippingtestbak' cannot be opened. It is in the middle of a
restore.
--i backup another log
backup log logshippingtest to disk='c:\logshippingtestlog.bak'
--i restore it with the standby option
restore log logshippingtestbak from disk='c:\logshippingtestlog.bak' with
standby ='c:\standby.bak'
--i read this database
select * from logshippingtestbak.dbo.sysobjects
--I restore the same log this time with norecovery
restore log logshippingtestbak from disk='c:\logshippingtestlog.bak' with
norecovery
--I verify this database is inaccessible again.
select * from logshippingtestbak.dbo.sysobjects
Server: Msg 927, Level 14, State 2, Line 1
Database 'logshippingtestbak' cannot be opened. It is in the middle of a
restore.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kev" <Kev@.discussions.microsoft.com> wrote in message
news:5722A586-57E3-41AA-B6DF-021F50F7F2CB@.microsoft.com...
>I have a database reconciliation script that runs between two databases and
> reconciles object counts and table counts etc between the two.
> Is there a way to run such a script across a primary and secondary in log
> shipping configuration. As the secondary is in RESTORING state it's not
> accessible. Therefore is it possible to do something like bring it briefly
> up
> to standby state, run the reconciliation and then take it back to
> restoring
> again? Would this work and are there any gotchas?
> Cheers.
|||Is it possible to do something like:
restore database [logshippingtestbak] with
standby=''c:\logshippingtestlog.bak'
<do the reconciliation>
restore database [logshippingtestbak] with norecovery
or do you always need to restore the last transaction log to switch modes.
Cheers
Kev.
"Hilary Cotter" wrote:
> Here is an example of how to do it:
> there are two databases logshippingtestbak and logshippingtest.
> I backup loshippingtest and restore this backup into the database
> logshippingtestback with no recovery
> backup log logshippingtest to disk='c:\logshippingtestlog.bak'
> GO
> --restoring my first log
> restore log logshippingtestbak from disk='c:\logshippingtestlog.bak' with
> norecovery
> GO
> select * from logshippingtestbak.dbo.sysobjects
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'logshippingtestbak' cannot be opened. It is in the middle of a
> restore.
> --i backup another log
> backup log logshippingtest to disk='c:\logshippingtestlog.bak'
> --i restore it with the standby option
> restore log logshippingtestbak from disk='c:\logshippingtestlog.bak' with
> standby ='c:\standby.bak'
> --i read this database
> select * from logshippingtestbak.dbo.sysobjects
> --I restore the same log this time with norecovery
> restore log logshippingtestbak from disk='c:\logshippingtestlog.bak' with
> norecovery
> --I verify this database is inaccessible again.
> select * from logshippingtestbak.dbo.sysobjects
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'logshippingtestbak' cannot be opened. It is in the middle of a
> restore.
>
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kev" <Kev@.discussions.microsoft.com> wrote in message
> news:5722A586-57E3-41AA-B6DF-021F50F7F2CB@.microsoft.com...
>
>
|||It doesn't have to be the last transaction log, but it would have to be the
next one in the chain. Consider if you dump each hour, you might not have
another log for 1 hour or so.
Also you don't need to put it back into norecovery, the next log applied
will. I prefer to put it back into norecovery so my users can't access it
and then interfer with the next tlog restore.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kev" <Kev@.discussions.microsoft.com> wrote in message
news:629337E7-A409-49C3-9C99-7510FC802069@.microsoft.com...[vbcol=seagreen]
> Is it possible to do something like:
> restore database [logshippingtestbak] with
> standby=''c:\logshippingtestlog.bak'
> <do the reconciliation>
> restore database [logshippingtestbak] with norecovery
> or do you always need to restore the last transaction log to switch modes.
> Cheers
> Kev.
>
> "Hilary Cotter" wrote:
|||Would my bit of sql work?
"Hilary Cotter" wrote:
> It doesn't have to be the last transaction log, but it would have to be the
> next one in the chain. Consider if you dump each hour, you might not have
> another log for 1 hour or so.
> Also you don't need to put it back into norecovery, the next log applied
> will. I prefer to put it back into norecovery so my users can't access it
> and then interfer with the next tlog restore.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kev" <Kev@.discussions.microsoft.com> wrote in message
> news:629337E7-A409-49C3-9C99-7510FC802069@.microsoft.com...
>
>
|||No, you would have to restore the log, like this
restore log [logshippingtestbak] from disk='c:\mylog' with
standby=''c:\logshippingtestlog.bak'
<do the reconciliation>
restore log [logshippingtestbak] from disk='c:\mylog1.bak' with norecovery
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kev" <Kev@.discussions.microsoft.com> wrote in message
news:10217BE0-4F01-44D0-B7F6-6E6661B385F9@.microsoft.com...[vbcol=seagreen]
> Would my bit of sql work?
> "Hilary Cotter" wrote:
|||Are there any disadvantages to keeping it in standby mode all the time?
Presumably you can still restore in the same way - you just have to be
careful that nobody tries to access it at the wrong time.
Kev.
"Hilary Cotter" wrote:
> No, you would have to restore the log, like this
> restore log [logshippingtestbak] from disk='c:\mylog' with
> standby=''c:\logshippingtestlog.bak'
> <do the reconciliation>
> restore log [logshippingtestbak] from disk='c:\mylog1.bak' with norecovery
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kev" <Kev@.discussions.microsoft.com> wrote in message
> news:10217BE0-4F01-44D0-B7F6-6E6661B385F9@.microsoft.com...
>
>
reconciles object counts and table counts etc between the two.
Is there a way to run such a script across a primary and secondary in log
shipping configuration. As the secondary is in RESTORING state it's not
accessible. Therefore is it possible to do something like bring it briefly up
to standby state, run the reconciliation and then take it back to restoring
again? Would this work and are there any gotchas?
Cheers.
Here is an example of how to do it:
there are two databases logshippingtestbak and logshippingtest.
I backup loshippingtest and restore this backup into the database
logshippingtestback with no recovery
backup log logshippingtest to disk='c:\logshippingtestlog.bak'
GO
--restoring my first log
restore log logshippingtestbak from disk='c:\logshippingtestlog.bak' with
norecovery
GO
select * from logshippingtestbak.dbo.sysobjects
Server: Msg 927, Level 14, State 2, Line 1
Database 'logshippingtestbak' cannot be opened. It is in the middle of a
restore.
--i backup another log
backup log logshippingtest to disk='c:\logshippingtestlog.bak'
--i restore it with the standby option
restore log logshippingtestbak from disk='c:\logshippingtestlog.bak' with
standby ='c:\standby.bak'
--i read this database
select * from logshippingtestbak.dbo.sysobjects
--I restore the same log this time with norecovery
restore log logshippingtestbak from disk='c:\logshippingtestlog.bak' with
norecovery
--I verify this database is inaccessible again.
select * from logshippingtestbak.dbo.sysobjects
Server: Msg 927, Level 14, State 2, Line 1
Database 'logshippingtestbak' cannot be opened. It is in the middle of a
restore.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kev" <Kev@.discussions.microsoft.com> wrote in message
news:5722A586-57E3-41AA-B6DF-021F50F7F2CB@.microsoft.com...
>I have a database reconciliation script that runs between two databases and
> reconciles object counts and table counts etc between the two.
> Is there a way to run such a script across a primary and secondary in log
> shipping configuration. As the secondary is in RESTORING state it's not
> accessible. Therefore is it possible to do something like bring it briefly
> up
> to standby state, run the reconciliation and then take it back to
> restoring
> again? Would this work and are there any gotchas?
> Cheers.
|||Is it possible to do something like:
restore database [logshippingtestbak] with
standby=''c:\logshippingtestlog.bak'
<do the reconciliation>
restore database [logshippingtestbak] with norecovery
or do you always need to restore the last transaction log to switch modes.
Cheers
Kev.
"Hilary Cotter" wrote:
> Here is an example of how to do it:
> there are two databases logshippingtestbak and logshippingtest.
> I backup loshippingtest and restore this backup into the database
> logshippingtestback with no recovery
> backup log logshippingtest to disk='c:\logshippingtestlog.bak'
> GO
> --restoring my first log
> restore log logshippingtestbak from disk='c:\logshippingtestlog.bak' with
> norecovery
> GO
> select * from logshippingtestbak.dbo.sysobjects
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'logshippingtestbak' cannot be opened. It is in the middle of a
> restore.
> --i backup another log
> backup log logshippingtest to disk='c:\logshippingtestlog.bak'
> --i restore it with the standby option
> restore log logshippingtestbak from disk='c:\logshippingtestlog.bak' with
> standby ='c:\standby.bak'
> --i read this database
> select * from logshippingtestbak.dbo.sysobjects
> --I restore the same log this time with norecovery
> restore log logshippingtestbak from disk='c:\logshippingtestlog.bak' with
> norecovery
> --I verify this database is inaccessible again.
> select * from logshippingtestbak.dbo.sysobjects
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'logshippingtestbak' cannot be opened. It is in the middle of a
> restore.
>
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kev" <Kev@.discussions.microsoft.com> wrote in message
> news:5722A586-57E3-41AA-B6DF-021F50F7F2CB@.microsoft.com...
>
>
|||It doesn't have to be the last transaction log, but it would have to be the
next one in the chain. Consider if you dump each hour, you might not have
another log for 1 hour or so.
Also you don't need to put it back into norecovery, the next log applied
will. I prefer to put it back into norecovery so my users can't access it
and then interfer with the next tlog restore.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kev" <Kev@.discussions.microsoft.com> wrote in message
news:629337E7-A409-49C3-9C99-7510FC802069@.microsoft.com...[vbcol=seagreen]
> Is it possible to do something like:
> restore database [logshippingtestbak] with
> standby=''c:\logshippingtestlog.bak'
> <do the reconciliation>
> restore database [logshippingtestbak] with norecovery
> or do you always need to restore the last transaction log to switch modes.
> Cheers
> Kev.
>
> "Hilary Cotter" wrote:
|||Would my bit of sql work?
"Hilary Cotter" wrote:
> It doesn't have to be the last transaction log, but it would have to be the
> next one in the chain. Consider if you dump each hour, you might not have
> another log for 1 hour or so.
> Also you don't need to put it back into norecovery, the next log applied
> will. I prefer to put it back into norecovery so my users can't access it
> and then interfer with the next tlog restore.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kev" <Kev@.discussions.microsoft.com> wrote in message
> news:629337E7-A409-49C3-9C99-7510FC802069@.microsoft.com...
>
>
|||No, you would have to restore the log, like this
restore log [logshippingtestbak] from disk='c:\mylog' with
standby=''c:\logshippingtestlog.bak'
<do the reconciliation>
restore log [logshippingtestbak] from disk='c:\mylog1.bak' with norecovery
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kev" <Kev@.discussions.microsoft.com> wrote in message
news:10217BE0-4F01-44D0-B7F6-6E6661B385F9@.microsoft.com...[vbcol=seagreen]
> Would my bit of sql work?
> "Hilary Cotter" wrote:
|||Are there any disadvantages to keeping it in standby mode all the time?
Presumably you can still restore in the same way - you just have to be
careful that nobody tries to access it at the wrong time.
Kev.
"Hilary Cotter" wrote:
> No, you would have to restore the log, like this
> restore log [logshippingtestbak] from disk='c:\mylog' with
> standby=''c:\logshippingtestlog.bak'
> <do the reconciliation>
> restore log [logshippingtestbak] from disk='c:\mylog1.bak' with norecovery
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kev" <Kev@.discussions.microsoft.com> wrote in message
> news:10217BE0-4F01-44D0-B7F6-6E6661B385F9@.microsoft.com...
>
>
Friday, March 23, 2012
Log Shipping Problem
Hello Everyone. I am log shippping on a server that has heavy reads and
writes. The Log Ship process runs every 15 minutes and when it runs, it
tends to significantly slow down all the queries running on the box. Queries
which run 20-100 milliseconds run for over 2-3 seconds. The slowing causes
are webservers to back up which in turn slows are website.
I notice in PerMon that after every transaction log backup a checkpoint is
run immedialty after.
The queries are slow during the checkpoint process.
Is there a reason for this? Also, how can I avoid this slow down.
About the Server:
Windows 2003 Enterprise Edition
Quad processeor with Intel XEON 3.0 Ghz.
SQL 2000 version 8.00.936
Thanks,
George
George
There is no real way to avoid the checkpoint process. The checkpoint process
tells SQL Server to write completed transactions to disk, something you
obviously want to happen. You can make the gap between checkpoints longer by
altering the recovery interval. However if you do that the checkpoints will
last longer when they do happen (they will have more to do) and your
transation logs will probably get bigger, and you system will take longer to
come up following a reboot.
About the only thing you can do is look at your disk configuration, to see
if there is a way to speed up your I/O processes.
Hope this helps
John
"George teVelde" wrote:
> Hello Everyone. I am log shippping on a server that has heavy reads and
> writes. The Log Ship process runs every 15 minutes and when it runs, it
> tends to significantly slow down all the queries running on the box. Queries
> which run 20-100 milliseconds run for over 2-3 seconds. The slowing causes
> are webservers to back up which in turn slows are website.
> I notice in PerMon that after every transaction log backup a checkpoint is
> run immedialty after.
> The queries are slow during the checkpoint process.
> Is there a reason for this? Also, how can I avoid this slow down.
> About the Server:
> Windows 2003 Enterprise Edition
> Quad processeor with Intel XEON 3.0 Ghz.
> SQL 2000 version 8.00.936
> Thanks,
> George
|||Thanks for the reply, I have one more question.
Is there any reason Log Shipping would grab all the availble threads and not
release them?
I realize that it may not be the log shipping doing it, but I am trying to
eliminate as many variables as I can.
Thanks.
|||Hi
No, no reason. If checkpointing affects your performance, the disk layout is
probably not optimal. Basic rule, Logs on a separate set of drive, not on
RAID-5, but on mirrors.
Regards
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/
"George teVelde" <GeorgeteVelde@.discussions.microsoft.com> wrote in message
news:69243841-B076-4990-84E1-4C38A7FA4548@.microsoft.com...
> Thanks for the reply, I have one more question.
> Is there any reason Log Shipping would grab all the availble threads and
not
> release them?
> I realize that it may not be the log shipping doing it, but I am trying to
> eliminate as many variables as I can.
> Thanks.
writes. The Log Ship process runs every 15 minutes and when it runs, it
tends to significantly slow down all the queries running on the box. Queries
which run 20-100 milliseconds run for over 2-3 seconds. The slowing causes
are webservers to back up which in turn slows are website.
I notice in PerMon that after every transaction log backup a checkpoint is
run immedialty after.
The queries are slow during the checkpoint process.
Is there a reason for this? Also, how can I avoid this slow down.
About the Server:
Windows 2003 Enterprise Edition
Quad processeor with Intel XEON 3.0 Ghz.
SQL 2000 version 8.00.936
Thanks,
George
George
There is no real way to avoid the checkpoint process. The checkpoint process
tells SQL Server to write completed transactions to disk, something you
obviously want to happen. You can make the gap between checkpoints longer by
altering the recovery interval. However if you do that the checkpoints will
last longer when they do happen (they will have more to do) and your
transation logs will probably get bigger, and you system will take longer to
come up following a reboot.
About the only thing you can do is look at your disk configuration, to see
if there is a way to speed up your I/O processes.
Hope this helps
John
"George teVelde" wrote:
> Hello Everyone. I am log shippping on a server that has heavy reads and
> writes. The Log Ship process runs every 15 minutes and when it runs, it
> tends to significantly slow down all the queries running on the box. Queries
> which run 20-100 milliseconds run for over 2-3 seconds. The slowing causes
> are webservers to back up which in turn slows are website.
> I notice in PerMon that after every transaction log backup a checkpoint is
> run immedialty after.
> The queries are slow during the checkpoint process.
> Is there a reason for this? Also, how can I avoid this slow down.
> About the Server:
> Windows 2003 Enterprise Edition
> Quad processeor with Intel XEON 3.0 Ghz.
> SQL 2000 version 8.00.936
> Thanks,
> George
|||Thanks for the reply, I have one more question.
Is there any reason Log Shipping would grab all the availble threads and not
release them?
I realize that it may not be the log shipping doing it, but I am trying to
eliminate as many variables as I can.
Thanks.
|||Hi
No, no reason. If checkpointing affects your performance, the disk layout is
probably not optimal. Basic rule, Logs on a separate set of drive, not on
RAID-5, but on mirrors.
Regards
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/
"George teVelde" <GeorgeteVelde@.discussions.microsoft.com> wrote in message
news:69243841-B076-4990-84E1-4C38A7FA4548@.microsoft.com...
> Thanks for the reply, I have one more question.
> Is there any reason Log Shipping would grab all the availble threads and
not
> release them?
> I realize that it may not be the log shipping doing it, but I am trying to
> eliminate as many variables as I can.
> Thanks.
Log Shipping Problem
Hello Everyone. I am log shippping on a server that has heavy reads and
writes. The Log Ship process runs every 15 minutes and when it runs, it
tends to significantly slow down all the queries running on the box. Querie
s
which run 20-100 milliseconds run for over 2-3 seconds. The slowing causes
are webservers to back up which in turn slows are website.
I notice in PerMon that after every transaction log backup a checkpoint is
run immedialty after.
The queries are slow during the checkpoint process.
Is there a reason for this? Also, how can I avoid this slow down.
About the Server:
Windows 2003 Enterprise Edition
Quad processeor with Intel XEON 3.0 Ghz.
SQL 2000 version 8.00.936
Thanks,
GeorgeGeorge
There is no real way to avoid the checkpoint process. The checkpoint process
tells SQL Server to write completed transactions to disk, something you
obviously want to happen. You can make the gap between checkpoints longer by
altering the recovery interval. However if you do that the checkpoints will
last longer when they do happen (they will have more to do) and your
transation logs will probably get bigger, and you system will take longer to
come up following a reboot.
About the only thing you can do is look at your disk configuration, to see
if there is a way to speed up your I/O processes.
Hope this helps
John
"George teVelde" wrote:
> Hello Everyone. I am log shippping on a server that has heavy reads and
> writes. The Log Ship process runs every 15 minutes and when it runs, it
> tends to significantly slow down all the queries running on the box. Quer
ies
> which run 20-100 milliseconds run for over 2-3 seconds. The slowing cause
s
> are webservers to back up which in turn slows are website.
> I notice in PerMon that after every transaction log backup a checkpoint is
> run immedialty after.
> The queries are slow during the checkpoint process.
> Is there a reason for this? Also, how can I avoid this slow down.
> About the Server:
> Windows 2003 Enterprise Edition
> Quad processeor with Intel XEON 3.0 Ghz.
> SQL 2000 version 8.00.936
> Thanks,
> George|||Thanks for the reply, I have one more question.
Is there any reason Log Shipping would grab all the availble threads and not
release them?
I realize that it may not be the log shipping doing it, but I am trying to
eliminate as many variables as I can.
Thanks.|||Hi
No, no reason. If checkpointing affects your performance, the disk layout is
probably not optimal. Basic rule, Logs on a separate set of drive, not on
RAID-5, but on mirrors.
Regards
--
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/
"George teVelde" <GeorgeteVelde@.discussions.microsoft.com> wrote in message
news:69243841-B076-4990-84E1-4C38A7FA4548@.microsoft.com...
> Thanks for the reply, I have one more question.
> Is there any reason Log Shipping would grab all the availble threads and
not
> release them?
> I realize that it may not be the log shipping doing it, but I am trying to
> eliminate as many variables as I can.
> Thanks.sql
writes. The Log Ship process runs every 15 minutes and when it runs, it
tends to significantly slow down all the queries running on the box. Querie
s
which run 20-100 milliseconds run for over 2-3 seconds. The slowing causes
are webservers to back up which in turn slows are website.
I notice in PerMon that after every transaction log backup a checkpoint is
run immedialty after.
The queries are slow during the checkpoint process.
Is there a reason for this? Also, how can I avoid this slow down.
About the Server:
Windows 2003 Enterprise Edition
Quad processeor with Intel XEON 3.0 Ghz.
SQL 2000 version 8.00.936
Thanks,
GeorgeGeorge
There is no real way to avoid the checkpoint process. The checkpoint process
tells SQL Server to write completed transactions to disk, something you
obviously want to happen. You can make the gap between checkpoints longer by
altering the recovery interval. However if you do that the checkpoints will
last longer when they do happen (they will have more to do) and your
transation logs will probably get bigger, and you system will take longer to
come up following a reboot.
About the only thing you can do is look at your disk configuration, to see
if there is a way to speed up your I/O processes.
Hope this helps
John
"George teVelde" wrote:
> Hello Everyone. I am log shippping on a server that has heavy reads and
> writes. The Log Ship process runs every 15 minutes and when it runs, it
> tends to significantly slow down all the queries running on the box. Quer
ies
> which run 20-100 milliseconds run for over 2-3 seconds. The slowing cause
s
> are webservers to back up which in turn slows are website.
> I notice in PerMon that after every transaction log backup a checkpoint is
> run immedialty after.
> The queries are slow during the checkpoint process.
> Is there a reason for this? Also, how can I avoid this slow down.
> About the Server:
> Windows 2003 Enterprise Edition
> Quad processeor with Intel XEON 3.0 Ghz.
> SQL 2000 version 8.00.936
> Thanks,
> George|||Thanks for the reply, I have one more question.
Is there any reason Log Shipping would grab all the availble threads and not
release them?
I realize that it may not be the log shipping doing it, but I am trying to
eliminate as many variables as I can.
Thanks.|||Hi
No, no reason. If checkpointing affects your performance, the disk layout is
probably not optimal. Basic rule, Logs on a separate set of drive, not on
RAID-5, but on mirrors.
Regards
--
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/
"George teVelde" <GeorgeteVelde@.discussions.microsoft.com> wrote in message
news:69243841-B076-4990-84E1-4C38A7FA4548@.microsoft.com...
> Thanks for the reply, I have one more question.
> Is there any reason Log Shipping would grab all the availble threads and
not
> release them?
> I realize that it may not be the log shipping doing it, but I am trying to
> eliminate as many variables as I can.
> Thanks.sql
Log Shipping Problem
Hello Everyone. I am log shippping on a server that has heavy reads and
writes. The Log Ship process runs every 15 minutes and when it runs, it
tends to significantly slow down all the queries running on the box. Queries
which run 20-100 milliseconds run for over 2-3 seconds. The slowing causes
are webservers to back up which in turn slows are website.
I notice in PerMon that after every transaction log backup a checkpoint is
run immedialty after.
The queries are slow during the checkpoint process.
Is there a reason for this? Also, how can I avoid this slow down.
About the Server:
Windows 2003 Enterprise Edition
Quad processeor with Intel XEON 3.0 Ghz.
SQL 2000 version 8.00.936
Thanks,
GeorgeGeorge
There is no real way to avoid the checkpoint process. The checkpoint process
tells SQL Server to write completed transactions to disk, something you
obviously want to happen. You can make the gap between checkpoints longer by
altering the recovery interval. However if you do that the checkpoints will
last longer when they do happen (they will have more to do) and your
transation logs will probably get bigger, and you system will take longer to
come up following a reboot.
About the only thing you can do is look at your disk configuration, to see
if there is a way to speed up your I/O processes.
Hope this helps
John
"George teVelde" wrote:
> Hello Everyone. I am log shippping on a server that has heavy reads and
> writes. The Log Ship process runs every 15 minutes and when it runs, it
> tends to significantly slow down all the queries running on the box. Queries
> which run 20-100 milliseconds run for over 2-3 seconds. The slowing causes
> are webservers to back up which in turn slows are website.
> I notice in PerMon that after every transaction log backup a checkpoint is
> run immedialty after.
> The queries are slow during the checkpoint process.
> Is there a reason for this? Also, how can I avoid this slow down.
> About the Server:
> Windows 2003 Enterprise Edition
> Quad processeor with Intel XEON 3.0 Ghz.
> SQL 2000 version 8.00.936
> Thanks,
> George|||Thanks for the reply, I have one more question.
Is there any reason Log Shipping would grab all the availble threads and not
release them?
I realize that it may not be the log shipping doing it, but I am trying to
eliminate as many variables as I can.
Thanks.|||Hi
No, no reason. If checkpointing affects your performance, the disk layout is
probably not optimal. Basic rule, Logs on a separate set of drive, not on
RAID-5, but on mirrors.
Regards
--
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/
"George teVelde" <GeorgeteVelde@.discussions.microsoft.com> wrote in message
news:69243841-B076-4990-84E1-4C38A7FA4548@.microsoft.com...
> Thanks for the reply, I have one more question.
> Is there any reason Log Shipping would grab all the availble threads and
not
> release them?
> I realize that it may not be the log shipping doing it, but I am trying to
> eliminate as many variables as I can.
> Thanks.
writes. The Log Ship process runs every 15 minutes and when it runs, it
tends to significantly slow down all the queries running on the box. Queries
which run 20-100 milliseconds run for over 2-3 seconds. The slowing causes
are webservers to back up which in turn slows are website.
I notice in PerMon that after every transaction log backup a checkpoint is
run immedialty after.
The queries are slow during the checkpoint process.
Is there a reason for this? Also, how can I avoid this slow down.
About the Server:
Windows 2003 Enterprise Edition
Quad processeor with Intel XEON 3.0 Ghz.
SQL 2000 version 8.00.936
Thanks,
GeorgeGeorge
There is no real way to avoid the checkpoint process. The checkpoint process
tells SQL Server to write completed transactions to disk, something you
obviously want to happen. You can make the gap between checkpoints longer by
altering the recovery interval. However if you do that the checkpoints will
last longer when they do happen (they will have more to do) and your
transation logs will probably get bigger, and you system will take longer to
come up following a reboot.
About the only thing you can do is look at your disk configuration, to see
if there is a way to speed up your I/O processes.
Hope this helps
John
"George teVelde" wrote:
> Hello Everyone. I am log shippping on a server that has heavy reads and
> writes. The Log Ship process runs every 15 minutes and when it runs, it
> tends to significantly slow down all the queries running on the box. Queries
> which run 20-100 milliseconds run for over 2-3 seconds. The slowing causes
> are webservers to back up which in turn slows are website.
> I notice in PerMon that after every transaction log backup a checkpoint is
> run immedialty after.
> The queries are slow during the checkpoint process.
> Is there a reason for this? Also, how can I avoid this slow down.
> About the Server:
> Windows 2003 Enterprise Edition
> Quad processeor with Intel XEON 3.0 Ghz.
> SQL 2000 version 8.00.936
> Thanks,
> George|||Thanks for the reply, I have one more question.
Is there any reason Log Shipping would grab all the availble threads and not
release them?
I realize that it may not be the log shipping doing it, but I am trying to
eliminate as many variables as I can.
Thanks.|||Hi
No, no reason. If checkpointing affects your performance, the disk layout is
probably not optimal. Basic rule, Logs on a separate set of drive, not on
RAID-5, but on mirrors.
Regards
--
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/
"George teVelde" <GeorgeteVelde@.discussions.microsoft.com> wrote in message
news:69243841-B076-4990-84E1-4C38A7FA4548@.microsoft.com...
> Thanks for the reply, I have one more question.
> Is there any reason Log Shipping would grab all the availble threads and
not
> release them?
> I realize that it may not be the log shipping doing it, but I am trying to
> eliminate as many variables as I can.
> Thanks.
Monday, March 12, 2012
Log Shipping from SQL Server 2000 to SQL Server 2005
I work for a small state government department. We are about to install SQL Server 2005.
A national government department that runs on SQL Server 2000 are willing to give us a backup of their database (20 gig I think) and then log ship to us regulary. Our aim is to have a copy of the database that we can query from and that is only a day or so out-of-date.
I'm having no luck.
1. I can backup the SQL Server 2000 database.
2. I can restore the SQL Server 2000 database onto the SQL Server 2005 box no worries.
2. BUT, if I attempt to restore the 2000 database with the option 'Leave the database in read-only mode ...' selected (so that I can then restore transaction logs and it's read-only), then I get an error message. ""This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.". I'm doing all this using SQL Server Management Studio.
It appears we will have to use SQL Server 2000 rather than 2005 to get this working.
Q: Has anyone successfully done this from SQL 2000 to SQL 2005 please? If so, how was it achieved please?
Thanks.
Geoff - Did you find an answer to this?
I'm also trying to log ship from 2000 to 2005
|||There is a good explanation about logshipping in the BOL.
"Upgrading a SQL Server 2000 Log Shipping Configuration"
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||The bottom line is that both ends of a log shipping configuration need to be at the same version.
In this case, you have a SQL 2005 instance on your end, and no ability to upgrade the primary database, as you don't control it, right?
One solution would be to install a SQL 2000 instance on your end, and use that for your end of the log-shipping configuration. There is no problem with having SQL 2005 and SQL 2000 instances side-by-side on the same machine.
You can then access the data in the SQL 2000 instance either from within that instance, or by setting up a distributed query from the SQL 2005 instance.
|||hi,
I,m facing the same problem.
I know this case is already got asnwered status.
but I can't really use 2 sql, 2000 and 2005 cause it's license matter. I read that log shipping is consist 3 step.
1. back up the db
2. copy the backup
3. restore the db
I have lil experience in database. can it be done manually ?
anybody can help me create that step manually ?
regards,
-dedys
|||Is this the official MircoSoft story on Logshipping between 2000 to 2005 SQL Servers?
I work at a company that could use SQL 2000 and SQL 2005 side by side on the Log Ship receiver
but I prefer to just restore the SQL 2000 transaction log back on the new SQL 2005 DB. Are the file
formats of the transaction log different between SQL 2000 and 2005? Thanks.
|||Ok Chaps,
You cannot log ship from SQL 2000 to SQL 2005.
What you can do is.
1. Log ship from SQL 2000 to SQL 2000
2. Then upgrade the DR server to 2005.
3. And carry on log shipping until you recover or failover to DR.
The DR server database will remain in SQL 2000 until you recover it. This works if you carry on restoring the log with norecovery. Because you use standby, the recovery is performed each time, the above will not work for you.
Geoff,
For your case try using replication instead.
Jag
Log Shipping from SQL Server 2000 to SQL Server 2005
I work for a small state government department. We are about to install SQL Server 2005.
A national government department that runs on SQL Server 2000 are willing to give us a backup of their database (20 gig I think) and then log ship to us regulary. Our aim is to have a copy of the database that we can query from and that is only a day or so out-of-date.
I'm having no luck.
1. I can backup the SQL Server 2000 database.
2. I can restore the SQL Server 2000 database onto the SQL Server 2005 box no worries.
2. BUT, if I attempt to restore the 2000 database with the option 'Leave the database in read-only mode ...' selected (so that I can then restore transaction logs and it's read-only), then I get an error message. ""This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.". I'm doing all this using SQL Server Management Studio.
It appears we will have to use SQL Server 2000 rather than 2005 to get this working.
Q: Has anyone successfully done this from SQL 2000 to SQL 2005 please? If so, how was it achieved please?
Thanks.
Geoff - Did you find an answer to this?
I'm also trying to log ship from 2000 to 2005
|||There is a good explanation about logshipping in the BOL.
"Upgrading a SQL Server 2000 Log Shipping Configuration"
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||The bottom line is that both ends of a log shipping configuration need to be at the same version.
In this case, you have a SQL 2005 instance on your end, and no ability to upgrade the primary database, as you don't control it, right?
One solution would be to install a SQL 2000 instance on your end, and use that for your end of the log-shipping configuration. There is no problem with having SQL 2005 and SQL 2000 instances side-by-side on the same machine.
You can then access the data in the SQL 2000 instance either from within that instance, or by setting up a distributed query from the SQL 2005 instance.
|||hi,
I,m facing the same problem.
I know this case is already got asnwered status.
but I can't really use 2 sql, 2000 and 2005 cause it's license matter. I read that log shipping is consist 3 step.
1. back up the db
2. copy the backup
3. restore the db
I have lil experience in database. can it be done manually ?
anybody can help me create that step manually ?
regards,
-dedys
|||Is this the official MircoSoft story on Logshipping between 2000 to 2005 SQL Servers?
I work at a company that could use SQL 2000 and SQL 2005 side by side on the Log Ship receiver
but I prefer to just restore the SQL 2000 transaction log back on the new SQL 2005 DB. Are the file
formats of the transaction log different between SQL 2000 and 2005? Thanks.
|||Ok Chaps,
You cannot log ship from SQL 2000 to SQL 2005.
What you can do is.
1. Log ship from SQL 2000 to SQL 2000
2. Then upgrade the DR server to 2005.
3. And carry on log shipping until you recover or failover to DR.
The DR server database will remain in SQL 2000 until you recover it. This works if you carry on restoring the log with norecovery. Because you use standby, the recovery is performed each time, the above will not work for you.
Geoff,
For your case try using replication instead.
Jag
Log Shipping from SQL Server 2000 to SQL Server 2005
I work for a small state government department. We are about to install SQL Server 2005.
A national government department that runs on SQL Server 2000 are willing to give us a backup of their database (20 gig I think) and then log ship to us regulary. Our aim is to have a copy of the database that we can query from and that is only a day or so out-of-date.
I'm having no luck.
1. I can backup the SQL Server 2000 database.
2. I can restore the SQL Server 2000 database onto the SQL Server 2005 box no worries.
2. BUT, if I attempt to restore the 2000 database with the option 'Leave the database in read-only mode ...' selected (so that I can then restore transaction logs and it's read-only), then I get an error message. ""This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.". I'm doing all this using SQL Server Management Studio.
It appears we will have to use SQL Server 2000 rather than 2005 to get this working.
Q: Has anyone successfully done this from SQL 2000 to SQL 2005 please? If so, how was it achieved please?
Thanks.
Geoff - Did you find an answer to this?
I'm also trying to log ship from 2000 to 2005
|||There is a good explanation about logshipping in the BOL.
"Upgrading a SQL Server 2000 Log Shipping Configuration"
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||The bottom line is that both ends of a log shipping configuration need to be at the same version.
In this case, you have a SQL 2005 instance on your end, and no ability to upgrade the primary database, as you don't control it, right?
One solution would be to install a SQL 2000 instance on your end, and use that for your end of the log-shipping configuration. There is no problem with having SQL 2005 and SQL 2000 instances side-by-side on the same machine.
You can then access the data in the SQL 2000 instance either from within that instance, or by setting up a distributed query from the SQL 2005 instance.
|||hi,
I,m facing the same problem.
I know this case is already got asnwered status.
but I can't really use 2 sql, 2000 and 2005 cause it's license matter. I read that log shipping is consist 3 step.
1. back up the db
2. copy the backup
3. restore the db
I have lil experience in database. can it be done manually ?
anybody can help me create that step manually ?
regards,
-dedys
|||Is this the official MircoSoft story on Logshipping between 2000 to 2005 SQL Servers?
I work at a company that could use SQL 2000 and SQL 2005 side by side on the Log Ship receiver
but I prefer to just restore the SQL 2000 transaction log back on the new SQL 2005 DB. Are the file
formats of the transaction log different between SQL 2000 and 2005? Thanks.
|||Ok Chaps,
You cannot log ship from SQL 2000 to SQL 2005.
What you can do is.
1. Log ship from SQL 2000 to SQL 2000
2. Then upgrade the DR server to 2005.
3. And carry on log shipping until you recover or failover to DR.
The DR server database will remain in SQL 2000 until you recover it. This works if you carry on restoring the log with norecovery. Because you use standby, the recovery is performed each time, the above will not work for you.
Geoff,
For your case try using replication instead.
Jag
Log Shipping from SQL Server 2000 to SQL Server 2005
I work for a small state government department. We are about to install SQL Server 2005.
A national government department that runs on SQL Server 2000 are willing to give us a backup of their database (20 gig I think) and then log ship to us regulary. Our aim is to have a copy of the database that we can query from and that is only a day or so out-of-date.
I'm having no luck.
1. I can backup the SQL Server 2000 database.
2. I can restore the SQL Server 2000 database onto the SQL Server 2005 box no worries.
2. BUT, if I attempt to restore the 2000 database with the option 'Leave the database in read-only mode ...' selected (so that I can then restore transaction logs and it's read-only), then I get an error message. ""This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.". I'm doing all this using SQL Server Management Studio.
It appears we will have to use SQL Server 2000 rather than 2005 to get this working.
Q: Has anyone successfully done this from SQL 2000 to SQL 2005 please? If so, how was it achieved please?
Thanks.
Geoff - Did you find an answer to this?
I'm also trying to log ship from 2000 to 2005
|||There is a good explanation about logshipping in the BOL.
"Upgrading a SQL Server 2000 Log Shipping Configuration"
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||The bottom line is that both ends of a log shipping configuration need to be at the same version.
In this case, you have a SQL 2005 instance on your end, and no ability to upgrade the primary database, as you don't control it, right?
One solution would be to install a SQL 2000 instance on your end, and use that for your end of the log-shipping configuration. There is no problem with having SQL 2005 and SQL 2000 instances side-by-side on the same machine.
You can then access the data in the SQL 2000 instance either from within that instance, or by setting up a distributed query from the SQL 2005 instance.
|||hi,
I,m facing the same problem.
I know this case is already got asnwered status.
but I can't really use 2 sql, 2000 and 2005 cause it's license matter. I read that log shipping is consist 3 step.
1. back up the db
2. copy the backup
3. restore the db
I have lil experience in database. can it be done manually ?
anybody can help me create that step manually ?
regards,
-dedys
|||Is this the official MircoSoft story on Logshipping between 2000 to 2005 SQL Servers?
I work at a company that could use SQL 2000 and SQL 2005 side by side on the Log Ship receiver
but I prefer to just restore the SQL 2000 transaction log back on the new SQL 2005 DB. Are the file
formats of the transaction log different between SQL 2000 and 2005? Thanks.
|||Ok Chaps,
You cannot log ship from SQL 2000 to SQL 2005.
What you can do is.
1. Log ship from SQL 2000 to SQL 2000
2. Then upgrade the DR server to 2005.
3. And carry on log shipping until you recover or failover to DR.
The DR server database will remain in SQL 2000 until you recover it. This works if you carry on restoring the log with norecovery. Because you use standby, the recovery is performed each time, the above will not work for you.
Geoff,
For your case try using replication instead.
Jag
Log Shipping fails after DB Reindex.
Can anyone help.
I have set up log shipping between two SQL 2000 servers, with no issues. A
job runs on the Primary server to reindex the databases. At this point Log
Shipping Fails, the error massage says that the servers are out of sync.
The log file shows the error
The log in this backup set begins at LSN 202000000017000001, which is too
late to apply to the database. An earlier log backup that includes LSN
20200000
0016800001 can be restored.
Is there a way to reindex the database that does not affect log shipping?
Many thanks in advance
Andy McDonald
MCSA
"Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> Can anyone help.
> I have set up log shipping between two SQL 2000 servers, with no issues. A
> job runs on the Primary server to reindex the databases. At this point Log
> Shipping Fails, the error massage says that the servers are out of sync.
> The log file shows the error
> The log in this backup set begins at LSN 202000000017000001, which is too
> late to apply to the database. An earlier log backup that includes LSN
> 20200000
> 0016800001 can be restored.
> Is there a way to reindex the database that does not affect log shipping?
It sounds more like somehow you're losing a log backup somewhere along the
line.
reindexing doesn't (or at least shouldn't) affect log-shipping (having just
done this lately).
> Many thanks in advance
> Andy McDonald
> MCSA
|||Greg
The data base is a large one, (about 25 GB) dont know if this helps. but we
only receive the error when re-indexing takes place.
Andy McDonald
MCSA
"Greg D. Moore (Strider)" wrote:
> "Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
> news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> It sounds more like somehow you're losing a log backup somewhere along the
> line.
> reindexing doesn't (or at least shouldn't) affect log-shipping (having just
> done this lately).
>
>
>
|||Are you 100% certain that you don't set the database to simple recovery mode during reindex?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...[vbcol=seagreen]
> Greg
> The data base is a large one, (about 25 GB) dont know if this helps. but we
> only receive the error when re-indexing takes place.
> Andy McDonald
> MCSA
> "Greg D. Moore (Strider)" wrote:
|||Hello
Yes I am sure.
"Tibor Karaszi" wrote:
> Are you 100% certain that you don't set the database to simple recovery mode during reindex?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
> news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...
>
>
|||"Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...
> Greg
> The data base is a large one, (about 25 GB) dont know if this helps. but
we
> only receive the error when re-indexing takes place.
Very weird. Really sounds like a log is benig lost somewhere along the
line.
Which I had a better answer.
[vbcol=seagreen]
> Andy McDonald
> MCSA
> "Greg D. Moore (Strider)" wrote:
message[vbcol=seagreen]
issues. A[vbcol=seagreen]
Log[vbcol=seagreen]
sync.[vbcol=seagreen]
too[vbcol=seagreen]
shipping?[vbcol=seagreen]
the[vbcol=seagreen]
just[vbcol=seagreen]
I have set up log shipping between two SQL 2000 servers, with no issues. A
job runs on the Primary server to reindex the databases. At this point Log
Shipping Fails, the error massage says that the servers are out of sync.
The log file shows the error
The log in this backup set begins at LSN 202000000017000001, which is too
late to apply to the database. An earlier log backup that includes LSN
20200000
0016800001 can be restored.
Is there a way to reindex the database that does not affect log shipping?
Many thanks in advance
Andy McDonald
MCSA
"Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> Can anyone help.
> I have set up log shipping between two SQL 2000 servers, with no issues. A
> job runs on the Primary server to reindex the databases. At this point Log
> Shipping Fails, the error massage says that the servers are out of sync.
> The log file shows the error
> The log in this backup set begins at LSN 202000000017000001, which is too
> late to apply to the database. An earlier log backup that includes LSN
> 20200000
> 0016800001 can be restored.
> Is there a way to reindex the database that does not affect log shipping?
It sounds more like somehow you're losing a log backup somewhere along the
line.
reindexing doesn't (or at least shouldn't) affect log-shipping (having just
done this lately).
> Many thanks in advance
> Andy McDonald
> MCSA
|||Greg
The data base is a large one, (about 25 GB) dont know if this helps. but we
only receive the error when re-indexing takes place.
Andy McDonald
MCSA
"Greg D. Moore (Strider)" wrote:
> "Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
> news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> It sounds more like somehow you're losing a log backup somewhere along the
> line.
> reindexing doesn't (or at least shouldn't) affect log-shipping (having just
> done this lately).
>
>
>
|||Are you 100% certain that you don't set the database to simple recovery mode during reindex?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...[vbcol=seagreen]
> Greg
> The data base is a large one, (about 25 GB) dont know if this helps. but we
> only receive the error when re-indexing takes place.
> Andy McDonald
> MCSA
> "Greg D. Moore (Strider)" wrote:
|||Hello
Yes I am sure.
"Tibor Karaszi" wrote:
> Are you 100% certain that you don't set the database to simple recovery mode during reindex?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
> news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...
>
>
|||"Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...
> Greg
> The data base is a large one, (about 25 GB) dont know if this helps. but
we
> only receive the error when re-indexing takes place.
Very weird. Really sounds like a log is benig lost somewhere along the
line.
Which I had a better answer.
[vbcol=seagreen]
> Andy McDonald
> MCSA
> "Greg D. Moore (Strider)" wrote:
message[vbcol=seagreen]
issues. A[vbcol=seagreen]
Log[vbcol=seagreen]
sync.[vbcol=seagreen]
too[vbcol=seagreen]
shipping?[vbcol=seagreen]
the[vbcol=seagreen]
just[vbcol=seagreen]
Log Shipping fails after DB Reindex.
Can anyone help.
I have set up log shipping between two SQL 2000 servers, with no issues. A
job runs on the Primary server to reindex the databases. At this point Log
Shipping Fails, the error massage says that the servers are out of sync.
The log file shows the error
The log in this backup set begins at LSN 202000000017000001, which is too
late to apply to the database. An earlier log backup that includes LSN
20200000
0016800001 can be restored.
Is there a way to reindex the database that does not affect log shipping?
Many thanks in advance
Andy McDonald
MCSA"Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> Can anyone help.
> I have set up log shipping between two SQL 2000 servers, with no issues. A
> job runs on the Primary server to reindex the databases. At this point Log
> Shipping Fails, the error massage says that the servers are out of sync.
> The log file shows the error
> The log in this backup set begins at LSN 202000000017000001, which is too
> late to apply to the database. An earlier log backup that includes LSN
> 20200000
> 0016800001 can be restored.
> Is there a way to reindex the database that does not affect log shipping?
It sounds more like somehow you're losing a log backup somewhere along the
line.
reindexing doesn't (or at least shouldn't) affect log-shipping (having just
done this lately).
> Many thanks in advance
> Andy McDonald
> MCSA|||Greg
The data base is a large one, (about 25 GB) dont know if this helps. but we
only receive the error when re-indexing takes place.
Andy McDonald
MCSA
"Greg D. Moore (Strider)" wrote:
> "Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
> news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> > Can anyone help.
> >
> > I have set up log shipping between two SQL 2000 servers, with no issues. A
> > job runs on the Primary server to reindex the databases. At this point Log
> > Shipping Fails, the error massage says that the servers are out of sync.
> >
> > The log file shows the error
> > The log in this backup set begins at LSN 202000000017000001, which is too
> > late to apply to the database. An earlier log backup that includes LSN
> > 20200000
> > 0016800001 can be restored.
> >
> > Is there a way to reindex the database that does not affect log shipping?
> It sounds more like somehow you're losing a log backup somewhere along the
> line.
> reindexing doesn't (or at least shouldn't) affect log-shipping (having just
> done this lately).
>
> >
> > Many thanks in advance
> > Andy McDonald
> > MCSA
>
>|||Are you 100% certain that you don't set the database to simple recovery mode during reindex?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...
> Greg
> The data base is a large one, (about 25 GB) dont know if this helps. but we
> only receive the error when re-indexing takes place.
> Andy McDonald
> MCSA
> "Greg D. Moore (Strider)" wrote:
>> "Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
>> news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
>> > Can anyone help.
>> >
>> > I have set up log shipping between two SQL 2000 servers, with no issues. A
>> > job runs on the Primary server to reindex the databases. At this point Log
>> > Shipping Fails, the error massage says that the servers are out of sync.
>> >
>> > The log file shows the error
>> > The log in this backup set begins at LSN 202000000017000001, which is too
>> > late to apply to the database. An earlier log backup that includes LSN
>> > 20200000
>> > 0016800001 can be restored.
>> >
>> > Is there a way to reindex the database that does not affect log shipping?
>> It sounds more like somehow you're losing a log backup somewhere along the
>> line.
>> reindexing doesn't (or at least shouldn't) affect log-shipping (having just
>> done this lately).
>>
>> >
>> > Many thanks in advance
>> > Andy McDonald
>> > MCSA
>>|||Hello
Yes I am sure.
"Tibor Karaszi" wrote:
> Are you 100% certain that you don't set the database to simple recovery mode during reindex?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
> news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...
> > Greg
> >
> > The data base is a large one, (about 25 GB) dont know if this helps. but we
> > only receive the error when re-indexing takes place.
> >
> > Andy McDonald
> > MCSA
> >
> > "Greg D. Moore (Strider)" wrote:
> >
> >>
> >> "Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
> >> news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> >> > Can anyone help.
> >> >
> >> > I have set up log shipping between two SQL 2000 servers, with no issues. A
> >> > job runs on the Primary server to reindex the databases. At this point Log
> >> > Shipping Fails, the error massage says that the servers are out of sync.
> >> >
> >> > The log file shows the error
> >> > The log in this backup set begins at LSN 202000000017000001, which is too
> >> > late to apply to the database. An earlier log backup that includes LSN
> >> > 20200000
> >> > 0016800001 can be restored.
> >> >
> >> > Is there a way to reindex the database that does not affect log shipping?
> >>
> >> It sounds more like somehow you're losing a log backup somewhere along the
> >> line.
> >>
> >> reindexing doesn't (or at least shouldn't) affect log-shipping (having just
> >> done this lately).
> >>
> >>
> >> >
> >> > Many thanks in advance
> >> > Andy McDonald
> >> > MCSA
> >>
> >>
> >>
>
>|||"Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...
> Greg
> The data base is a large one, (about 25 GB) dont know if this helps. but
we
> only receive the error when re-indexing takes place.
Very weird. Really sounds like a log is benig lost somewhere along the
line.
Which I had a better answer.
> Andy McDonald
> MCSA
> "Greg D. Moore (Strider)" wrote:
> >
> > "Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in
message
> > news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> > > Can anyone help.
> > >
> > > I have set up log shipping between two SQL 2000 servers, with no
issues. A
> > > job runs on the Primary server to reindex the databases. At this point
Log
> > > Shipping Fails, the error massage says that the servers are out of
sync.
> > >
> > > The log file shows the error
> > > The log in this backup set begins at LSN 202000000017000001, which is
too
> > > late to apply to the database. An earlier log backup that includes LSN
> > > 20200000
> > > 0016800001 can be restored.
> > >
> > > Is there a way to reindex the database that does not affect log
shipping?
> >
> > It sounds more like somehow you're losing a log backup somewhere along
the
> > line.
> >
> > reindexing doesn't (or at least shouldn't) affect log-shipping (having
just
> > done this lately).
> >
> >
> > >
> > > Many thanks in advance
> > > Andy McDonald
> > > MCSA
> >
> >
> >
I have set up log shipping between two SQL 2000 servers, with no issues. A
job runs on the Primary server to reindex the databases. At this point Log
Shipping Fails, the error massage says that the servers are out of sync.
The log file shows the error
The log in this backup set begins at LSN 202000000017000001, which is too
late to apply to the database. An earlier log backup that includes LSN
20200000
0016800001 can be restored.
Is there a way to reindex the database that does not affect log shipping?
Many thanks in advance
Andy McDonald
MCSA"Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> Can anyone help.
> I have set up log shipping between two SQL 2000 servers, with no issues. A
> job runs on the Primary server to reindex the databases. At this point Log
> Shipping Fails, the error massage says that the servers are out of sync.
> The log file shows the error
> The log in this backup set begins at LSN 202000000017000001, which is too
> late to apply to the database. An earlier log backup that includes LSN
> 20200000
> 0016800001 can be restored.
> Is there a way to reindex the database that does not affect log shipping?
It sounds more like somehow you're losing a log backup somewhere along the
line.
reindexing doesn't (or at least shouldn't) affect log-shipping (having just
done this lately).
> Many thanks in advance
> Andy McDonald
> MCSA|||Greg
The data base is a large one, (about 25 GB) dont know if this helps. but we
only receive the error when re-indexing takes place.
Andy McDonald
MCSA
"Greg D. Moore (Strider)" wrote:
> "Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
> news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> > Can anyone help.
> >
> > I have set up log shipping between two SQL 2000 servers, with no issues. A
> > job runs on the Primary server to reindex the databases. At this point Log
> > Shipping Fails, the error massage says that the servers are out of sync.
> >
> > The log file shows the error
> > The log in this backup set begins at LSN 202000000017000001, which is too
> > late to apply to the database. An earlier log backup that includes LSN
> > 20200000
> > 0016800001 can be restored.
> >
> > Is there a way to reindex the database that does not affect log shipping?
> It sounds more like somehow you're losing a log backup somewhere along the
> line.
> reindexing doesn't (or at least shouldn't) affect log-shipping (having just
> done this lately).
>
> >
> > Many thanks in advance
> > Andy McDonald
> > MCSA
>
>|||Are you 100% certain that you don't set the database to simple recovery mode during reindex?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...
> Greg
> The data base is a large one, (about 25 GB) dont know if this helps. but we
> only receive the error when re-indexing takes place.
> Andy McDonald
> MCSA
> "Greg D. Moore (Strider)" wrote:
>> "Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
>> news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
>> > Can anyone help.
>> >
>> > I have set up log shipping between two SQL 2000 servers, with no issues. A
>> > job runs on the Primary server to reindex the databases. At this point Log
>> > Shipping Fails, the error massage says that the servers are out of sync.
>> >
>> > The log file shows the error
>> > The log in this backup set begins at LSN 202000000017000001, which is too
>> > late to apply to the database. An earlier log backup that includes LSN
>> > 20200000
>> > 0016800001 can be restored.
>> >
>> > Is there a way to reindex the database that does not affect log shipping?
>> It sounds more like somehow you're losing a log backup somewhere along the
>> line.
>> reindexing doesn't (or at least shouldn't) affect log-shipping (having just
>> done this lately).
>>
>> >
>> > Many thanks in advance
>> > Andy McDonald
>> > MCSA
>>|||Hello
Yes I am sure.
"Tibor Karaszi" wrote:
> Are you 100% certain that you don't set the database to simple recovery mode during reindex?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
> news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...
> > Greg
> >
> > The data base is a large one, (about 25 GB) dont know if this helps. but we
> > only receive the error when re-indexing takes place.
> >
> > Andy McDonald
> > MCSA
> >
> > "Greg D. Moore (Strider)" wrote:
> >
> >>
> >> "Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
> >> news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> >> > Can anyone help.
> >> >
> >> > I have set up log shipping between two SQL 2000 servers, with no issues. A
> >> > job runs on the Primary server to reindex the databases. At this point Log
> >> > Shipping Fails, the error massage says that the servers are out of sync.
> >> >
> >> > The log file shows the error
> >> > The log in this backup set begins at LSN 202000000017000001, which is too
> >> > late to apply to the database. An earlier log backup that includes LSN
> >> > 20200000
> >> > 0016800001 can be restored.
> >> >
> >> > Is there a way to reindex the database that does not affect log shipping?
> >>
> >> It sounds more like somehow you're losing a log backup somewhere along the
> >> line.
> >>
> >> reindexing doesn't (or at least shouldn't) affect log-shipping (having just
> >> done this lately).
> >>
> >>
> >> >
> >> > Many thanks in advance
> >> > Andy McDonald
> >> > MCSA
> >>
> >>
> >>
>
>|||"Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...
> Greg
> The data base is a large one, (about 25 GB) dont know if this helps. but
we
> only receive the error when re-indexing takes place.
Very weird. Really sounds like a log is benig lost somewhere along the
line.
Which I had a better answer.
> Andy McDonald
> MCSA
> "Greg D. Moore (Strider)" wrote:
> >
> > "Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in
message
> > news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> > > Can anyone help.
> > >
> > > I have set up log shipping between two SQL 2000 servers, with no
issues. A
> > > job runs on the Primary server to reindex the databases. At this point
Log
> > > Shipping Fails, the error massage says that the servers are out of
sync.
> > >
> > > The log file shows the error
> > > The log in this backup set begins at LSN 202000000017000001, which is
too
> > > late to apply to the database. An earlier log backup that includes LSN
> > > 20200000
> > > 0016800001 can be restored.
> > >
> > > Is there a way to reindex the database that does not affect log
shipping?
> >
> > It sounds more like somehow you're losing a log backup somewhere along
the
> > line.
> >
> > reindexing doesn't (or at least shouldn't) affect log-shipping (having
just
> > done this lately).
> >
> >
> > >
> > > Many thanks in advance
> > > Andy McDonald
> > > MCSA
> >
> >
> >
Friday, February 24, 2012
Log Shipping and Backup Jobs = Conflict?
Can we run log shipping alongside our backup plan?
The concern is that the full backup runs overnight and
then diff backups run during the day and at the same time
log shipping occurs every 30 mins - don't both jobs
require the resetting of the archive bit specifying when
the last trn backup took place?
Many thanks in advance for the help, Rgds, Simon.As long as the ONLY log backups are done through log shipping you should be
fine...Whole and differential backups do NOT affect the transaction log...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:2494501c46018$ae3cd380$a601280a@.phx
.gbl...
> Can we run log shipping alongside our backup plan?
> The concern is that the full backup runs overnight and
> then diff backups run during the day and at the same time
> log shipping occurs every 30 mins - don't both jobs
> require the resetting of the archive bit specifying when
> the last trn backup took place?
> Many thanks in advance for the help, Rgds, Simon.
The concern is that the full backup runs overnight and
then diff backups run during the day and at the same time
log shipping occurs every 30 mins - don't both jobs
require the resetting of the archive bit specifying when
the last trn backup took place?
Many thanks in advance for the help, Rgds, Simon.As long as the ONLY log backups are done through log shipping you should be
fine...Whole and differential backups do NOT affect the transaction log...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:2494501c46018$ae3cd380$a601280a@.phx
.gbl...
> Can we run log shipping alongside our backup plan?
> The concern is that the full backup runs overnight and
> then diff backups run during the day and at the same time
> log shipping occurs every 30 mins - don't both jobs
> require the resetting of the archive bit specifying when
> the last trn backup took place?
> Many thanks in advance for the help, Rgds, Simon.
Log Shipping and Backup Jobs = Conflict?
Can we run log shipping alongside our backup plan?
The concern is that the full backup runs overnight and
then diff backups run during the day and at the same time
log shipping occurs every 30 mins - don't both jobs
require the resetting of the archive bit specifying when
the last trn backup took place?
Many thanks in advance for the help, Rgds, Simon.
As long as the ONLY log backups are done through log shipping you should be
fine...Whole and differential backups do NOT affect the transaction log...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:2494501c46018$ae3cd380$a601280a@.phx.gbl...
> Can we run log shipping alongside our backup plan?
> The concern is that the full backup runs overnight and
> then diff backups run during the day and at the same time
> log shipping occurs every 30 mins - don't both jobs
> require the resetting of the archive bit specifying when
> the last trn backup took place?
> Many thanks in advance for the help, Rgds, Simon.
The concern is that the full backup runs overnight and
then diff backups run during the day and at the same time
log shipping occurs every 30 mins - don't both jobs
require the resetting of the archive bit specifying when
the last trn backup took place?
Many thanks in advance for the help, Rgds, Simon.
As long as the ONLY log backups are done through log shipping you should be
fine...Whole and differential backups do NOT affect the transaction log...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:2494501c46018$ae3cd380$a601280a@.phx.gbl...
> Can we run log shipping alongside our backup plan?
> The concern is that the full backup runs overnight and
> then diff backups run during the day and at the same time
> log shipping occurs every 30 mins - don't both jobs
> require the resetting of the archive bit specifying when
> the last trn backup took place?
> Many thanks in advance for the help, Rgds, Simon.
Log Shipping and Backup Jobs = Conflict?
Can we run log shipping alongside our backup plan?
The concern is that the full backup runs overnight and
then diff backups run during the day and at the same time
log shipping occurs every 30 mins - don't both jobs
require the resetting of the archive bit specifying when
the last trn backup took place?
Many thanks in advance for the help, Rgds, Simon.As long as the ONLY log backups are done through log shipping you should be
fine...Whole and differential backups do NOT affect the transaction log...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:2494501c46018$ae3cd380$a601280a@.phx.gbl...
> Can we run log shipping alongside our backup plan?
> The concern is that the full backup runs overnight and
> then diff backups run during the day and at the same time
> log shipping occurs every 30 mins - don't both jobs
> require the resetting of the archive bit specifying when
> the last trn backup took place?
> Many thanks in advance for the help, Rgds, Simon.
The concern is that the full backup runs overnight and
then diff backups run during the day and at the same time
log shipping occurs every 30 mins - don't both jobs
require the resetting of the archive bit specifying when
the last trn backup took place?
Many thanks in advance for the help, Rgds, Simon.As long as the ONLY log backups are done through log shipping you should be
fine...Whole and differential backups do NOT affect the transaction log...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:2494501c46018$ae3cd380$a601280a@.phx.gbl...
> Can we run log shipping alongside our backup plan?
> The concern is that the full backup runs overnight and
> then diff backups run during the day and at the same time
> log shipping occurs every 30 mins - don't both jobs
> require the resetting of the archive bit specifying when
> the last trn backup took place?
> Many thanks in advance for the help, Rgds, Simon.
Monday, February 20, 2012
log shipping + full backup 26 hours
Hi All,
My normal job that runs on the primary server that does
the transaction log backup to the log shipping directory
normally takes anywhere from 30 seconds to 5 minutes.
However when doing a full backup the log shipping job and
the full backup ran for 26 hours. Which normally runs for
about 2 - 2 1/2 hours. What's going on. Can you not do a
full backup and have log shipping?
Regards,
Bryan
Did you check to see if anything was blocking the backup while you were
doing it?
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:010e01c483f6$fe8ca480$a301280a@.phx.gbl...
> Hi All,
> My normal job that runs on the primary server that does
> the transaction log backup to the log shipping directory
> normally takes anywhere from 30 seconds to 5 minutes.
> However when doing a full backup the log shipping job and
> the full backup ran for 26 hours. Which normally runs for
> about 2 - 2 1/2 hours. What's going on. Can you not do a
> full backup and have log shipping?
> Regards,
> Bryan
|||Bryan, when you do a full backup, your T-log backup will be in a waiting
state. So if ur backup runs for 2 hrs then your T-log backups will be
blocked for that entire 2 hours.. As far as 26 hrs, that remains a mystery..
Havent really seen any factors blocking a backup process. Do you backup
locally or over the network ? If its over the n/w then yes there could have
been some n/w glitch causing it to run that long.. But keep investigating..
Look at the error logs and see if theres anything thats obvious.
"ChrisR" <chris@.noemail.com> wrote in message
news:e$bZJcAhEHA.904@.TK2MSFTNGP09.phx.gbl...
> Did you check to see if anything was blocking the backup while you were
> doing it?
>
> "bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
> news:010e01c483f6$fe8ca480$a301280a@.phx.gbl...
>
My normal job that runs on the primary server that does
the transaction log backup to the log shipping directory
normally takes anywhere from 30 seconds to 5 minutes.
However when doing a full backup the log shipping job and
the full backup ran for 26 hours. Which normally runs for
about 2 - 2 1/2 hours. What's going on. Can you not do a
full backup and have log shipping?
Regards,
Bryan
Did you check to see if anything was blocking the backup while you were
doing it?
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:010e01c483f6$fe8ca480$a301280a@.phx.gbl...
> Hi All,
> My normal job that runs on the primary server that does
> the transaction log backup to the log shipping directory
> normally takes anywhere from 30 seconds to 5 minutes.
> However when doing a full backup the log shipping job and
> the full backup ran for 26 hours. Which normally runs for
> about 2 - 2 1/2 hours. What's going on. Can you not do a
> full backup and have log shipping?
> Regards,
> Bryan
|||Bryan, when you do a full backup, your T-log backup will be in a waiting
state. So if ur backup runs for 2 hrs then your T-log backups will be
blocked for that entire 2 hours.. As far as 26 hrs, that remains a mystery..
Havent really seen any factors blocking a backup process. Do you backup
locally or over the network ? If its over the n/w then yes there could have
been some n/w glitch causing it to run that long.. But keep investigating..
Look at the error logs and see if theres anything thats obvious.
"ChrisR" <chris@.noemail.com> wrote in message
news:e$bZJcAhEHA.904@.TK2MSFTNGP09.phx.gbl...
> Did you check to see if anything was blocking the backup while you were
> doing it?
>
> "bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
> news:010e01c483f6$fe8ca480$a301280a@.phx.gbl...
>
Subscribe to:
Posts (Atom)