--------------------------------------------
-- full & transaction log backup and norecovery mode
--------------------------------------------
-- principal server
backup database mirrortest to disk = 'd:\mssql\share\mirrortest.full'
backup log mirrortest to disk = 'd:\mssql\share\mirrortest.log'
-- mirror server
restore database mirrortest from disk = '\\ip\share\mirrortest.full' with norecovery
restore log mirrortest from disk = '\\ip\share\mirrortest.log' with norecovery
--------------------------------------------
-- mirror mirroring endpoint add
--------------------------------------------
CREATE ENDPOINT [Mirroring] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)
--------------------------------------------
-- principal mirroring endpoint add
--------------------------------------------
CREATE ENDPOINT [Mirroring] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)
--------------------------------------------
-- mirroring patner setting (mirror server) partner ip
--------------------------------------------
alter database mirrortest set partner = 'tcp://ip:5022'
--------------------------------------------
-- mirroring patner setting (principal server) partner ip
--------------------------------------------
alter database mirrortest set partner = 'tcp://ip:5022'
--------------------------------------------
-- mirroring start (principal server)
--------------------------------------------
alter database mirrortest set safety full
--------------------------------------------
-- ** mirroring off
--------------------------------------------
alter database mirrortest set partner off
--------------------------------------------
-- ** mirroring failover
--------------------------------------------
alter database mirrortest set partner failover
--------------------------------------------
-- ** mirroring suspend
--------------------------------------------
alter database mirrortest set partner suspend
--------------------------------------------
-- ** mirroring suspend
--------------------------------------------
alter database mirrortest set partner resume
--------------------------------------------
-- ** mirroring endpoint
--------------------------------------------
SELECT name, port FROM sys.tcp_endpoints;
--------------------------------------------
-- ** mirroring status
--------------------------------------------
select
db.name,
db.state_desc,
dm.mirroring_role_desc, --**
dm.mirroring_state_desc, --**
dm.mirroring_safety_level_desc, --**
dm.mirroring_partner_name,
dm.mirroring_partner_instance
from sys.databases db
inner join sys.database_mirroring dm
on db.database_id = dm.database_id
where dm.mirroring_role_desc is not null
order by db.name
--------------------------------------------
-- ** go home~
--------------------------------------------
ALTER DATABASE [databaseName] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
