Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
How Does It Work?
DB snapshots use a very unique and efficient technique to take an "online" backup of the source database. When you create a DB snapshot, a "ghost copy" of each data file of the source database is created. The files do not contain any data at all, though. Basically, the DB snapshot is simply an empty shell, to start with. Any queries on the snapshot use the data from the source database (see Figure 1).
)
But an interesting thing happens when data in the main database changes. The database pages that are about to change are copied to the snapshot. Then the pages are updated in the main database. So the snapshot now contains the "original" copy of the changed data (see Figure 2). Any query on the snapshot—which accesses the data in the changed pages in the main database—will use the locally copied pages. Hence, the data seen by the query will be from the time when the snapshot was created.
)
Obviously, the snapshot creation is a simple task, as no data copy occurs. Hence the operation is very fast. Also, the snapshot would use very little disk space to start with. As such, you can afford to have many snapshots at a time. But be aware that the snapshot might grow very fast, depending on how fast data changes in the main database. Also, there’s a definite processing overhead in copying the changing pages to the snapshot.
The snapshot creation command is shown below. It’s very similar to the database creation script, except that the "names" (but not the physical filenames) have to match the original database’s files:
CREATE DATABASE [TESTSS] ON ( NAME = N’TestPrimary’, FILENAME = N’C:\SnapshotData\TestSSPrimary.mdf’ ) AS SNAPSHOT OF [Test]
In this example, I’m creating a snapshot called TESTSS for the database TEST. The TEST database contains just one file called TestPrimary, which is matched in the snapshot creation script.
The snapshot’s files are created as NTFS "sparse" files. Figure 3 shows the file properties dialog box for a snapshot database file. The file size is reported as 275MB, but the file itself takes up only 128KB on the disk; 275MB is the reserved size, and matches the size of the original file from the main database at the time of snapshot creation.
)