Moving Database with Mirroring and Log Shipping

For disk maintenance or relocation reason, sometimes you need to move user database data and log files to a new disk location. Below are the procedures to move a primary database with Mirroring and Log Shipping enabled:

1. On the new location folder, grant enough access permission to the SQL Server service account.

2. Disable the Log Backup Agent Job for the database in primary server, the agent job name should be LSBackup_<DBName>.

3. Wait for the log backup agent job completion if it's running, in order to prevent any transaction log not yet transferred to the mirroring secondary database but accidentally being truncated in the primary database (otherwise the mirroring secondary database must be reinitialized by a full backup later on).

4. Drop mirroring by sql statement ALTER DATABASE DBName SET PARTNER OFF;

5. Move data and log files:
USE master;
GO
-- Return the logical file name.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'DBName');
GO
-- Offline
ALTER DATABASE DBName SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
-- Physically move the file to a new location.
-- In the following statement, modify the path specified in FILENAME to
-- the new location of the file on your server.
-- *** Make sure the SQL Service Account has security right to access the new directory ***
ALTER DATABASE DBName
    MODIFY FILE ( NAME = DB_Data_LogicalName,
    FILENAME = 'New Location\DB_Data.mdf');
GO
ALTER DATABASE DBName
    MODIFY FILE ( NAME = DB_Log_LogicalName,
    FILENAME = 'New Location\DB_Log.ldf');
GO
-- Online
ALTER DATABASE DBName SET ONLINE;
GO
--Verify the new location.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'DBName');

6. Re-Create Mirroring (for example mirroring endpoint port number 5022):
  a) On Standby: ALTER DATABASE DBName SET PARTNER = 'TCP://PrimaryServer:5022';
  b) On Primary: ALTER DATABASE DBName SET PARTNER = 'TCP://StandbyServer:5022';

7. Re-Enable the Log Backup Agent Job.

8. Check Mirroring and LogShipping resumed.