If an SQL Server 2005 database has multiple snapshots, do the first changes get written to all snapshots or only to the most recent? Good question, let's make the answer:
The following scenario is based on this sql-script. It assumes you have the AdventureWorks and your database engine uses C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Be careful, the script changes some data in AdventureWorks, so some examples from books-online may behave unexpected after this demo.
The scenario is in four stages;
- The first snapshot is created and a query displays the sizes of the snapshot file on disk (BytesOnDisk) and the internal size (BytesWritten).
- Person.Contact is updated, the Firstname and LastName for all records are swapped, practically resulting in the whole table being written to the snapshot before the update. Right after this update, the second snapshot is created and the query again shows the information about the (now 2) snapshots. The first snapshot holding the Person.Contact table before the update, the second being still empty.
- Person.Address is updated, AddressLine1 and AddressLine2 are swapped (and NULLs for AddressLine2 are set to empty strings). The query following this update shows both snapshots have grown, proving all snapshots get written (and we have the answer!!!).
- To show a snapshot only processes the changes once since it was created, the FirstName and LastName columns in Person.Contact are swapped again. This time, only the second snapshot gets written to (you may notice one or two data pages (8192 bytes) being added to the first snapshot, an insignificant change compared to previous changes).