I will continue to explain Database Snapshots in SQL Server in this article.
Read previous articles before this post.
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
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.