From standby to read only AND Read only to Standby/Readonly

Replication
1

HI Experts
How can i switch the database from standby to read only AND Read only to Standby/Readonly mode?

2

Standby to read only I reckon is two steps:

RESTORE DATABASE MyDatabaseName
   WITH   RECOVERY

and then set it to Read Only, probably like this

ALTER DATABASE MyDatabaseName SET READ_ONLY WITH ROLLBACK IMMEDIATE

but probably a window-of-opportunity between those two commands?

Read Only to Standby - can't be done (AFAIK). Restore from backup again, in Standby mode.

1 Like
3

Thank you Kristen !
informative too...

4

Standby is already read-only...I think what you are wanting to do is put the database into read-only (standby) and then back to recovering (so you can apply logs).

See this: https://www.mssqltips.com/sqlservertip/3574/change-sql-server-log-shipped-database-from-restoring-to-standby-readonly/

5

If the database is restored using STANDBY then can;t you just do more log restores (also in STANDBY)?

Its a while since I've done it, but that's my recollection. I don't remember having to take the DB out of Standby, and then back into Standby to do more restores (and have opportunity to Query the DB too)