Posts

Showing posts from February, 2013

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

Image
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 ******/ SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO CREATETABLE [dbo].[Tbl_mirror_test]( [ID] [int] IDENTITY(1,1)NOTNULL, [Event] [nvarchar](50)NULL )ON [PRIMARY] GO INSERTINTO [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.
CREATEDATABASE SnapshotDB007 ON ( NAME = Kumar_Data,FILENAME='G:\ Kumar _Data_22.ss') ASSNAPSHOTOF Kumar GO
/************ SnapshotDB007--> the snaphshot name
Kumar_Data-->logical file name for the mdf file of Kumar database.
G:\…

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'.

SELECTnameASDATABASE_NAME,owner_sidFROMSYS.databases WHEREnameIN( SELECTSD.NAMEASDATABASE_NAME --SL.loginname AS OWNERNAME FROMMASTER..SYSLOGINSSLJOIN SYS.databasesSD ONSL.SID=SD.OWNER_SIDWHERESL.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.*************/
SELECTnameASDATABASE_NAME,owner_sidFROMSYS.databases WHEREnameNOT

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
ALTERDATABASE [Mirroringdatabase] SETPARTNEROFF

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
RESTOREDATABASE [Mirroringdatabase] FROMDISK=N'X:\Mirroringdatabase\Mirroringdatabase_Full_TSQL.bak' WITHFILE= 1, MOVEN'Mirroringdatabase_log'TON'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
ALTERDATABASE Mirroringdatabase SETPARTNER='TCP://PRINCIPALSERVER.…