Using snapshots for database structure recovery | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Using snapshots for database structure recovery

It sounds like snapshot is a good solution for database structure recovery, something we now just keep in VSS in case the db goes down. I mean recovering logins, jobs, stored procedures, schema, etc. Is anyone using them for this purpose?
It is a good tool for every DBA to provide High_availability on their environment, but it has to be used carefully otherwise there will be a risk of losing the schedule of backups. We are still testing the DB snapshots scenario for our Production support purpose, and keep in mind about few things you cannot perform in this feature: – You cannot grant a new user access to the data in a snapshot.
– You can not backup the snapshots so if you have to restore your source database your reporting snapshots are lost.
– Where data pages have not changed you will be accessing the source database file which may cause contention at the file level since both the source database and the snapshot will be accessing the same MDF.
– Full text indexes are not available on snapshots so if you require full text searching for your reporting then snapshots are not an option. More you can find on the Books online in this regard. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks Satya, – You cannot grant a new user access to the data in a snapshot.
Yes, you would need to do this in the source first, then recreate the snapshot, true? – You can not backup the snapshots so if you have to restore your source database your reporting snapshots are lost.
I think I would have to delete the snapshots before restoring the database, since they are tied together. And you’re right, there’s this issue of increased IO.
Yes on the first point and good to go on second point.
Yes there will be interval of spikes between the snapshot operations, but PERFMON *SYSMON) can help to get what is happening. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>