How to Add Data File to Mirrored SQL Server Database


Introduction

The procedure of adding an SQL Server database to a principal database differs a lot relatively when database files are not placed in the mirrored and principal database on a similar path during Database Mirroring process. In the discussed scenario, mirroring configuration on your SQL Server may be suspended on adding a database to another one. This is because; the server won’t be able to generate the file on the mirrored server end. The condition will prevent the database from being synchronized resulting forcing the mirroring procedure to be put under a suspended state.

Nevertheless, if the correct and complete database path is available on both; the mirrored and principal server then the normal procedure can be executed. However, on having different paths, the user would want to know how to add data file to mirrored SQL Server database. Therefore, the following segment discusses the same in a sequential manner.

Adding Data to Mirrored SQL Database

An overview of the entire procedure can be summed up in the following four points:

  1. Remove the existing mirroring partner
  2. Create a new file or file group on the primary server
  3. Take transaction log file backup and restore the same on the mirror server with the help of WITH MOVE option
  4. Once done, database mirroring has to be re-established

Steps to Add Data File to Mirrored SQL Database

Step 1: Verify the mirroring configuration status and that of the partner too. Execute the following TSQL statement on the primary / principal server.

NOTE: Database ID 5 has been used for representational purpose. To find the ID of your mirrored database, execute – ‘sp_helpdb’.

mirrored-sql-database-1

The provided TSQL statement will help you find out the mirroring status as synchronized. In addition, you will also get to know the mirrored partner name as mentioned.

NOTE: Disable any/all backup jobs being executed on the server before advancing to the next stage. This helps prevent complexity from being encountered in the process. In case of any log backup, its restoration needs to be done on the mirrored server prior to database mirror is re-established.

Now remove the mirrored partner and you will now be able to continue creating the secondary database.

mirrored-sql-database-2

Executing the first TSQL mentioned above will now display the status and partner name – NULL. This will confirm that configuration of mirroring is currently broken. The database at the mirrored server end will now come under a restoring state.

Step 2: Now on the principal server, create a secondary file or file group. Execute the given below TSQL.

NOTE: You can additionally use the GUI wizard via SSMS for the secondary database creation.

mirrored-sql-database-3

Step 3: Generate a backup for Transaction Log file from the primary server to be able to restore it on the mirror server.

mirrored-sql-database-4

TIP: Verify whether the secondary file/file group creation is recorded by the transaction log on the primary server by performing a restoration of the file list.

mirrored-sql-database-5

Now on the mirror server, perform transaction log backup restoration with options NORECOVERY and MOVE used in the process. MOVE must be used for placement of the file to be done at a different location on a mirrored server.

mirrored-sql-database-6

Step 4: Mirror session can now be re-established as both the primary and mirrored servers now have the newly generated secondary database. Now assign a partner on the mirrored server and then do the same on the primary server as well.

On the Mirrored Server:

mirrored-sql-database-7

On the Primary Server:

mirrored-sql-database-8

On performing the provided steps in the exact sequential order, you should now be having the secondary file moved successfully to the mirrored server. The mirrored server will work as it did previously and you now know how to add a database file to a mirrored SQL Server database.