CREATE Database SNAPSHOTS Using SQL Server

Create snapshots using SQL Server 2008.Snapshots contain version changes after database snapshot has been created. Using this we can restore the data backup quickly. It contains only changes so we can revert back easily in case any user delete tables. Here am showing how to recover table using database snapshots.

First create a new database snapshot DB

Createdatabase SnapshotDB

GO

Create table after database has been created

USESnapshotDB

CREATETABLE [dbo].[PurchaseOrderDetail](

      [PurchaseOrderID] [int] NOTNULL,

      [LineNumber] [smallint] NOTNULL,

      [ProductID] [int] NULL,

      [UnitPrice] [money] NULL,

      [OrderQty] [smallint] NULL,

      [ReceivedQty] [float] NULL,

      [RejectedQty] [float] NULL,

      [DueDate] [datetime] NULL,

      [rowguid] [uniqueidentifier] ROWGUIDCOLNOTNULL,

      [ModifiedDate] [datetime] NOTNULL,

      [LineTotal]  AS ([UnitPrice]*[OrderQty]),

      [StockedQty]  AS ([ReceivedQty]-[RejectedQty]),

CONSTRAINT [PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber] PRIMARYKEYCLUSTERED

(

      [PurchaseOrderID] ASC,

      [LineNumber] ASC

)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]

)ON [PRIMARY]

GO

Create a database snapshot for that database SnapshotDB

CREATEDATABASE SnapshotDB_snapshot ON (

NAME = SnapshotDB,

FILENAME='D:\SQL server\MSSQL10.MALLSREDDY\MSSQL\DATA\SnapshotDB_Snapshot.ss')

ASSNAPSHOTOF SnapshotDB;

GO

Then from database SnapshotDB drop the table PurchaseOrderDetail

use SnapshotDB

droptable PurchaseOrderDetail

Then using the snapshot database ‘SnapshotDB_snapshot’ we can restore this

Using the following statement

RESTOREDATABASE SnapshotDB

FROM

DATABASE_SNAPSHOT='SnapshotDB_snapshot';

GO

You will encounter the below error.

Msg 3102, Level 16, State 1, Line 1

RESTORE cannot process database 'SnapshotDB' because it is in use by this session. It is recommended that the master database be used when performing this operation.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

You need to disconnect the connections for the database SnapshotDB or set the database into single user mode. Once restored again set to multi user mode.