Share via

Adding Secondary Data File To Mirrored Database Steps

  • Article

Steps to add secondary data file to Mirrored Database

1. Make sure mirrored database is in synchronized state using below query - at principal server

SELECT db_name(database_id) as database_name, mirroring_state_desc,
mirroring_role_desc,  mirroring_partner_name,  mirroring_partner_instance
FROM sys.database_mirroring where mirroring_state_desc = 'SYNCHRONIZED'

2. Disable backup jobs at principal server if any

3. Run the below command to stop the mirroring -- at principal server

  Alter database MirrorNew set partner off

Now you can observe db mirroring will be removed and mirror server database state becomes restoring

4. Add secondary data file / filegroup to principal server database - at principal server

alter database mirrornew add file
 (name='second_datafile',
  filename='E:\Program Files\Microsoft SQL Server\DATA\MirrorNew_second.ndf',
  size = 50mb)

5. Make sure file got added to principal server database -- at principal server
select * from MirrorNew..sysfiles

6. Generate log backup of principal database  -- at principal server
backup log mirrornew to disk='e:\backups\mirrornewwithsecondary.trn' with init

7. You can verify whether log backup has newly added file or not using the below command
restore filelistonly from disk='e:\backups\mirrornewwithsecondary.trn'

8. Copy the log backup to mirror server

9. Run the below command to verify the file location of the database

select * from sysaltfiles where dbid = DB_ID('mirrornew')

10. Restore the log file on mirror server database using with move option -- at mirror server

restore log mirrornew from disk='e:\backups\mirrornewwithsecondary.trn'
with norecovery, move 'second_datafile' to 'E:\Program Files\Microsoft SQL Server\DATA\MirrorNew_second.ndf'

11. Verify newly created file added to database or nore   -- at mirror server
select * from sysaltfiles where dbid = DB_ID('mirrornew')

12. Reinitiate the mirroring from mirror server -- at mirror server
alter database mirrornew set partner ='tcp://kalyanmirror:5022' (Verify properties of DB-Choose Mirroring) -- It moves database into Inrecovery mode

13. Finally initiate mirroring step from principal server -- at principal server
alter database mirrornew set partner='tcp://kalyanprimary:5023'