October 29, 2007 at 5:24 am
Hi,
I need to know how insert (Not Update) operation will work in database snapshot and how that will restore back ??
Any body who know this???
October 30, 2007 at 4:18 am
You can't insert into a snapshot. All the snapshot does is "tag" the database at the time the snapshot is taken. Then, everytime something in the database changes, the original "tagged" data moves into the snapshot. Inserts will not show up in the snapshot. They will, however, be in the original database.
BTW, "Tag" is probably a poor way to phrase this, but I don't remember the technical jargon for what the engine actually does to mark the original records.
If you restore a database from a snapshot, then it will replace all the changed data with the original data and should wipe out all the inserted data. It's a lot like restoring a database from a backup. The only difference is that this restore can be a lot faster because you rarely have as much data to be fixed in a snapshot situation.
The best way to verify what I'm telling you, though, is to test this. Create yourself a mini-db with one or two tables, insert some records, snapshot it, insert more records, then restore the snapshot.
October 30, 2007 at 6:52 am
As Brandie stated:
The Snapshot is read-only. When you say that you need to understand how the "Insert" works but not the "update", it appears you don't understand how either works.
Another way to think of it is like a picture (i.e. the snapshot reference 🙂 ). Now as you update the ORIGIONAL database, any change that you make to it the OLD data gets copied to the snapshot. I suspect that this is where you are getting confused. Don't think of it as tables/rows. But instead as disk blocks. As long as the disk blocks don't change in the image, the snapshot is empty. If a block that "is part of the origional picture" is changed, the old block is copied to the snapshot" So if the origional db is "extended" then that is actually ignored by the snapshot.
The restore operation is just that. The old blocks are copied back over the DB. As for inserted data, it is removed/erased. I can't say if the datafile is shrunk or just marked as available. Someone else might know that.
October 30, 2007 at 7:09 am
Hi,
Thanks a lot for this information, now i understud how it works. One more doubt is will data in snapshot file will be emptied after completion of snapshot restoration or will it remain in snapshot???
October 30, 2007 at 7:24 am
Honestly, I'm not sure if the snapshot "empties" or is deleted completely once you restore. The best way for you to find out the answer, though, is to test it as I suggested earlier. @=)
October 30, 2007 at 7:32 am
Not sure you are going to be able to tell. The files that are created are sparse files, so they appear to be the same size as the actual DB, but don't actually occupy space until they are written to. So the second that they are created, they may look to be 100G, but in reality they are completely empty.
From my experience restoring them. If I restore from an old snapshot it takes longer than a fresh one. Even if I restore it 5 minutes after I restored it the first time. So I will often after doing a restore from an old snapshot, just drop and re-create a new snapshot. 🙂 That works much faster.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply