Adding data file to Mirroring database
What is Mirroring?
Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible
Issue:
When business needed to add NDF (new data file) to database which is participating mirroring. If paths are same on principal and mirror db for NDF will be seamless. If we don’t follow below steps mirroring will get fail.
Steps to follow:
- Break the Mirroring by running below command
Command :“ALTER DATABASE DBNAME SET PARTNER OFF”
- To add data file go to database properties on principal database–>files–>click on add
- Take Transaction Log backup at principal database.
- Restore the log file with move:
RESTORE log DB2001 FROM Disk=’D:\SQL_Backups\DB2001_tlog.trn’
with norecovery, MOVE ‘db_ndf’ TO ‘D:\Data file\Db_ndf.ndf’
Note: With no recovery —> Very Important…!!
- Once restored the log backup successfully, Reconfigure DB Mirroring again
Recent Posts
