July 23, 2010 at 11:52 am
Despite the reading I've done I'm still unclear about the full benefits of restoring a database from a db snapshot. Apparantly there are limitations, and these I am not clear on.
If the under-lying table are still intact when a database is restored from a snapshot, then does one have a fully operational database whose metadata as it pertains to msdb and model databases is intact as well?
When is a database snapshot 'no good'?
July 23, 2010 at 12:26 pm
hxkresl (7/23/2010)
Despite the reading I've done I'm still unclear about the full benefits of restoring a database from a db snapshot.
One word: speed.
A snapshot is taken by splitting a mirrored storage device - like EMC BCV.
SQL Server snapshots a operationally identical to standard SQL Server backups but you do not have to actually copy the data, mirror copy is splitted and presented as a snapshot.
Picture this scenario, you have a 1.5 TB production database and you have to fully refresh a production size UAT database - would you like to take a conventional full backup and restore it or just take a snapshot which will be done in under a minute and use it as your UAT database?
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 23, 2010 at 12:39 pm
OK, got the part about speed but I'm tripping on "read-only". That doesn't sound fully functional.
July 23, 2010 at 12:54 pm
Could you clarify what it is you are asking? You can use a DB Snapshot to revert a database to the point in time that the snapshot was taken.
July 23, 2010 at 12:58 pm
hxkresl (7/23/2010)
OK, got the part about speed but I'm tripping on "read-only". That doesn't sound fully functional.
It is read-only... until you restore it 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 23, 2010 at 1:10 pm
Yes for sure. From my reading I am made to understand that a restored DB snapshot is 'read-only'. Iin the same breath of a page I read that a DB snapshot can be used to fully restore a database to the point in time that the snapshot was taken.
What I can't reconcile is 'fully functional' and 'read-only'. If I need a database that is fully functional I need to be able to write to it. A read-only database would not allow such a thing.
If I have the choice between
1. restoring a FULL, Diff, and t-log back-up with no tail of a log (ie. lose some data)
OR
2. restoring that same database's snapshot which I took just prior to the action that corrupted the database,
then which path do I take? Does it depend?
July 23, 2010 at 1:12 pm
PaulB-TheOneAndOnly (7/23/2010)
hxkresl (7/23/2010)
OK, got the part about speed but I'm tripping on "read-only". That doesn't sound fully functional.It is read-only... until you restore it 🙂
Well, cool, Paul, you posted while I was writing my response. So you are saying 'revert', THEN 'restore' will fully recover the db to a read and write database! Great. So, this is the answer to my post I believe.
July 23, 2010 at 1:22 pm
I think I'm getting a little lost. You mention something about restoring a snapshot. I don't believe this is possible as you can't backup a snapshot.
What may help is if you could provide an example of what you are trying to figure out.
From my knowledge, if you take a snapshot of a database at 8:00 AM, at 9:00 AM you could revert the database back to its original state as of 8:00 AM using the snapshot. You would lose all updates/inserts/deletes done after the snapshot. Once reverted, the databasse would be operational.
Restoring from a full, differential, and t-log backups (with no tail-log backup) would store the database operational to the end of the last log backup, losing updates/inserts/deletes that would have been in the tail-log backup.
July 23, 2010 at 1:26 pm
PaulB-TheOneAndOnly (7/23/2010)
A snapshot is taken by splitting a mirrored storage device - like EMC BCV.SQL Server snapshots a operationally identical to standard SQL Server backups but you do not have to actually copy the data, mirror copy is splitted and presented as a snapshot.
That's a SAN snapshot, an actual copy of the data and log files of a database. I believe the OP is talking about a database snapshot, a sparse file that only contains the original version of pages modified since the snapshot was created.
A database snapshot is not a separate database, it's just a portion (the old version of the part that has changed). It cannot be backed up. It cannot be moved to a different server and if the source database of the snapshot disappears, the snapshot becomes invalid. It's use is as a read-only copy of the database as it was at the time the snapshot was created.
The only 'restore' that you can do from a snapshot is a revert to the time that the snapshot was created, and the source DB must be available and online for that to be possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2010 at 1:33 pm
GilaMonster (7/23/2010)
PaulB-TheOneAndOnly (7/23/2010)
A snapshot is taken by splitting a mirrored storage device - like EMC BCV.SQL Server snapshots a operationally identical to standard SQL Server backups but you do not have to actually copy the data, mirror copy is splitted and presented as a snapshot.
That's a SAN snapshot, an actual copy of the data and log files of a database. I believe the OP is talking about a database snapshot, a sparse file that only contains the original version of pages modified since the snapshot was created.
A database snapshot is not a separate database, it's just a portion (the old version of the part that has changed). It cannot be backed up. It cannot be moved to a different server and if the source database of the snapshot disappears, the snapshot becomes invalid. It's use is as a read-only copy of the database as it was at the time the snapshot was created.
The only 'restore' that you can do from a snapshot is a revert to the time that the snapshot was created, and the source DB must be available and online for that to be possible.
I had a feeling that PaulB was thinking SAN snapshot, not database snapshot, makes sense considering what he was saying.
July 23, 2010 at 1:41 pm
OP stands for me, the Outstanding Person, I believe 🙂
OK, and what if the source database is corrupted, ie. a virus attack that deleted tables and data, but not the policies, not the alerts, not the indexes, and not the filegroups to which the tables have been partitioned (scenario I am inventing), then a database snapshot will be able to restore a fully operational database?
July 23, 2010 at 1:46 pm
hxkresl (7/23/2010)
OP stands for me, the Outstanding Person, I believe 🙂OK, and what if the source database is corrupted, ie. a virus attack that deleted tables and data, but not the policies, not the alerts, not the indexes, and not the filegroups to which the tables have been partitioned (scenario I am inventing), then a database snapshot will be able to restore a fully operational database?
If your source database has been corrupted in this manner, a full restore of the database is probably best. It may be possible that a snapshot would work, but I'd be wary personally without have tested such a scenerio in a test environment with a copy of a production or test database.
July 23, 2010 at 1:53 pm
So, 'read-only' is the state that a snapshot database is in only until it's been RESTORED(*edit: er, REVERTED')! Who doesn't know that????? 🙂 Thanks.
** second edit: read on, below.
July 23, 2010 at 2:00 pm
Depends what you mean by 'corruption'
If you mean deleted rows, dropped tables, then yes, a DB snapshot will fix that.
If you take a DB snapshot at 8am, and something drops a table at 11:30 am, you can copy the data back from the snapshot (create table, insert ... select) or you can revert the entire database back to 8am.
If you mean the traditional definition of corruption - damaged pages that throw high severity errors when read, a DB snapshot is unlikely to help you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2010 at 2:03 pm
hxkresl (7/23/2010)
So, 'read-only' is the state that a snapshot database is in only until it's been RESTORED! Who doesn't know that????? 🙂 Thanks.
No, no, no, no, no.
A database snapshot is read only. Full stop, end of story.
What you can do with a database snapshot is revert the source DB to it. So you're essentially rolling back the source database to the point when the shapshot was created. The source database is read-write. The snapshot is still (and always) read-only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply