Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (26)   |   Related Tips: 1 | 2 | 3 | More > 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


Last Update:






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.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, August 01, 2016 - 2:11:40 AM - Sam Back To Top

 

  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

good.... explanation  Bro.

 


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

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

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

 


Wednesday, March 04, 2015 - 8:15:20 AM - dan Back To Top

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

Nyc post Manvendra..:)


Sunday, May 04, 2014 - 12:08:13 AM - bhargav Back To Top

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

 

For clustering i want some info


Wednesday, March 05, 2014 - 5:33:45 AM - Sathya Back To Top

Nice article...Thanks a lot


Tuesday, February 04, 2014 - 12:50:37 AM - ram Back To Top

Great Explanation Thanks singh is king

 


Friday, January 03, 2014 - 6:36:49 PM - Kamal Back To Top

Good post which helped me alot :)


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

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

Good one...


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

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

Great Post ....keep it up


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

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

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

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


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

 

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

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

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

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
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

 

Very well explained steps... 


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

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

Great Post Manvendra keep it up. . . . . 


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

Great Article ! Keep it up!


Learn more about SQL Server tools