How to add a database file to a mirrored SQL Server database

By:   |   Comments (27)   |   Related: 1 | 2 | 3 | > Database Mirroring


Problem

When using Database Mirroring and if you have not placed your database files for your principal database and mirrored database on an identical path then adding a database file to the principal database is quite different than the normal process. In this case, when you add a database file to a database, your mirroring configuration will be suspended because SQL Server will not be able to create that file on the mirrored server. This will not allow the databases to be in sync and will force mirroring to go into a suspended state.

If the complete path (including drive letter and folder names) for the database files exists on both the principal and mirrored server then you can follow the normal process, but if the paths are different then you would have to follow this process to add a database file to a mirrored database.

In this tip I will describe step by step how to add a database file for a mirrored database that has different drives and/or paths.

Solution

At a high level these are the steps we will take to add a new database file for a mirrored database that has different file paths on the principal and mirror.  First remove the mirror partner, then create the database file on the principal server. After that I will take a log backup and restore it on the mirrored server using the WITH MOVE option. Once the restore is done, I will re-establish database mirroring.

NOTE: DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTING IN LOWER-LIFE CYCLE ENVIRNOMENTS

Steps to add new database file to mirrored database

Step 1

First, we should check the mirroring configuration and partner status. Run the below command on the principal server to get this information.  Here we have used database id 5 for our database, you can find your database id for your mirrored database using "sp_helpdb".

SELECT (SELECT DB_NAME(5))AS DBName,database_id,mirroring_state_desc,
mirroring_role_desc,mirroring_partner_name,mirroring_partner_instance
FROM sys.database_mirroring
WHERE database_id=5

Find the mirroring partners and status

Step 2

We can see the mirroring status is synchronized and its mirror partner name. Before moving ahead, we should 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.

Now we will remove the mirror partner to initiate this file creation process. Run the below command on the principal server to break the mirror.

ALTER DATABASE AdventureWorks2008R2 SET PARTNER OFF

Break database mirroring to start the process

Now you can again check the mirroring configuration with the help of the SQL code in step 1. Once you run step 1 again, the output should be like the screenshot below. You can see the status and partner names are showing NULL, meaning the mirroring configuration is broken. Also your database on the mirrored server will be in a restoring state.

Check Mirroring status after breaking mirroring configuration

Step 3

Next, create your database file on the principal server. I ran this statement to create a secondary database file on the principal server. You can use the GUI method as well to create this database file.

ALTER DATABASE AdventureWorks2008R2 ADD FILE (NAME = AdventureWorks2008R2_Data2, 
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data2.ndf', 
SIZE = 1000MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

Create new database file on your principle server

Step 4

Once you have added a new database file on your database, run a log backup that will be restored on the mirrored server.

BACKUP LOG AdventureWorks2008R2
TO DISK = 'C:\AdventureWork2008R2_25Nov20121229.trn'
WITH INIT

Run Log backup

Step 5

Before restoring the log backup on the mirrored server, you can 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 = 'C:\AdventureWork2008R2_25Nov20121229.trn'

Restore filelistonly to verify newly created file is added in backup or not

We can see that the newly created file is captured in the log backup file, so go ahead and restore this log backup on the mirrored server using the NORECOVERY and MOVE options. We are using the MOVE option to place the file in a different location on our mirrored server.

RESTORE DATABASE AdventureWorks2008R2 
FROM DISK = 'C:\AdventureWork2008R2_25Nov20121229.trn' 
 WITH NORECOVERY,
 MOVE 'AdventureWorks2008R2_Data2'
  TO 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MANVENDRA\MSSQL\DATA\AdventureWorks2008R2_Data2.ndf'

Restore created Log backup on mirrored server

Step 6

Now the principal database and mirror database have the new database file. Now we should re-establish mirroring between the servers for this database. First, add the partner server on the mirror server. The below SQL code will add the principal server on the mirror server.

ALTER DATABASE [AdventureWorks2008R2] SET PARTNER = 
'TCP://PRINCIPALSERVERNAME.DOMAIN.com:5022'

Add principle to mirror server

Step 7

Now add the partner server on the principal. Run the below SQL code to add the mirror server to the principal server to resume mirroring.

ALTER DATABASE [AdventureWorks2008R2] SET PARTNER = 
'TCP://MIRRORSERVERNAME.DOMAIN.com:5023'

Add principle to mirror server

Step 8

Once the above commands are successful, you are done with your task to add a database file to a mirrored database.

We can check and verify whether the mirroring configuration is established between both databases. Run this SQL statement along with sp_heflpfile on the principal server.

SELECT (SELECT DB_NAME(5))AS DBName,database_id,mirroring_state_desc,
mirroring_role_desc,mirroring_partner_name,mirroring_partner_instance
FROM sys.database_mirroring
WHERE database_id=5
GO
USE AdventureWorks2008R2
go
sp_helpfile

Verify Mirroring and database file

You can also verify on your mirrored server after a failover to check the database properties for the mirrored database.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Saturday, April 21, 2018 - 6:55:16 PM - Versd Back To Top (75750)

 

 I want this database adding the process to use it. In the mirror SQL, I find some errors and then I use this steps and I succeed. Thank you so much for this easy useable solutions. 

Monday, August 1, 2016 - 2:11:40 AM - Sam Back To Top (43016)

 

  Hi

If i have server A and B and all of them have the same database, Can i use it to synchronize data in B  when i change in any table or filed in server A instantly ? 

My regards

 

Thursday, July 28, 2016 - 1:03:06 AM - uga Back To Top (42992)

good.... explanation  Bro.

 

Wednesday, February 17, 2016 - 10:33:05 AM - Akram Back To Top (40703)

My priniciple database schedueled for evert 1 hour T.log backup . In this case restoring last T.log backup  backup doesn't work . Is there any other way to add file to principle db ??

 

Friday, December 18, 2015 - 11:54:09 AM - Giampaolo Back To Top (40281)

What if I want to remove an NDF file on a principal server with mirroring enabled?

 

Wednesday, March 4, 2015 - 8:15:20 AM - dan Back To Top (36442)

Step 5, I think it should be RESTORE LOG, instead of RESTORE DATABASE.......

Thursday, July 10, 2014 - 6:55:43 AM - Bikram Back To Top (32627)

Nyc post Manvendra..:)

Sunday, May 4, 2014 - 12:08:13 AM - bhargav Back To Top (30603)

Nice Article thanks for providing the step by step how to reach the goal.

 

For clustering i want some info

Wednesday, March 5, 2014 - 5:33:45 AM - Sathya Back To Top (29645)

Nice article...Thanks a lot

Tuesday, February 4, 2014 - 12:50:37 AM - ram Back To Top (29321)

Great Explanation Thanks singh is king

 

Friday, January 3, 2014 - 6:36:49 PM - Kamal Back To Top (27956)

Good post which helped me alot :)

Monday, September 16, 2013 - 5:52:21 AM - sunny Back To Top (26805)

I have a one question..''What is end point for the data base mirroring''?

Monday, May 20, 2013 - 11:22:47 PM - santosh Back To Top (24053)

Good one...

Thursday, April 11, 2013 - 8:32:11 AM - DivineFlame Back To Top (23301)

Perry Whittle has written an excellent article on same subject more than a year ago.

http://www.sqlservercentral.com/articles/Database+Mirroring/72294/

I was assuming that this tip will provide something new. Anyway, nice post.

Thursday, February 28, 2013 - 1:24:09 AM - ravinder Back To Top (22465)

Great Post ....keep it up

Thursday, January 17, 2013 - 7:36:29 AM - Jeremy Kadlec Back To Top (21510)

Zen,

Thank you for the comment.  Per Manvendra the tip has been updated.

Thank you,
Jeremy Kadlec
Community Co-Leader

Tuesday, January 15, 2013 - 7:50:58 AM - Manvendra Back To Top (21453)

Manu- Doesnot matter, you can use 'Restore Database' syntax as well to rerstore this operation.

Tuesday, January 15, 2013 - 3:34:30 AM - Manu Back To Top (21447)

I think RESTORE DATABASE AdventureWorks2008R2 must be replaced with Restore Log...

Monday, January 14, 2013 - 6:52:46 AM - Lucho Back To Top (21436)

 

Hello, thanks for this post!

What is the normal procedure to add a db file to a mirrored db?

 

Lucho

Friday, January 11, 2013 - 12:51:16 PM - Jay Back To Top (21409)

I mean to say without breaking.

.................................i wish if there was included feature to add file without breaking mirror.

Thanks

Friday, January 11, 2013 - 12:49:15 PM - Jay Back To Top (21408)

Nothing new, you have to break the mirror to add the file on the same path\folder or different.

Thats by design, i wish if there was included feature to add file with breaking mirror.

As Mirroring is deprecated from SQL 2012 and you can add files while Always ON is configured without breaking. make sure files size. path & folder are same.

Thanks

Friday, January 11, 2013 - 12:10:31 PM - PRASAD YANGAMUNI Back To Top (21407)

This is excellent post ... I awaiting for this. keep posting like these articles. 

 

Thanks,

Prasad

Friday, January 11, 2013 - 10:54:09 AM - ZEN Back To Top (21405) 
On step 7：
 
ALTER DATABASE [AdventureWorks2008R2] SET PARTNER = 
'TCP://PRINCIPALSERVERNAME.DOMAIN.com:5023'
 
 
Should it be: 
 
ALTER DATABASE [AdventureWorks2008R2] SET PARTNER = 
'TCP://MirroredSERVERNAME.DOMAIN.com:5023'
 
Thanks for your post BTW.  We had exactly the same issue.  There's one white paper on Technet detailed the solution exactly the same here. 
 

Friday, January 11, 2013 - 10:22:12 AM - INS Back To Top (21403)

 

Very well explained steps... 

Friday, January 11, 2013 - 9:50:13 AM - SunGlassesTK12 Back To Top (21402)

Nice Read.  I thought once you set your hard drive for mirroring everything you put on the hard drive is automatically copied.  Thanks.

Friday, January 11, 2013 - 3:22:06 AM - Tarun Gulati Back To Top (21393)

Great Post Manvendra keep it up. . . . . 

Friday, January 11, 2013 - 2:54:37 AM - Ayyanar Back To Top (21390)

Great Article ! Keep it up!














get free sql tips
agree to terms