I was working on a requirement where we need to read secondary database in logshipping which is in Restoring state.
There are two possible options:
1. Create Database Snapshot of logshipping database which is in Restoring state
2. Change state of secondary database to Read-only or Standby
When I tried to create snapshot of secondary database, I got error:
Msg 1822, Level 16, State 1, Line 1
The database must be online to have a database snapshot.
Puzzled on why we get this error because in Database Mirroring, we have option to create database snapshot on Mirror database. After a little research, got this documentation:
In a log shipping configuration, database snapshots can be created only on the primary database, not on a secondary database. If you switch roles between the primary server instance and a secondary server instance, you must drop all the database snapshots before you can set the primary database up as a secondary database.
http://msdn.microsoft.com/en-us/library/ms189940.aspx
So we went a-head with changing secondary database by running command:
RESTORE DATABASE [Demo] WITH STANDBY = N'C:\Demo4.BAK'
but problem here was the next time the restore job runs, it changes the secondary database again to "Restoring..." :)
