January 2019
M T W T F S S
« Dec    
 123456
78910111213
14151617181920
21222324252627
28293031  

Categories

WordPress Quotes

Cherish your visions and your dreams as they are the children of your soul, the blueprints of your ultimate achievements.
Napoleon Hill

Recent Comments

January 2019
M T W T F S S
« Dec    
 123456
78910111213
14151617181920
21222324252627
28293031  

Short Cuts

2012 SERVER (64)
2016 windows (9)
AIX (13)
Amazon (34)
Ansibile (19)
Apache (133)
Asterisk (2)
cassandra (2)
Centos (209)
Centos RHEL 7 (261)
chef (3)
cloud (2)
cluster (3)
Coherence (1)
DB2 (5)
DISK (25)
DNS (9)
Docker (28)
Eassy (11)
ELKS (1)
EXCHANGE (3)
Fedora (6)
ftp (5)
GIT (3)
GOD (2)
Grub (1)
Hacking (10)
Hadoop (6)
horoscope (23)
Hyper-V (10)
IIS (15)
IPTABLES (15)
JAVA (7)
JBOSS (32)
jenkins (1)
Kubernetes (2)
Ldap (5)
Linux (189)
Linux Commands (167)
Load balancer (5)
mariadb (14)
Mongodb (4)
MQ Server (24)
MYSQL (84)
Nagios (5)
NaturalOil (13)
Nginx (31)
Ngix (1)
openldap (1)
Openstack (6)
Oracle (34)
Perl (3)
Postfix (19)
Postgresql (1)
PowerShell (2)
Python (3)
qmail (36)
Redis (12)
RHCE (28)
SCALEIO (1)
Security on Centos (29)
SFTP (1)
Shell (64)
Solaris (58)
Sql Server 2012 (4)
squid (3)
SSH (10)
SSL (14)
Storage (1)
swap (3)
TIPS on Linux (28)
tomcat (60)
Uncategorized (29)
Veritas (2)
vfabric (1)
VMware (28)
Weblogic (38)
Websphere (71)
Windows (19)
Windows Software (2)
wordpress (1)
ZIMBRA (17)

WP Cumulus Flash tag cloud by Roy Tanck requires Flash Player 9 or better.

Who's Online

16 visitors online now
7 guests, 9 bots, 0 members

Hit Counter provided by dental implants orange county

Snapshot database in SQL Server

What is a database snapshot ?

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.

How does a snapshot work:-

a. When you create a snapshot a sparse file is created for each data file.
b.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.
c.If the same data is the subsequently changed again, those changes will be ignored and not copied to the snapshot.

How to create a snapshot?

USE master
GO
— Create a source Database
CREATE DATABASE sourcedb
GO
USE sourcedb
GO
— Populate sourcedb Database with some Table
CREATE TABLE Employee (ID INT, Value VARCHAR(10))
INSERT INTO Employee VALUES(1, ‘First’);
INSERT INTO Employee VALUES(2, ‘Second’);
INSERT INTO Employee VALUES(3, ‘Third’);
INSERT INTO Employee VALUES(4, ‘Fourth’);
GO
— Now Create Snapshot Database
CREATE DATABASE SnapshotDB ON
(Name =’sourcedb’,
FileName=’E:\snapshotdb.ss1′)
AS SNAPSHOT OF sourcedb;
GO
— Select from source and Snapshot Database
SELECT * FROM sourcedb.dbo.Employee;
SELECT * FROM SnapshotDB.dbo.Employee;
GO

ID Value
1 First
2 Second
3 Third
4 Fourth

ID Value
1 First
2 Second
3 Third
4 Fourth

Now lets do some dml activity on source db :-
— Delete from sourcedb Database
DELETE FROM sourcedb.dbo.employee;
GO

ID Value

ID Value
1 First
2 Second
3 Third
4 Fourth

So we can see the rows have been deleted from the sourcedb but the snapshot db is totally intact as it is.

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.

— Restore Data from Snapshotdb Database
USE master
GO
RESTORE DATABASE sourcedb
FROM DATABASE_SNAPSHOT = ‘Snapshotdb’;
GO
— Select from sourcedb and Snapshotdb Database
SELECT * FROM sourcedb.dbo.employee;
SELECT * FROM Snapshotdb.dbo.employee;
GO

ID Value
1 First
2 Second
3 Third
4 Fourth

ID Value
1 First
2 Second
3 Third
4 Fourth

So we see all the datas are restored to the point when the snapshotdb was created.

Benefits of Database Snapshots:-

1.Snapshots can be used for reporting purposes.
2.Maintaining historical data for report generation.
3.Using a mirror database that you are maintaining for availability purposes to offload reporting.
4.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
large dml activity, usually create a snapshot of the original db prior to the dml activity being done.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

  

  

  

Blue Captcha Image
Refresh

*

Protected by WP Anti Spam