I just started at a new company and I'm responsible for the reporting sql server. Their process to keep the reporting db in sync with the production db is to constantly keep the production db in Standby/Read-Only mode so that they can apply a transaction log that comes each night from the production db.
What I need is to grab a copy of the current reporting db so that I can put it on a test server, unfortunately I can't take a full backup of it in the standby/read-only state.
I realize that I can get it out of that mode by doing "restore database 'dbname' with recovery". However after I do that, can I get the db back into the Standby/Read-Only mode to continue applying the nightly transaction log?
No. You can't return a database to Standby/Read-Only mode after using the restore WITH RECOVERY option.
When a database is in recovery mode, there may be incomplete transactions in the database, which means the database is not in a ready state. Either, you restore the next transaction log backup, which will complete the transactions, or you do the restore 'WITH RECOVERY', which will roll-back open transactions. Once, the open transactions are rolled-back, the next transaction-log backup can't be restored, because the database is no longer in a state expected for the transaction-log restore.
EDIT:
An alternative is to restore a copy of the most recent production full backup and restore the appropriate transaction-log backups to that database.