Configure database mirroring for multiple databases via TSQL or Powershell when mirror is already restoring

Question

We are trying to automate the mirroring set up for multiple databases whose mirror is already in RESTORING state as the set up was part of log shipping

So when we switch from log shipping to mirror I am unable to find a way using TSQL or Powershell to set this mirroring between Primary and Secondary as most of the scripts on the internet are taking full backups initially and restoring. But we don't need to implement that as DB is already restoring.

We can set this up using GUI but it's time consuming when doing so for 20 DBs during migration. Please help with any mirroring automation that will set up for multiple databases having their mirror already set up with databases in restoring mode.

Answer 1

Run the Alter Database command to add database to mirroring session

--Run on Mirror
ALTER DATABASE Test_DB 
    SET PARTNER = 'TCP://Mirror_Me:5022'

Run on both mirror and principal. Remember to change the port numbers appropriately

--Run on Principal                                                                                     
ALTER DATABASE Test_DB
    SET PARTNER = 'TCP://Mirror_Me:5023'

Read this for more info.

Adding Additional script for a complete configuration minus the backup and restore process:

--This script allows db mirroring to be setup iwth a witness. 
--Edited to skip the backup and restore process to the mirror
--Make right configuraton changes accordingly

--On A server
    CREATE ENDPOINT [Endpoint_Mirroring] --change endpoint name as required
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022)  --change port number as required. default is 5022
    FOR DATA_MIRRORING
    (ROLE = ALL);

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain\login_name] --change to required domain account

--On B server
    CREATE ENDPOINT [Endpoint_Mirroring] --change endpoint name as required (good to keep the same name)
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022) --change port number as required. 
    FOR DATA_MIRRORING
    (ROLE = ALL);

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain\login_name] --change to required domain account


--Change for current db
    ALTER DATABASE TEST_DB SET PARTNER = 'TCP://servername.domainname.net:5022'  --change the database/server name
    GO

--On B server
    ALTER DATABASE TEST_DB SET PARTNER = 'TCP://servername.domainname.net:5022' --change the database/server name
    GO

--To setup Witness on Server C
    CREATE ENDPOINT [Endpoint_Mirroring] --change endpoint name as required (good to keep the same name)
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022) --change port number as required. 
    FOR DATA_MIRRORING
    (ROLE = ALL);

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain\login_name]


--On Principal Server
    ALTER DATABASE Test_DB SET WITNESS = 'TCP://servername.domainname.net:5022' --change the database/server name
    GO

NOTE: This does not contain setting up certificates for encrypted connections on the endpoints/mirroring. It does not hand transfer of logins from principal to mirror. This is outside the scope of this question.

DISCLAIMER: This script is AS-IS and should be thoroughly tested before implementing into a production environment.