March 2019
M T W T F S S
« Jan    
 123
45678910
11121314151617
18192021222324
25262728293031

Categories

WordPress Quotes

Life shrinks or expands in proportion to one's courage.
Anais Nin

Recent Comments

March 2019
M T W T F S S
« Jan    
 123
45678910
11121314151617
18192021222324
25262728293031

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 (188)
Linux Commands (166)
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

13 visitors online now
2 guests, 11 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