SQL Server Database Snapshots -4


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

SQL Server Database Snapshots -3


This error is very scary and financial loss for Production database.


We will use the Snapshot database to return from this error. We use the AWBuildVersion table of Snapshot database  to create the AWBuildVersion table in the AdventureWorks database. We will use Select * into command for this. The screenshot after running the script is as follows. As indicated by the red line, Dropped Table is returned into Source database with its data.



Let’s do another similar example in terms of being more comprehensible. Let’s delete the data of any table and return it from Snapshot database again. As outlined in Image 1.1 below, the BillOfMaterials table at the bottom of the ProductionWorks database is being deleted from the AdventureWorks database. When we select the Select count at the same time, the 0 record is queried as shown in the image below.



We use the Snapshot database again to return from this error. As in 2.1 above, we are doing the insertion of the same schema and table of the Snapshot database into the corresponding table in the source database. In the same way, the same number of line records are inserted as shown in image 2.2 when we queried Select Count.

Finally, lets make an administrator error from the Snapshot database, and this time we will Restore Source database from Snaphot database. Thus, AdventureWorks database snapshot is getting back to the initial state. I am deleting the data of the BillOfMaterials table from Production Schema and at the same time I dropped the SalesOrderDetail table under the Sales schema. The screenshot is as follows. As the data of the BillOfMaterials table at the bottom of the Production Schema has been deleted, the SalesOrderDetail table does not appear becuase it is dropped.



We made a lot of changes in the source database, we have said that these changes are always written to Sparse File. The following image shows the final version of the Sparse file. While the original size 1 has not changed, the original Sparse file number 2 has increased. The reason for this, as I said, is that all changes made in the Source database are written here. So when the user reads changed data, it reads from the Sparse File. If user query unchanged data, it will be read from source database.


Now let’s return back to Snapshot.


SQL Server 2017 Database Snaphot Restore code

restore database  AdventureWorks from database_snapshot='AdventureWorksSnaphot'


We can query all dropped and deleted tables after restoring Snapshot as shown above image.



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 *