How do SQL Server Snapshots work?

All topics about maintaining SQL Server from a DBA perspective.
Post Reply
Posts: 6
Joined: Mon Jun 24, 2019 1:38 pm

Mon Jun 24, 2019 1:50 pm

SQL Servers have been growing in business exponentially since they were created in the 1970's. First created by IBM in San Jose, California, SQL did not become a standard computer language until the mid-1980s. Since that time, during its growth, there have been many upgrades and changes to the SQL relational database management systems, one such upgrade is the SQL server snapshot.

A SQL server snapshot is essentially what it sounds like, it is a snapshot of the system that is stored and used as a primary system. When a snapshot occurs, the information is stored in the main drive and then further information created after the snapshot occurs is stored in a new data is stored in what is called a sparse file. A snapshot creates a backup of the system at that time, and uses 90% less drive space than backing up the system does. This information is stored for as long as the user requires it, and can be dropped when no longer needed.

The purposes of a SQL server snapshot vary depending on the needs of the user. In some cases, it is used simply to back up the system without having to use so much drive space during the working day. In other situations, it is created when data is perceived as "ideal" and any new data needs to be compared to the data that was previously acquired. This information can be stored indefinitely and new snapshots can be created at any point to continue tracking specific data. When saving the user should simply take the name of the data and add a time stamp to it. This will ensure that there is no confusion when accessing the information as to what its purpose is.

When a SQL server snapshot is dropped, the new information and the previous information join to form a completely new set of information. The information from the snapshot is no longer available and cannot be referenced for projects or projections. When the decision is made to drop it, it is vital that the project manager or user understands that it will become irretrievable.

While some business may never drop a SQL server snapshot, others may find that they simply want to test their data against studies that may have been conducted or to simply experiment with different ways of looking at their data. Once the information is retrieved the SQL snapshot is dropped in favor of new testing, or they return to their regular data consumption. Companies that track long-term data may never drop a snapshot, and the SQL servers are designed to function either way.
Post Reply

Social Media