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!!!!!!!

SQL Server:Best practice to set Database Owner name.

/*************How to find the owner of all the databases to match with database creation standards i.e. it should be in non expiry sql logins.

If we give any employee windows login, then the validity of the login will come to an end once the employee leave his job. In order to avoid those issues in future, we need to set owner of the database to 'SQL Login'*****************/

--'00000007' IS THE SQL AUTHENTICATED LOGIN for database owner. You can replace your login with ‘00000007'.

SELECT name AS DATABASE_NAME,owner_sid FROM SYS.databases

WHERE name IN(

SELECT SD.NAME AS DATABASE_NAME

--SL.loginname AS OWNERNAME

FROM MASTER..SYSLOGINS SL JOIN

SYS.databases SD

ON SL.SID=SD.OWNER_SID WHERE SL.loginname='00000007')

/***********How to find the owner of all the databases which doesn't match with database creation standards

i.e it should be in non expiry sql logins.*************/

SELECT name AS DATABASE_NAME,owner_sid FROM SYS.databases

WHERE name NOT IN(

SELECT SD.NAME AS DATABASE_NAME

--SL.loginname AS OWNERNAME

FROM MASTER..SYSLOGINS SL JOIN

SYS.databases SD

ON SL.SID=SD.OWNER_SID WHERE SL.loginname='00000007')

'00000007' IS THE SQL AUTHENTICATED LOGIN for database owner. You can replace your sql login with ‘00000007'.

/*************To find the non-standard database owner name by passing the above script output to below sid parameter value.

OWNER_SID SHOULD BE LIKE-'0x0105066666600051500000100'*************/

select name as owner_name from MASTER..syslogins where sid='input the parameter value'

/********How to change the owner of the database into database creation standards owner name? **************/

USE DATABASE_NAME

GO

select name as owner_name from MASTER..syslogins where sid=0x01

GO

--SP_CHANGEDBOWNER 'NEW_OWNER_NAME'

SP_CHANGEDBOWNER '00000007'

Hope you can maintain the owner name for all database as one sql login to follow the database creation standards by the above method.

SQL2008R2:Steps to Restore the principal database in mirroring!!!!!!!!!!

Error: Msg 3104, Level 16, State 1, Line 1

RESTORE cannot operate on database 'Mirroringdatabase' because it is configured for database mirroring.

Use ALTER DATABASE to remove mirroring if you intend to restore the database.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Solution:

Follow the below steps to restore the principal database

1. Switch of the mirroring

ALTER DATABASE [Mirroringdatabase]

SET PARTNER OFF

2. Restore the principal database.Once restore is completed Then Take the Backup of the Principal Database and Transaction Log copy over to Mirror Server

RESTORE DATABASE [Mirroringdatabase] FROM DISK = N'X:\Mirroringdatabase\Mirroringdatabase_Full_TSQL.bak'

WITH FILE = 1,

MOVE N'Mirroringdatabase_log' TO N'H:\Microsoft SQL Server\MSSQL.INST01\Data\Mirroringdatabase_1.ldf',

RECOVERY, NOUNLOAD, STATS = 10

GO

3. Set Partner on Mirror server

--Run below script in mirror server

ALTER DATABASE Mirroringdatabase

SET PARTNER = 'TCP://PRINCIPALSERVER.ENVIRONMENT.DOMAIN.NET:5022'

--Mirror database changed into recovery from restoring process..

4. Set Partner on Master server

--Run below script in principal server

ALTER DATABASE Mirroringdatabase

SET PARTNER = 'TCP://MIRRORSERVER.environment.domain.net:5022'

I have got the below error while running the above script.

Error : Msg 1408, Level 16, State 0, Line 1

The remote copy of database "Mirroringdatabase" is not recovered far enough to enable database mirroring.

Because the transaction Log file that you restore on your mirror database is not new and fresh enough to catch the primary database. So, backup the full and log from the primary database and restore it on the mirror database in the possible shortest time and try starting the database mirroring.

RESTORE DATABASE [Mirroringdatabase] FROM DISK = N'X:\Mirroringdatabase\Mirroringdatabase_Full_TSQL.bak'

WITH FILE = 1,

MOVE N'Mirroringdatabase_log' TO N'H:\Microsoft SQL Server\MSSQL.INST01\Data\Mirroringdatabase_1.ldf',

NORECOVERY, NOUNLOAD, STATS = 10

GO

RESTORE LOG [Mirroringdatabase] FROM DISK = N'X:\Mirroringdatabase\Mirroringdatabase_TLog_TSQL.trn'

WITH FILE = 1,

NORECOVERY, NOUNLOAD, STATS = 10

GO

5. Repeat the steps 3 and 4 to set partners for mirroring.

Hope you can restore the principal database without no issues.!!!