CODEWITHSUNDEEP
sql-serversnapshot
dance2die
dance2die

Reputation: 36985

How does a SQL Server Snapshot retrieve deleted record from original database?

I have created a snapshot, AdventureWorksSS. Deleted a record after creating a snapshot.

When I query from AdventureWorksSS, deleted record shows up.

Where is that deleted record stored and how does Snapshot still retrieve the deleted record?

As far as I understand, snapshot is a pointer to the original database and fetches data only necessary. Do deleted data get inserted into snapshots automatically?

Upvotes: 0

Views: 748

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294447

Snapshot isolation level retrieves the previous row from the Version Store. The Version Store keeps track of all changes that affect a versioned row in tempdb.

Database snapshot does a copy on write of the affected pages and uses the OS sparse files feature.

Upvotes: 1

KM.
KM.

Reputation: 103697

this explaines it: http://msdn.microsoft.com/en-us/library/ms187054.aspx

Upvotes: 1

Related Questions