{"id":7975,"date":"2019-04-05T11:32:44","date_gmt":"2019-04-05T11:32:44","guid":{"rendered":"https:\/\/ittutorial.org\/?p=7975"},"modified":"2019-11-28T06:54:54","modified_gmt":"2019-11-28T06:54:54","slug":"snapshot-database-sql-server-3","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/snapshot-database-sql-server-3\/","title":{"rendered":"SQL Server Database Snapshots -3"},"content":{"rendered":"<p>Hi<\/p>\n<p>I will continue to explain Database Snapshots in SQL Server in this article.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2778\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/database_snapshot_31.jpg\" alt=\"\" width=\"411\" height=\"389\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/database_snapshot_31.jpg 411w, https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/database_snapshot_31-300x284.jpg 300w\" sizes=\"auto, (max-width: 411px) 100vw, 411px\" \/><\/p>\n<p><!--more--><\/p>\n<p>Read previous articles before this post.<\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"GxvCJB4aGg\"><p><a href=\"https:\/\/ittutorial.org\/snapshot-database-sql-server-1\/\">SQL Server Database Snapshots -1<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;SQL Server Database Snapshots -1&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/snapshot-database-sql-server-1\/embed\/#?secret=YHveakvILh#?secret=GxvCJB4aGg\" data-secret=\"GxvCJB4aGg\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"Cr1xcLyviy\"><p><a href=\"https:\/\/ittutorial.org\/snapshot-database-sql-server-2\/\">SQL Server Database Snapshots -2<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;SQL Server Database Snapshots -2&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/snapshot-database-sql-server-2\/embed\/#?secret=g1kGu49hsW#?secret=Cr1xcLyviy\" data-secret=\"Cr1xcLyviy\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p>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<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/mehmetsalihdeveci.net\/2012\/09\/21\/sql-server-2008-de-database-snaphot-kullanimi\/snapshot-1\/\" rel=\"attachment wp-att-2775\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-medium wp-image-2775\" title=\"Snapshot 1\" src=\"http:\/\/salihdeveci.files.wordpress.com\/2012\/09\/snapshot-1.jpg?w=306\" alt=\"\" width=\"306\" height=\"400\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-1.jpg 356w, https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-1-230x300.jpg 230w\" sizes=\"auto, (max-width: 306px) 100vw, 306px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>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.<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: center;\"><a href=\"http:\/\/mehmetsalihdeveci.net\/2012\/09\/21\/sql-server-2008-de-database-snaphot-kullanimi\/snapshot-2\/\" rel=\"attachment wp-att-2776\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-2776\" title=\"Snapshot Kodu\" src=\"http:\/\/salihdeveci.files.wordpress.com\/2012\/09\/snapshot-2.jpg?w=400\" alt=\"\" width=\"400\" height=\"186\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-2.jpg 1195w, https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-2-300x140.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-2-768x359.jpg 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-2-1024x478.jpg 1024w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><\/a><\/p>\n<pre><em><strong>SQL Server Database Snaphot code\r\n<\/strong><\/em>\r\nCREATE DATABASE AdventureWorksSnaphot \u00a0ON\r\n( NAME = AdventureWorks_Data, FILENAME = 'd:\\Snapshot\\AdventureWorks_SparseFile.ss' )\r\nAS SNAPSHOT OF AdventureWorks;\r\nGO<\/pre>\n<p>&nbsp;<\/p>\n<div class=\"tlid-results-container results-container\">\n<div class=\"tlid-result result-dict-wrapper\">\n<div class=\"result tlid-copy-target\">\n<div class=\"result-header\">\n<div class=\"starbutton jfk-button-flat jfk-button unstarred\" tabindex=\"0\" role=\"button\" aria-label=\"Star translation\" data-tooltip=\"Star translation\" data-tooltip-align=\"t,c\">\n<div class=\"jfk-button-img\"><\/div>\n<\/div>\n<\/div>\n<div class=\"text-wrap tlid-copy-target\">\n<div class=\"result-shield-container tlid-copy-target\" tabindex=\"0\"><span class=\"tlid-translation translation\" lang=\"en\"><span title=\"\">AdventureWorksSnaphot was created at the bottom of the Snaphot tab, as it appears on the left side when the script is run.<\/span> <span title=\"\">This database is just an image or snapshot of the AdventureWorks database.<\/span> <span class=\"\" title=\"\">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.<\/span> <span class=\"\" title=\"\">The following image shows the location of the AdventureWorksSnaphot database on the Physical disk.<\/span><\/span><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/mehmetsalihdeveci.net\/2012\/09\/21\/sql-server-2008-de-database-snaphot-kullanimi\/snapshot-3\/\" rel=\"attachment wp-att-2777\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-medium wp-image-2777\" title=\"Snapshot 3\" src=\"http:\/\/salihdeveci.files.wordpress.com\/2012\/09\/snapshot-3.jpg?w=400\" alt=\"\" width=\"400\" height=\"276\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-3.jpg 956w, https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-3-300x208.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-3-768x532.jpg 768w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>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&#8217;s Sparsefile file. Sparse File as shown in the image below, only the Source database will be\u00a0 changed pages.<\/p>\n<p><a href=\"http:\/\/mehmetsalihdeveci.net\/2012\/09\/21\/sql-server-2008-de-database-snaphot-kullanimi\/database_snapshot_3-2\/\" rel=\"attachment wp-att-2778\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-medium wp-image-2778\" title=\"Database Snapshot \" src=\"http:\/\/salihdeveci.files.wordpress.com\/2012\/09\/database_snapshot_31.jpg?w=400\" alt=\"\" width=\"400\" height=\"378\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/database_snapshot_31.jpg 411w, https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/database_snapshot_31-300x284.jpg 300w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>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.<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/mehmetsalihdeveci.net\/2012\/09\/21\/sql-server-2008-de-database-snaphot-kullanimi\/snapshot-4\/\" rel=\"attachment wp-att-2779\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-2779 size-full\" title=\"Snapshot 4\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-4.jpg\" alt=\"\" width=\"1007\" height=\"707\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-4.jpg 1007w, https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-4-300x211.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-4-768x539.jpg 768w\" sizes=\"auto, (max-width: 1007px) 100vw, 1007px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>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.<\/p>\n<p><a href=\"http:\/\/mehmetsalihdeveci.net\/2012\/09\/21\/sql-server-2008-de-database-snaphot-kullanimi\/snapshot-5\/\" rel=\"attachment wp-att-2780\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-2780 size-full\" title=\"Snapshot 5\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-5.jpg\" alt=\"\" width=\"1129\" height=\"501\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-5.jpg 1129w, https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-5-300x133.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-5-768x341.jpg 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/snapshot-5-1024x454.jpg 1024w\" sizes=\"auto, (max-width: 1129px) 100vw, 1129px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>What will we do as DBA now ?<\/p>\n<p>&nbsp;<\/p>\n<p>I will continue to tell in the next post.<\/p>\n<p>&nbsp;<\/p>\n<h4><span style=\"color: #ff0000;\">Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.<\/span><\/h4>\n<p><a href=\"https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/\">https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi I will continue to explain Database Snapshots in SQL Server in this article.<\/p>\n","protected":false},"author":1,"featured_media":2778,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[2227],"tags":[467,508,1031,1515,1561,3217,1577,1578,1583,1600,1610,3218,3171,1664,3219,1665,1666,1856],"class_list":["post-7975","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-sql-server-2","tag-database-snapshot","tag-deveci","tag-mehmet-salih-deveci","tag-salih-deveci","tag-snapshot","tag-snapshot-database-sql-server","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-sql-server-2012","tag-sql-server-2014","tag-sql-server-2016","tag-sql-server-2017","tag-sql-server-snapshot","tag-sql-server-snapshot-database","tag-sql-server-snapshot-kavrami","tag-sql-server-snapshot-nasil-alinir","tag-veritaban-inda-snapshot-alma"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/09\/database_snapshot_31.jpg","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/7975","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=7975"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/7975\/revisions"}],"predecessor-version":[{"id":12937,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/7975\/revisions\/12937"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/2778"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=7975"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=7975"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=7975"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}