I Thought that I can change the DatabaseLS_Main from Standby mode to bring it online, take the snapshot and bring it back to STANDBY mode...is it right to do ?
What you are telling is not possible using logshipping.
There are limitations of database snapshots:
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.
There are some options:
- If your database is not that big, you can do a snapshot replication, if you really want a snapshot of your database as opposed to doing a backup/restore.
- If you want a near-to-realtime data on some secondary server, then transactional replication would be a better option.
- Alternatively, you can use database mirroring. It allows database snapshot on the mirrored server but the database must be in the synchronized mirroring state.
- Now that SQL Server 2012 and 2014 are out with AlwaysON, you can consider that as well if you want a read-only copy of your database.
Note that database snapshots as well as AlwaysON are Enterprise features :-)