Wednesday, February 6, 2013

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


No comments:

Post a Comment

MYSQL::Setting Validate_Password componet for MySQL Database to ensure password policy settings

Inadequate Password Settings for MySQL Database We observed that the `validate_password%` settings on hostname `<insert hostname>` a...