SQL Server Database Snapshots -3


I will continue to explain Database Snapshots in SQL Server in this article.

Read previous articles before this post.

SQL Server Database Snapshots -1

SQL Server Database Snapshots -2


Database Snaphots under the Databases tab are as follows. Since no Snapshot of any Database has been taken, the Database Snapshots tab is blank as it appears




You cannot create Snapshot on SQL Server Management Studio, you should use T-SQL code in the following image. An example is the AdventureWorks database, which is the test database I used in many examples.


SQL Server Database Snaphot code

CREATE DATABASE AdventureWorksSnaphot  ON
( NAME = AdventureWorks_Data, FILENAME = 'd:\Snapshot\AdventureWorks_SparseFile.ss' )
AS SNAPSHOT OF AdventureWorks;


AdventureWorksSnaphot was created at the bottom of the Snaphot tab, as it appears on the left side when the script is run. This database is just an image or snapshot of the AdventureWorks database. Although a Sparse file is created up to the size of the AdventureWorks database in the disk, it is initially created as a very small size. The following image shows the location of the AdventureWorksSnaphot database on the Physical disk.



The size 1 of the above image (1) is actually the size of the AdventureWorks Source database. Size 2 is the size of the AdventureWorksSnaphot’s Sparsefile file. Sparse File as shown in the image below, only the Source database will be  changed pages.


When we examine the Snapshot database, all items such as Table, Views, Stored Procedures etc. in the Source are all in the Snapshot database. As in the image below, the same tables as Source database can also be queried from the Snaphot database.



I said Snapshot database are also effective for Administrator and user errors. For example, errors such as deleting a table completely, or truncating the table known Administrator errors. For example, the AWBuildVersion table that appears in Frame 1 under the DBO schema from the AdventureWorks database is being dropped. When the Tables Tab is refreshed after the script is run, the AWBuildVersion table is dropped from the AdventureWorks Source database as it appears in image number 2 below.


What will we do as DBA now ?


I will continue to tell in the next post.


Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.


About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and 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 [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *