|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Thursday, October 22, 2009 12:45 AM
Points: 130,
Visits: 306
|
|
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
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 1,895,
Visits: 4,146
|
|
restore database dbname with recovery
brings a database online again, but did you actually want to know how to failover a mirrored database?
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, October 22, 2009 12:45 AM
Points: 130,
Visits: 306
|
|
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.
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 1,895,
Visits: 4,146
|
|
have you tried setting up endpoints via the GUI?
check ports are not in use by any other process
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, October 22, 2009 12:45 AM
Points: 130,
Visits: 306
|
|
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
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 1,895,
Visits: 4,146
|
|
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.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, October 22, 2009 12:45 AM
Points: 130,
Visits: 306
|
|
Thanks, but I am more interested in getting the script running, as I have to mirror 100 +- servers.
Grateful for any hint
Regards
Niays
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 1,895,
Visits: 4,146
|
|
I think you are missing the grant connect statement to the endpoints.
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 1,895,
Visits: 4,146
|
|
| 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.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, October 22, 2009 12:45 AM
Points: 130,
Visits: 306
|
|
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
|
|
|
|
