In today’s digital world, data availability and recovery are crucial for businesses. IT managers are now focusing on two important metrics: Recovery Point Objective (RPO) and Recovery Time Objective (RTO). RPO refers to the acceptable amount of data loss, while RTO refers to the acceptable amount of business downtime. Lowering RPO and RTO should be a key objective in any backup and recovery strategy.

Microsoft offers various data protective measures to minimize RPO and RTO. One of these measures is the use of database snapshots. In this article, we will explore the concept of database snapshots and how they can contribute to a robust backup and recovery strategy.

What are Database Snapshots?

Database snapshots are a feature in SQL Server that allow you to create a read-only, point-in-time copy of a database. They use NTFS sparse files with the Alternate Data Stream (ADS) mechanism. This mechanism copies the original data block to a sparse file before updating it, ensuring a consistent state for the snapshot.

Database snapshots operate at the data file level, so for each data file of a database, you need to associate a sparse file to create a successful snapshot. It’s important to note that the sparse file mechanism is supported in NTFS but not in FAT32.

Creating and Managing Database Snapshots

Creating and managing database snapshots is done through T-SQL commands, as SQL Server Management Studio does not support these operations. To create a snapshot database, you can use the following query:

CREATE DATABASE [sample_snap1]
ON (NAME=[sample], FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\sample_snap1')
AS SNAPSHOT OF [SAMPLE]

Once created, a snapshot database will appear in the object explorer like any other SQL Server database. You can compare the disk space usage of the original database and the snapshot using the following query:

SELECT BytesOnDisk FROM fn_virtualfilestats(DB_ID('sample_snap1'), FILE_ID('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\sample.snap1'))

It’s important to note that the size of the snapshot database will grow as the original database changes. Therefore, it’s recommended to limit the number of snapshots to minimize the impact on production environment performance.

Recovering Data from Snapshots

One of the key benefits of database snapshots is their ability to guard against data corruption. If you encounter data corruption or need to recover specific data, you can revert to a snapshot. To recover data from a snapshot, you can use the following query:

INSERT sample..mytab SELECT * FROM sample_snap3..mytab

If the original table data is updated or corrupted, you can recover from the corruption using a similar query:

UPDATE sample..mytab SET c2=(SELECT c2 FROM sample_snap3..mytab WHERE c1=1)

It’s important to note that reverting to a snapshot is not supported if the source database contains read-only or compressed files, or if any files are offline that were online during the snapshot. Additionally, both the original and snapshot databases will be marked as “in restore” during the revert operation.

Limitations and Considerations

While database snapshots offer valuable protection against logical disasters and data corruption, there are some limitations and considerations to keep in mind:

• The performance of the original database may be reduced due to increased I/O between the original database and snapshots whenever a page is updated. Limiting the number of snapshots can help minimize this impact.
• The source database is not scalable with snapshots. Adding too many snapshots can impact the performance of the original database.
• Reverting to a snapshot will recover your data from corruption, but you must perform a full database backup following the revert operation.
• Snapshots consume disk space, so it’s important to regularly delete older snapshots that are no longer needed.

By understanding these limitations and exercising caution, you can effectively leverage database snapshots to minimize your Recovery Time Objective and ensure the availability of your data.

Stay tuned for the next part of this series, where we will explore snapshot backups and their role in data availability.