Tag Archives: snapshots

Retrieving data from a Database Snapshots in SQL Server

Posted on by
Reply

I wrote a blog earlier, on Database Snapshots which explained about Database Snapshots, steps to create Database Snapshots and some points to remember when working with them. Before continuing reading this blog, I would recommend you read that blog here… Introduction to Database Snapshots in SQL Server

In this blog we shall look at a simple task of restoring data from a Database Snapshots in case if a table is deleted accidentally.

I would recommend you to use the scripts that are available in the earlier blog mentioned above to create the database snapshot and the tables used in this blog. We start this process by dropping a table and assume that, this has happened as an unforeseen event (accidentally dropping a table, in other words, dropping a table instead of dropping an actual one).

Once that table is dropped, our next step is to create the same table and bring in the data from one of the most recent Database Snapshots. In order to do this I am using the SELECT INTO statement as shown below.

To confirm whether or not the table has got the data back, we run the SELECT command on the table we created and populated data, in the previous step.

In this way Database Snapshots can also be used an alternate mechanism of Disaster Recovery for unforeseen events such as accidental dropping, truncating or updating of data.

*** Note: You cannot retrieve entire data from a database snapshot table, since it contains only the data in the table at the point of creation of the database snapshot. Any new data that has been inserted, updated, modified or deleted after the creation of the database snapshot cannot be retrieved.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

Introduction to Database Snapshots in SQL Server 2005

Posted on by
Reply

Database Snapshots is a new feature introduced in SQL Server 2005. It provides a mechanism that enables us to save an exact read only copy of a database when the snapshot was taken. It would be highly beneficial that a database snapshot is taken when certain bulk changes to the table data is made or you need to maintain an exact copy of your database as it was at the end of the day, each day.

Some points to be noted while working with Database Snapshots.

Database snapshot can be created using T-SQL script only, it cannot be implemented using the Management Studio.

  • Database snapshot of a database should exist on the same SQL Instance as the Source Database. Why this is so would be beyond the point of discussion for this article, so I would explain in another blog soon.
  • Database snapshots cannot be backed up. (But in the Object Explorer you will see that the menu options are available to backup)
  • Database snapshot cannot be updated or modified (That is why it is referred as read only Copy)
  • It is available only in Enterprise Edition of SQL Server 2005.

Script to create the Source Database

USE [master]
GO

CREATE DATABASE [Snapshot_Source] ON PRIMARY

( NAME = N'Snapshot_Source_Data', FILENAME =
N'D:\Snapshot_Source_Data.mdf' , SIZE = 4096KB,

MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'Snapshot_Source_log',
FILENAME = N'D:\Snapshot_Source_Log.ldf' ,

SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

Creating table called DemoTable using the following script

CREATE TABLE [Snapshot_Source].[dbo].[DemoTable](

[Name] [varchar](50) NULL,

[Age] [int] NULL )

ON [PRIMARY]

Inserting records in the DemoTable

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'A' , 30 )

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'B' , 35 )

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'C' , 37 )

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'D' , 40 )

Run the following script in the Query Editor

SELECT * FROM [Snapshot_Source].[dbo].[DemoTable]

CREATE DATABASE SNAPSHOT_01OCT2009_6AM ON PRIMARY

( NAME = N'Snapshot_Source_Data',

FILENAME = N'D:\SNAPSHOT_01OCT2009_6AM.ss' )

AS SNAPSHOT OF Snapshot_Source;

GO

Modifying the Data in the Source Database

Let’s modify the data in the source database by the following update statements.

UPDATE [Snapshot_Source].[dbo].[DemoTable]

SET [AGE] = 50 WHERE [Name] = 'A'

UPDATE [Snapshot_Source].[dbo].[DemoTable]

SET [AGE] = 55 WHERE [Name] = 'B'

Verifying the data at the source database and also from the snapshot by running the following script in the Query Editor.

SELECT * FROM [SNAPSHOT_01OCT2009_6AM].[dbo].[DemoTable]

SELECT * FROM [Snapshot_Source].[dbo].[DemoTable]

You can use a database snapshot in situations which needs a continuous read only copy of the database at a point of time. For example, you have a reporting application which requires data to be available and it is ok to have the data as it was at the end of day yesterday. A scheduled database snapshot of your transaction based Database which runs after the working hours each day would be a good solution.

Dropping a Database Snapshot

Dropping a database snapshot is same as dropping a database, using the Drop Database command.

The following command would delete the database snapshot that was used in this example.

DROP DATABASE SNAPSHOT_01OCT2009_6AM

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru to know when there is new content.

–Bru Medishetty