What is a database snapshot ?<\/p>\n
Database snapshots are an Enterprise only feature which was introduced \u00a0in SQL Server 2005.A database snapshot is a view of what the source database looked like at the time when the
\nsnapshot was created.<\/p>\n
How does a snapshot work:-<\/p>\n
a. When you create a snapshot a sparse file is created for each data file.
\nb.When data is modified in the source database for the first time, the old value of the modified data is copied to the sparse file.
\nc.If the same data is the subsequently changed again, those changes will be ignored and not copied to the snapshot.<\/p>\n
How to create a snapshot?<\/p>\n
USE master
\nGO
\n— Create a source Database
\nCREATE DATABASE sourcedb
\nGO
\nUSE sourcedb
\nGO
\n— Populate sourcedb Database with some Table
\nCREATE TABLE Employee (ID INT, Value VARCHAR(10))
\nINSERT INTO Employee VALUES(1, ‘First’);
\nINSERT INTO Employee VALUES(2, ‘Second’);
\nINSERT INTO Employee VALUES(3, ‘Third’);
\nINSERT INTO Employee VALUES(4, ‘Fourth’);
\nGO
\n— Now Create Snapshot Database
\nCREATE DATABASE SnapshotDB ON
\n(Name =’sourcedb’,
\nFileName=’E:\\snapshotdb.ss1′)
\nAS SNAPSHOT OF sourcedb;
\nGO
\n— Select from source and Snapshot Database
\nSELECT * FROM sourcedb.dbo.Employee;
\nSELECT * FROM SnapshotDB.dbo.Employee;
\nGO<\/p>\n
ID Value
\n1 First
\n2 Second
\n3 Third
\n4 Fourth<\/p>\n
ID Value
\n1 First
\n2 Second
\n3 Third
\n4 Fourth<\/p>\n
Now lets do some dml activity on source db :-
\n— Delete from sourcedb Database
\nDELETE FROM sourcedb.dbo.employee;
\nGO<\/p>\n
ID Value<\/p>\n
ID Value
\n1 First
\n2 Second
\n3 Third
\n4 Fourth<\/p>\n
So we can see the rows have been deleted from the sourcedb but the snapshot db is totally intact as it is.<\/p>\n
Now, incase if we want to undo all the changes we did, we can revert back the database from the snapshotdb which was created before doing the activity.<\/p>\n
— Restore Data from Snapshotdb Database
\nUSE master
\nGO
\nRESTORE DATABASE sourcedb
\nFROM DATABASE_SNAPSHOT = ‘Snapshotdb’;
\nGO
\n— Select from sourcedb and Snapshotdb Database
\nSELECT * FROM sourcedb.dbo.employee;
\nSELECT * FROM Snapshotdb.dbo.employee;
\nGO<\/p>\n
ID Value
\n1 First
\n2 Second
\n3 Third
\n4 Fourth<\/p>\n
ID Value
\n1 First
\n2 Second
\n3 Third
\n4 Fourth<\/p>\n
So we see all the datas are restored to the point when the snapshotdb was created.<\/p>\n
Benefits of Database Snapshots:-<\/p>\n
1.Snapshots can be used for reporting purposes.
\n2.Maintaining historical data for report generation.
\n3.Using a mirror database that you are maintaining for availability purposes to offload reporting.
\n4.In the event of a user error on a source database, you can revert the source database to the state it was in when a given database snapshot was created. For example, before doing
\nlarge dml activity, usually create a snapshot of the original db prior to the dml activity being done.<\/p>\n","protected":false},"excerpt":{"rendered":"
What is a database snapshot ?<\/p>\n
Database snapshots are an Enterprise only feature which was introduced in SQL Server 2005.A database snapshot is a view of what the source database looked like at the time when the snapshot was created.<\/p>\n
How does a snapshot work:-<\/p>\n
a. When you create a snapshot a sparse file is […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[44],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/5618"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5618"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/5618\/revisions"}],"predecessor-version":[{"id":5619,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/5618\/revisions\/5619"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5618"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5618"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5618"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}