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...
>
>