IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi
I will continue to explain Database Snapshots in SQL Server in this article.
Read previous articles before this post.
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 coderestoredatabaseAdventureWorksfromdatabase_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.
https://ittutorial.org/sql-server-tutorials-microsoft-database-for-beginners/
