{"id":1787,"date":"2012-02-20T15:37:09","date_gmt":"2012-02-20T15:37:09","guid":{"rendered":"http:\/\/craftydba.com\/?p=1787"},"modified":"2017-10-12T01:01:39","modified_gmt":"2017-10-12T01:01:39","slug":"database-files-part-3","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1787","title":{"rendered":"Database Files &#8211; Part 3"},"content":{"rendered":"<p><a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms187054.aspx\">DATABASE SNAPSHOTS<\/a> were added to the SQL Server product in the 2005 version.  They are great for saving a version of the database before major data changes happen and perfect for rolling back the changes if they are not successful.  While books online suggest <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms175472.aspx\">other uses<\/a>, I think this one is the best.  Please see the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms189940.aspx\">limitations<\/a> of snapshots before creating one.   I am going to explain how to create a database snapshot, how to revert back to a snapshot after corrupted occurs, and how to drop a database snapshot.<\/p>\n<p>A SNAPSHOT uses the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms175823.aspx\">NTFS SPARSE<\/a> file technology.  Basically, snapshot data files are empty upon creation and grown in increments of 64 KB.  When changes such as UPDATES or DELETES happen to the data, the original data is written to the snapshot before applying the change to the database.  The size of the SNAPSHOT is at most the size of the database at the time the snapshot is taken.  As a convention, a *.ss extension is used for snapshot data files.<\/p>\n<p>The <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms175876.aspx\">CREATE DATABASE<\/a>, <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms189281.aspx\">RESTORE DATABASE<\/a>, and <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms190220.aspx\">DROP DATABASE<\/a> statements are used perform operations with SNAPSHOTS.<\/p>\n<p>This article assumes the [WILDLIFE] database has been built using the scripts in either Part 1 or Part 2.  We are going to create a sub-directory to hold the new snapshot data files.  Snapshots are based on the original data files.  The below snippet creates a SNAPSHOT of the [WILDLIFE] database.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"tsql - create database snapshot\">\r\n\r\n-- Modify production directory to hold snapshots\r\nEXEC xp_cmdshell 'mkdir c:\\MSSQL\\SNAP';\r\nGO\r\n\r\n-- Create a new snapshot database\r\nCREATE DATABASE WILDLIFE_SS ON\r\n( NAME = WildLifeData, FILENAME = 'C:\\MSSQL\\SNAP\\Wild_Life_Data.ss' )\r\nAS SNAPSHOT OF [WILDLIFE];\r\nGO\r\n<\/pre>\n<\/p>\n<p>I can not stress enough that SNAPSHOTS should not be used for point in time recovery.  They are great precaution before execution a major ETL process.  If the ETL process fails, the SNAPSHOT can be used to revert the database.  SNAPSHOTS are quicker than a RESTORE process since they keep track of the changes only.  A 1 TB database that is corrupted after 1 GB of ETL data changes has alot less data to restore than using a full recovery strategy.<\/p>\n<p>The following snippet deletes odd number entries in the ANIMALS table.  This is a simple example of a ETL process gone bad.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"tsql - remove records with odd id\">\r\n-- Select odd id numbers\r\nSELECT COUNT(*) AS ODDNUMS FROM WILDLIFE.dbo.ANIMALS \r\nWHERE (ID % 2) = 1\r\n\r\n-- Remove the odd numbers (unwanted damage)\r\nDELETE FROM WILDLIFE.dbo.ANIMALS \r\nWHERE (ID % 2) = 1\r\n><\/pre>\n<\/p>\n<p>Last but not least, the SNAPSHOT can be used to revert the database to a state before the unwanted DELETE operation.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"tsql - restore from database snapshot\">\r\n-- Start with master\r\nUSE master;\r\nGO\r\n\r\n-- Do a restore with the snapshot (REVERT)\r\nRESTORE DATABASE [WILDLIFE] FROM DATABASE_SNAPSHOT = 'WILDLIFE_SS';\r\nGO\r\n\r\n-- Total number of records = 445\r\nSELECT COUNT(*) AS TOTAL FROM WILDLIFE.dbo.ANIMALS \r\n<\/span><\/pre>\n<\/p>\n<p>To clean up after the ETL process, use the DROP DATABASE command to remove the SNAPSHOT.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"tsql - drop database snapshot\">\r\n-- Use master\r\nUSE [master]\r\nGO\r\n\r\n-- Delete existing snapshot database\r\nIF EXISTS (SELECT name FROM sys.databases WHERE name = N'WILDLIFE_SS')\r\n    DROP DATABASE [WILDLIFE_SS]\r\nGO\r\n<\/pre>\n<p>In summary, DATABASE SNAPSHOTS can be used to prevent unwanted changes to the database during major ETL updates.  They should not be used in place of normal FULL, DIFFERENTIAL, and LOG backups.  Snapshots will not get you that point in time recovery that is needed by most business lines.<\/p>\n<p>Enclosed is the complete sample SQL <a href='https:\/\/craftydba.com\/wp-content\/uploads\/2012\/02\/snap-shot-example.txt'>script<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>DATABASE SNAPSHOTS were added to the SQL Server product in the 2005 version. They are great for saving a version of the database before major data changes happen and perfect for rolling back the changes if they are not successful. While books online suggest other uses, I think this one is the best. Please see the limitations of snapshots before creating one. I am going to explain how to create a database snapshot, how to revert back to a snapshot after corrupted occurs, and how to drop a database snapshot.&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[50,117,388,52,12,15,386,387,389,28,29],"class_list":["post-1787","post","type-post","status-publish","format-standard","hentry","category-db-admin","tag-create-database","tag-database-administrator","tag-database_snapshot","tag-drop-database","tag-free-code","tag-john-f-miner-iii","tag-restore-database","tag-snapshot","tag-sparse-ntfs-file","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1787","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1787"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1787\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1787"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1787"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1787"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}