How to add a database file to a mirrored SQL Server database when database files are quite different (different file paths)

1-      check the mirroring configuration and partner status

SELECT (SELECT DB_NAME(DB_ID('DB'))) AS DBName,database_id,mirroring_state_desc,

mirroring_role_desc,mirroring_partner_name

,mirroring_partner_instance

FROM sys.database_mirroring

WHERE database_id=DB_ID(' DB')

 

 

2-      Disable any SQL Server backup jobs to reduce any futher complexity. Otherwise, if any log backup occurs in between you will need to restore all log backups on the mirrored server before establishing the database mirror.

 

3-      Remove the mirror partner to initiate this file creation process

ALTER DATABASE DB Name SET PARTNER OFF

 

 

4-      Check the mirroring configuration after removing the mirror with step 1 code, the output should be like the screenshot below.

 

 

5-      Create your database file on the principal server.

 

6-      Run a log backup.

 

7-      Check whether your newly created file is captured in the log backup. Run the RESTORE FILELISTONLY statement to get this info.

 

RESTORE FILELISTONLY FROM DISK = 'D:\Lbak.trn'

                                                                                                             

 

8-      Restore it on the mirrored server.

ESTORE DATABASE DB

FROM DISK = 'D:\Lbak.trn'

 WITH NORECOVERY,

   MOVE 'Logical file name' TO 'Mirrored Physical File path'

,  MOVE 'Logical file name' TO 'Mirrored Physical File path'

,  MOVE 'Logical file name' TO 'Mirrored Physical File path'