SQL Server 2008R2:Database Snapshot uses --Database Mirroring

How to verify the data is mirrored from principal database to mirror database in the existing DATABASE MIRRORING (use of DATABASE SNAPSHOT).

Step1: Create a new table in the principal server database and insert data into it by the following statement.

USE [Kumar]

GO

/****** Object: Table [dbo].[Tbl_mirror_test] Script Date: 02/14/2013 19:48:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Tbl_mirror_test](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Event] [nvarchar](50) NULL

) ON [PRIMARY]

GO

INSERT INTO [Kumar].[dbo].[ Tbl_mirror_test]

([Event])

VALUES

(‘Kabbadi Match’)

GO

Step 2: Then create a database snapshot in the mirror server (Ensure your SQL server supports database snapshot feature) by the following statement.

CREATE DATABASE SnapshotDB007 ON

( NAME = Kumar_Data, FILENAME = 'G:\ Kumar _Data_22.ss' )

AS SNAPSHOT OF Kumar

GO

/************

SnapshotDB007--> the snaphshot name

Kumar_Data-->logical file name for the mdf file of Kumar database.

G:\ Kumar _Data_22.ss-->you can mention any filename in any path to store snapshot.

SNAPSHOT OF Kumar -->specifies which database needs to be snapshot**********/

Step 3: Fetch the updated data by executing the following statement in the mirror server.

select * from SnapshotDB007.dbo.Tbl_ mirror _Test

Output:

Kabbadi Match

If you try to drop the database 'Kumar',it wont allow to drop until you remove the associated database snapshot by the below method.

DROP DATABASE Kumar

Error:

Msg 3709, Level 16, State 2, Line 2

Cannot drop the database while the database snapshot "SnapshotDB" refers to it. Drop that database first.

USE [master]

GO

/****** Object: Database [SnapshotDB] Script Date: 02/14/2013 13:32:28 ******/

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'SnapshotDB')

DROP DATABASE [SnapshotDB]

GO

Note:

We have no options to create database snapshot by wizard in the Sql Server Management Studio.(Refer the below image).

Hope you can verify the database mirroring data by the above method.If you know any other method,please Submit a comment!!!!!!!