Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know it is normal for the target db to be in "restoring" state

I also would like to do a few queries as a sanity check that the data is all up to date.

Can I take it to a read only, non-restoring state, temporarily, to check the data, and then put it back to restoring, without breaking the whole restore chain?

thanks!

share|improve this question

3 Answers 3

up vote 4 down vote accepted

I would suggest configuring the log shipping to STANDBY mode instead of NORECOVERY. This will allow you to query the secondary db for any reporting, etc.

When you use NORECOVERY mode, the secondary database will not allow any users to access it, so the database does not have to worry about uncommitted transactions. The log can just be restored as it currently sits without worrying about any users accessing/changing things.

When you use STANDBY mode, the database restores in NORECOVERY mode, then looks at and rolls any open transactions. Once that is complete, users can access the database and it will be in read-only mode. When the next log is restored, the database disconnects all users and repeats the rollback/forward process and then, again, places the database in read-only mode. Lather, rinse, repeat.

Think of it as a transaction log restore. When you restore each transaction log, you leave the DB in NORECOVERY so that you can continue to append any additional log files until you have restored to the point you require. Then, you set it to RECOVERY and the database is able to be read. Make sense?

@DenisT brought up a good point that the 'disconnect all users' option must be checked in order for all users to get booted. Otherwise, the log restore will fail.

Here's a great article on it http://askmesql.blogspot.se/2011/01/log-shipping-norecovery-vs-standby-mode.html

share|improve this answer
1  
the restore disconnects the users (drops all DB connections) only if you check this option in the configuration. Otherwise, the restore will fail. –  DenisT Jul 31 at 17:57
1  
Good point and definitely something to factor in. Added this to the post. –  Kris Gruttemeyer Jul 31 at 17:59
    
Can I just switch the existing NORECOVERY to STANDBY on the fly and all will be OK? –  samsmith Jul 31 at 20:39
1  
That's a very good question. I would THINK that it would be okay and that the change will take effect next time the log ship occurs. However, I've never set one up in NORECOVERY and tried to switch. All of mine are in standby. Maybe someone else can speak to that point or maybe has attempted it before? –  Kris Gruttemeyer Jul 31 at 20:47

Can I take it to a read only, non-restoring state, temporarily, to check the data, and then put it back to restoring, without breaking the whole restore chain?

The key to that question is "breaking the restore chain."

If you want to query and verify numbers, then standby mode in logshipping is your best option.

If you have SQL Server 2012 Enterprise Edition available to you, then you can forego logshipping in favor of Availability Groups with a readonly subscriber. This would also achieve your requirements.

share|improve this answer
    
Thanks for updating and clarifying that EE is required for AG. –  SQLRNNR Jul 31 at 19:40
    
No worries! Welcome to DBA.SE! –  swasheck Jul 31 at 19:42
    
Availability groups is something I would love (and pay for), but the dependence on Windows domains and OS related dependencies makes availability groups unfeasible for us. We will be on log shipping forever (at least until avail groups can be managed purely within sql, like setting up mirroring... We don't use mirroring because it does not support one-->many replication) –  samsmith Jul 31 at 20:37

Can I take it to a read only, non-restoring state, temporarily, to check the data, and then put it back to restoring, without breaking the whole restore chain?

No you cannot do this without breaking Logshipping. You cannot convert recovering database to read only without bringing it online and moment you bring it online Logshipping breaks.

Instead configure logshipping with secondary in standby mode. Note that when you check the box disconnect user when restoring database while configuring Logshipping it would disconnect all users running query on secondary DB for restore. This is one of the drawback.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.