SQL Server Database Snapshots -3

Mehmet Salih Deveci

6 years ago

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;
GO

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.

https://ittutorial.org/sql-server-tutorials-microsoft-database-for-beginners/