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?



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

  • 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 '14 at 17:57
  • 1
    Good point and definitely something to factor in. Added this to the post. – Kris Gruttemeyer Jul 31 '14 at 17:59
  • Can I just switch the existing NORECOVERY to STANDBY on the fly and all will be OK? – Jonesome Reinstate Monica Jul 31 '14 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 '14 at 20:47
  • I am using STANDBY mode but users disconnect every 15 minute. If I uncheck drop users then restore fails. However I dont want intrept report users. I dont want to use avaibility group because i have standart edition. is there equel to alwalys on avaibility - read only secondary. – MahmutHAKTAN Feb 27 '15 at 13:10

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.

  • Thanks for updating and clarifying that EE is required for AG. – SQLRNNR Jul 31 '14 at 19:40
  • No worries! Welcome to DBA.SE! – swasheck Jul 31 '14 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) – Jonesome Reinstate Monica Jul 31 '14 at 20:37

I know this is an old post, but I thought I'd chime in in case someone else has this question. If all you want to do is avoid disrupting the log chain, simply disable the SQL Agent job that is performing the restore. The .trn files will back up in your file system, and you will have some latency (depending on how long you have the job disabled), but that will free up the database for you to do whatever you need to. When you enable the job again, files will process in order from where they left off.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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