SQL Server Database Snapshots -1

Hi

In this article, I will explain Database Snapshots in SQL Server. Database Snapshot is a feature that comes with SQL Server 2005 and is an ongoing feature in 2008, 2012, 2014, 2016, SQL Server 2017 and so on. Database Snapshot; The simplest definition is that a read-only copy of the Database or snapshot of database at any given moment.

When the snapshot process is performed, SQL Server takes the current image of the selected database but does not have the current physical data on the disk for Snapshot database.

There is a unique physical data and it is just for Production database. Uncommit transactions rollback while the snapshot is being taken. Any changes made to the snapshot database will be written to the Sparse File for Snapshot database.

This means that if anybody has changed any data in Snapshot database when anybody query any select related with same data from Snapshot , then SQL Server will read the changed data from the Sparse file and read the unmodified data from the original database.

 

The most beautiful portrait that summarizes this scenario is the following picture that is published by MSDN.

 

 

 

As shown above picture, in the 1st case, 3 out of 10 pages of the production Database is being Updated. These Updates are also written in Sparse File as shown in the 1st case. in the 2st case, 8 pages from the source database are updated and 7 pages are written to the sparse file.

When a Report or Query is executed from Snapshot, SQL Server reads unchanged pages from SQL Server Source Database, and SQL Server reads changed pages from Sparse file.

 

The main purpose of Snapshot is to create a Stable Report or test database for developers for a certain interval by making a backup of any of the database and creating reports via Snapshot.  if minor errors occur in production database, Admin can  return to its original state from Snapshot.

However, it is very important that Snapshot database is connected to one-to-one its source database If it is deleted or migrated then main database can not be returned from Snapshot database. That’s why the Snapshot database isn’t exactly a Backup Strategy for SQL Server. It can be used for test databases.

 

I will continue to tell about SQL Server database snapshot in next post.

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.