Basically, I’m using a custom log shipping method on a sql 2012, where the transaction files are manually copied to an offsite location. The files are then restored using something similar:
RESTORE LOG xyz FROM MyAdvWorks_log2 WITH STANDBY = 'c:undo.bak'
I would really like to be able to set that stand-by database briefly to Active, do various stuff (not just read) and then rollback/discard those changes and allow the restore to continue.
Is that even possible?
If not, can I copy the stand-by server and set it to active (and then when I’m done, delete it)?
(since this is longer than a comment .. so posting this as an answer)
In sql server enterprise edition, you can leverage database snapshots.
If I understand your question, you want to do some changes on a standy (not live) copy of your database and then discard those ?
If that is true, then you can create a database snapshot of your main database using :
CREATE DATABASE snapshotDBName... AS SNAPSHOT OF yourPRODDBNAME
Then do the modifications – Inserts / Updates / Deletes using regular TSQL and once you are done with your modifications, you can drop the snapshot using
DROP DATABASE snapshotDBName
You can read more on SQL Server Database Snapshots
Note: If this is not what you want, then I will delete this answer .. which I wrote assuming stuff.
Given your situation and the information presented here, consider the following…
If disk space allows for it, configure your custom log shipping to have 2 databases on the Secondary server that are both constantly restoring logs from the Primary. The purpose of database A would be for Disaster Recovery purposes and should be left alone so you never endanger your ability to use it in an emergency situation. Database B would then be used for your purposes described above, where you can bring it online when needed and do as you see fit. Once finished, restore the necessary full database backup in NORECOVERY mode to get database B back to restoring logs again.
The goal is really to separate out your DR needs from your other needs as the two can be conflicting (as you’ve seen). Without knowing the details of your custom log shipping process it’s not possible to advise you on how to go about this, but hopefully this helps conceptually with what could be done.