This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous post, I have shared basic theory on SQL Server Database Snapshot.
In this post, I am sharing T-SQL scripts to create Snapshot Database in SQL Server.
First, Create one test database:
|
1 2 3 4 |
USE master GO CREATE DATABASE TestRND GO |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 |
USE TestRND GO CREATE TABLE tbl_TestSnapshot (ID INTEGER PRIMARY KEY, Name VARCHAR(50)) GO INSERT INTO tbl_TestSnapshot VALUES (1,'Anvesh'),(2,'Neevan') ,(3,'Roy'),(4,'Martin') ,(5,'Jenny'),(6,'Kavia') GO |
Create a Snapshot Database:
Please specify source database .mdf file name and file path and create a new sparse file for Snapshot with .ss extension.
|
1 2 3 4 5 6 7 |
USE master GO CREATE DATABASE TestRND_SS ON (NAME=TestRND,FILENAME='C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQL_DBRND\MSSQL\DATA\TestRND_SS.ss') AS SNAPSHOT OF TestRND GO |
Update data into source database:
|
1 2 3 4 |
USE TestRND GO UPDATE tbl_TestSnapshot SET Name = 'dbrnd' WHERE ID=1 GO |
As earlier, I have discussed that Snapshot Database keep all old data.
whenever a source database updates the data, it never updates the original data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE TestRND_SS GO SELECT *FROM tbl_TestSnapshot GO /* Result... ID Name ------ ------------ 1 Anvesh 2 Neevan 3 Roy 4 Martin 5 Jenny 6 Kavia */ |
