Ulock read only database

Question

Post reply

Ulock read only database

Niyala

SSCrazy

Points: 2778
More actions
July 1, 2009 at 6:41 am

#202009

Hi list,
I restored a database with norecovery mode for the purpose database mirroring. How can I change the satae of this database so I can read again?
Thanks indeed
Niyala

Viewing 14 posts - 1 through 13 (of 13 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

restore database dbname with recovery

brings a database online again, but did you actually want to know how to failover a mirrored database?

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

Niyala SSCrazy Points: 2778 More actions · Answer 2

Thanks indeed. How to failover was my next issue that I wanted to test. Unfortunately my mirroring attempt fails when I use the following script.

-- Change the recovary model to full.

USE [master]

GO

ALTER DATABASE [myDB] SET RECOVERY FULL WITH NO_WAIT

GO

-- Create a full backup of the database

USE myDB

GO

BACKUP DATABASE myDB

TO DISK = N'E:\backup\myDBDATA\myDB.bak' WITH NOFORMAT, INIT

GO

-- Create a transaction log backup

BACKUP LOG [myDB] TO DISK = N'E:\backup\myDBDATA\myDB.trn' WITH NOFORMAT, INIT

GO

On the Mirror server

RESTORE DATABASE [myDB]

from disk = '\\Server_1\E$\Backup\myDBDATA\myDB.bak'

WITH NORECOVERY, REPLACE

RESTORE LOG [myDB]

FROM DISK = '\\Server_1\E$\Backup\myDBDATA\myDB.trn'

WITH NORECOVERY

On the principal

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP (LISTENER_PORT = 5022)

FOR DATABASE_MIRRORING (ROLE = PARTNER)

GO

On the mirror

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP (LISTENER_PORT = 5022)

FOR DATABASE_MIRRORING (ROLE = ALL)

GO

ALTER DATABASE myDB

SET PARTNER = 'TCP://Server_1.xxx-ge.net:5022'

GO

The following step fails with the following error

On the principal

ALTER DATABASE myDB

SET PARTNER = 'TCP://Server_2.xxx-ge.net:5022'

Msg 1418, Level 16, State 1, Line 1

The server network address "TCP://PDIESS01SQ0012.POLIZEI-BW.DE: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.

Please I appreciate if you could give me a hint on how to solve this problem.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

have you tried setting up endpoints via the GUI?

check ports are not in use by any other process

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

Niyala SSCrazy Points: 2778 More actions · Answer 4

Thanks. I used the gui. This ports are not used by other applications. I managed to do the mirroring using the same port using the wizard. I then deleted the endpoints (DROP endpoints mirroring).

Then I repeated the procedure using the script. Now neither the script nor the wizard can do th mirroring.

Need hint please

Regards

Niyala

george sibbald SSC Guru Points: 104210 More actions · Answer 5

undoing it when you had it working might have been shooting yourself in the foot. run

alter database dbname set partner off

to remove all traves of mirroring and try through the GUI again.

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

Niyala SSCrazy Points: 2778 More actions · Answer 6

Thanks, but I am more interested in getting the script running, as I have to mirror 100 +- servers.

Grateful for any hint

Regards

Niays

george sibbald SSC Guru Points: 104210 More actions · Answer 7

I think you are missing the grant connect statement to the endpoints.

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

george sibbald SSC Guru Points: 104210 More actions · Answer 8

else, set up just one via the GUI and then use the script option on the mirroring tab of properties to create the base script for you.

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

Niyala SSCrazy Points: 2778 More actions · Answer 9

Hi,

Thanks for your message. I tried to script the action, unfortunately this action can not be scripted using the scripting button. Is there another means to script?

Regards

Niyala

Niyala SSCrazy Points: 2778 More actions · Answer 10

Hi,

Thanks again. I was wondering if you could give me information or guidelines about how to failover. I have not witness. I have only the principal and the mirror. I can manually failover the databases, but how about if the principal crushes?

Thanks a lot.

Regards

Niyala

george sibbald SSC Guru Points: 104210 More actions · Answer 11

sorry no. have you tried BOL - search on 'Setting Up Database Mirroring (Transact-SQL)'

Otherwise it looks like there is a problem with the server name or port.

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

Niyala SSCrazy Points: 2778 More actions · Answer 12

Hi,

Thanks a lot, I solved this problem. I do not know what was going wrong, all I did was to reinstall sql server on both machines.

My open question is how to use the mirror database in case the principal fails. Ok I can do restore. Is there additional step to do?

Thanks indeed.

Niyala

george sibbald SSC Guru Points: 104210 More actions · Answer 13

Niyala (7/2/2009)
Hi,
Thanks again. I was wondering if you could give me information or guidelines about how to failover. I have not witness. I have only the principal and the mirror. I can manually failover the databases, but how about if the principal crushes?
Thanks a lot.
Regards
Niyala

You have two options if the principal is totally unavailable. Alter database set partner off followed by bring database online

or

alter database set partner force_service_allow_data_loss

which you use depends on how long you think the principal will be down and whether you want the mirror to become the principal moving forwards. there is a risk of data loss if you are using high performance mode.

you will also need to point the client to the new principal server

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