Recently, I came across a file creation issue within database mirroring where the user had disparate file and drive paths for the principal and the mirror servers. SQL Server Books Online is very clear about the requirements for this as detailed in the URLs below;

• Prerequisites, Restrictions, and Recommendations for Database Mirroring - http://msdn.microsoft.com/en-us/library/ms366349.aspx
• Troubleshooting Database Mirroring Deployment - http://technet.microsoft.com/en-us/library/ms189127.aspx

A quote from Books Online: Adding a file during a mirroring session without impacting the session requires that the path of the file exists on both servers. Therefore, if you move the database files when creating the mirror database, a later add-file operation might fail on the mirror database and cause mirroring to be suspended. For information about dealing with a failed create-file operation, see Troubleshooting Database Mirroring Deployment.

Furthermore BOL goes on to say;

Books Online Failed Create-File Operation

To fix the problem:

The database owner must remove the mirroring session and restore a full backup of the filegroup that contains the added file.

The owner must then back up the log containing the add-file operation on the principal server and manually restore the log backup on the mirror database using the WITH NORECOVERY and WITH MOVE options. Doing this creates the specified file path on the mirror server and restores the new file to that location.

To prepare the database for a new mirroring session, the owner must also restore WITH NO RECOVERY any other outstanding log backups from the principal server.

Attempting to add a new file to a database when the mirror file paths are dissimilar will put the mirror session into suspend mode. In this article we will seek to address the steps required when adding a database file to a mirrored database where the servers are using disparate drives\paths. The following pointers indicate the prerequisites for this article;

• database is part of a mirroring session
• SQL Server instances use disparate drive letters and\or paths

This article assumes that you already have a successful and active database mirror session where your SQL Server instances are using dissimilar drives and\or paths. The creation and configuration of a database mirror session are outside of the scope of this article.

For the purposes of this article I have the following drives\paths in use on my SQL Server Principal and Mirror servers.

• Principal Server - D:\Program Files\Microsoft SQL Server\MSSQL10.PRINCIPAL\MSSQL\DATA
• On the Mirror Server - E:\Program Files\Microsoft SQL Server\MSSQL10.MIRROR\MSSQL\DATA

The first step is to add a new filegroup if you require one, do this using

ALTER DATABASE [AdventureWorks] ADD FILEGROUP FG1

Once you have defined the filegroup, you need to switch off the mirroring session as directed by BOL. You will remove the mirror session but leave all configurations in place (TCP endpoints, authentication, etc). Switch off the mirror session as shown below from the principal server;

ALTER DATABASE [AdventureWorks] SET PARTNER OFF

At this point the mirroring session has been switched off and the mirror database is now in the "Restoring" state. Add the new database file to the Principal database using the following:

ALTER DATABASE AdventureWorks ADD FILE (NAME = AdventureWorks_IDX, 
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL10.PRINCIPAL\MSSQL\DATA\AdventureWorks_Idx.ndf', 
SIZE = 200MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) TO FILEGROUP FG1

Now take a transaction log backup on the principal database as follows;

BACKUP LOG AdventureWorks TO DISK = '\\hanode1\backup\AdventureWorks_createnewfile.trn' WITH INIT

Now apply the log backup you have just taken to the Mirror database (remember it's in the "Restoring" state so Transaction Log backups may be applied) using the "WITH MOVE" clause as shown below:

RESTORE DATABASE AdventureWorks 
  FROM DISK = '\\hanode1\backup\AdventureWorks_createnewfile.trn' 
 WITH NORECOVERY,
 MOVE 'AdventureWorks_IDX' to 'D:\Program Files\Microsoft SQL Server\MSSQL10.MIRROR\MSSQL\DATA\AdventureWorks_Idx.ndf'

Important: If you have any existing backup regime in place that may have taken a transaction log backup during the timescale of these works they must also be applied prior to starting the mirror session

You may now restore the mirror session as follows. From the Mirror, specify the Principal using this code. You will need to substitute your server name and port number in each of the following statements.

ALTER DATABASE [AdventureWorks] SET PARTNER = 
'TCP://MyPrincipal.MyDomain.co.uk:5123'

To start the mirror session now, use the following from the principal

ALTER DATABASE [AdventureWorks] SET PARTNER = 
'TCP://MyMirror.MyDomain.co.uk:5124'

To establish a witness connect to principal and use

ALTER DATABASE [AdventureWorks] SET WITNESS = 
'TCP://MyWitness.MyDomain.co.uk:5122'

The mirror session is now set to synchronous High safety mode (safety is on by default) with automatic fail over (specifying a witness). To use High performance mode (asynchronous) do not specify a witness and turn transaction safety off using;

ALTER DATABASE [AdventureWorks] SET PARTNER SAFETY OFF

BOL is very clear about the requirements around mirroring and the file paths\drives. The above should give a clearer indication of what needs to be done to support a mirror session where the file paths and\or drives do not match on the Principal and Mirror servers. As always, test this in your offline\virtual environment before tackling the Production systems. Happy mirroring!