Restoring Error

Question

Restoring Error

cavs143

Old Hand

Points: 319
More actions
June 23, 2009 at 4:12 pm

#384835

hi
i am restoring a db through the full back up and by tran log backup both WITH THE "NO RECOVERY" MODE
but my db shows like DbName(Restoring...)
when i try to launch the mirroring it shows this error message:
database cannot be opened.it is in the middle of a restore

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

When you applied the last transaction log, did you use the WITH RECOVERY clause or did you use the WITH NORECOVERY clause?

If the latter, try RESTORE DATABASE dbname WITH RECOVERY

ps. One Orange Chip Points: 29422 More actions · Answer 2

cavs143 (6/23/2009)
when i try to launch the mirroring it shows this error message:
database cannot be opened.it is in the middle of a restore

Can you elaborate more on Launch Mirroring? Are you trying to configure mirroring? In that case you need to consider this database (the one u're restoring) as a mirror database and not principle.

Pradeep Singh

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 3

Well if you restore the full backup and then the t-log with no recovery the database is suppose to show you the status of 'restoring' 🙂

if you are configuring Mirroring then you need to follow the steps below (assuming you need the witness server)

STEPS TO FOLLOW :-

=================

1. Log in to both boxes as local Admin.

2. Change SQL Server startup account to use "Network Service" Or “Local System Account”

3. Add NT AUTHORITY\NETWORK SERVICE to the admin group for both servers

4. On server A ( Principal server ) execute the following

It will Configure Outbound Connections

-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!';

GO

-- USE master

CREATE CERTIFICATE HOST_A_cert

WITH SUBJECT = 'HOST_A certificate',

START_DATE = '04/10/2007'

GO

-- CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP ( LISTENER_PORT=5022, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE HOST_A_cert

, ENCRYPTION = REQUIRED ALGORITHM RC4

, ROLE = ALL

);

GO

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOST_A_cert.cer';

GO

5. Now copy HOST_A_cert.cer to server B (C:\HOST_A_cert.cer) and Copy HOST_A_cert.cer to server C (C:\HOST_A_cert.cer)

6. On server B (Mirror server) Execute the following

It will Configure Outbound Connections

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';

GO

CREATE CERTIFICATE HOST_B_cert

WITH SUBJECT = 'HOST_B certificate for database mirroring',

START_DATE ='05/10/2007'

GO

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5022

, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE HOST_B_cert

, ENCRYPTION = REQUIRED ALGORITHM RC4

, ROLE = ALL

);

GO

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\HOST_B_cert.cer';

GO

7. Now copy HOST_B_cert.cer to server A (C:\HOST_B_cert.cer) and Copy HOST_A_cert.cer to server C (C:\HOST_A_cert.cer)

8. On server A ( Principal server ) Execute the following

It will Configure Inbound Connections

USE master;

CREATE LOGIN HOST_B_login WITH PASSWORD = '1Sample_Strong_Password!@#';

GO

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

GO

--Associate the certificate with the user.

CREATE CERTIFICATE HOST_B_cert

AUTHORIZATION HOST_B_user

FROM FILE = 'C:\HOST_B_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

GO

9. On server B ( Mirror server ) Execute the following

It will Configure Inbound Connections

USE master;

CREATE LOGIN HOST_A_login WITH PASSWORD = '=Sample#2_Strong_Password2';

GO

CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

GO

--Associate the certificate with the user.

CREATE CERTIFICATE HOST_A_cert

AUTHORIZATION HOST_A_user

FROM FILE = 'C:\HOST_A_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

GO

10.On server C ( Witness server ) execute the following

It will Configure Outbound Connections

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!';

GO

USE master

CREATE CERTIFICATE HOST_C_cert

WITH SUBJECT = 'HOST_C certificate',

START_DATE = '04/10/2007'

GO

-- CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP ( LISTENER_PORT=5023, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING (

AUTHENTICATION = CERTIFICATE HOST_C_cert

, ENCRYPTION = REQUIRED ALGORITHM RC4

, ROLE = Witness

);

GO

BACKUP CERTIFICATE HOST_C_cert TO FILE = 'C:\HOST_C_cert.cer';

GO

11. Now copy HOST_C_cert.cer to server B (C:\HOST_C_cert.cer) and Copy HOST_C_cert.cer to server A (C:\HOST_C_cert.cer)

12. Execute the following steps one by one …

On Principal Server

{

USE master;

CREATE LOGIN HOST_C_login WITH PASSWORD = '1Sample_Strong_Password!@#';

GO

CREATE USER HOST_C_user FOR LOGIN HOST_C_login;

GO

--Associate the certificate with the user.

CREATE CERTIFICATE HOST_C_cert

AUTHORIZATION HOST_C_user

FROM FILE = 'C:\HOST_C_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];

GO

}

--

On Mirror Server

{

USE master;

CREATE LOGIN HOST_C_login WITH PASSWORD = '1Sample_Strong_Password!@#';

GO

CREATE USER HOST_C_user FOR LOGIN HOST_C_login;

GO

--Associate the certificate with the user.

CREATE CERTIFICATE HOST_C_cert

AUTHORIZATION HOST_C_user

FROM FILE = 'C:\HOST_C_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];

GO

}

---

13.On server C ( Witness server ) Execute the following

It will Configure Inbound Connections

USE master;

CREATE LOGIN HOST_A_login WITH PASSWORD = '1Sample_Strong_Password!@#';

GO

CREATE USER HOST_A_user FOR LOGIN HOST_B_login;

GO

--Associate the certificate with the user.

CREATE CERTIFICATE HOST_A_cert

AUTHORIZATION HOST_A_user

FROM FILE = 'C:\HOST_A_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

GO

On server C ( Witness server ) Execute the following

It will Configure Inbound Connections

USE master;

CREATE LOGIN HOST_B_login WITH PASSWORD = '1Sample_Strong_Password!@#';

GO

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

GO

--Associate the certificate with the user.

CREATE CERTIFICATE HOST_B_cert

AUTHORIZATION HOST_B_user

FROM FILE = 'C:\HOST_B_cert.cer'

GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

GO

14. Take a Full & Transaction log Backup of the Database (on Principal) and Restore on Mirror With NoRecovery.

15. On Mirror Server B Execute :

Alter Database

Set Partner =’TCP://:5022’

Go

16. On Principal Server A Execute :

Alter Database

Set Partner =’TCP://:5022’

Go

13. On Principal Server A Execute :

Alter Database

Set Witness=’TCP://:5023’

Go

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

cavs143 Old Hand Points: 319 More actions · Answer 4

cavs143

Old Hand

Points: 319

June 24, 2009 at 1:19 pm

#1014882

Thanks to all of u......it worked for me

forsqlserver SSCoach Points: 18902 More actions · Answer 5

Dear Lynn,

I am getting this error as you suggest...

TITLE: Database Properties

------------------------------

An error occurred while starting mirroring.

------------------------------

ADDITIONAL INFORMATION:

Alter failed for Database 'abcv_Mirror'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Database mirroring cannot be enabled because the "abcv_Mirror" database is an auto-close database on one of the partners. (Microsoft SQL Server, Error: 1469)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3077&EvtSrc=MSSQLServer&EvtID=1469&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

One of the databases has the auto-close option set to true. Set it to false for both.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

forsqlserver SSCoach Points: 18902 More actions · Answer 7

Dear All,

here ajayg is server name and intranet.nic.in is domain name.

Now the error is:

TITLE: Database Properties

------------------------------

An error occurred while starting mirroring.

------------------------------

ADDITIONAL INFORMATION:

Alter failed for Database 'abcv_Mirror'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server network address "TCP://ajayg.intranet.nic.in:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3077&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

Thanks