Adding additional datafile to the Primary logshipping database!

Recently I got an Opportunity to add an additional datafile to Primary log shipping database on two different file groups,because Identified that the Primary database was full with two file groups are full & the files which resides in the Mount points there were no space on it.


Hence Plan to add additional data files to the two file groups one is called Primary and other one is called DATA1.

So the Mistake what I done was as mentioned below-

1)Didn't get in to the Mind what will Happens to the secondary database if I add additional datafile to the primary(how ever I already know that how ever I didn't check

the status of Secondary database Because I was not sure that Logs will applies over there Because all the Logs were applying from other 3rd part tool i,e Control-M and the control-m job was scheduled to run every 6 Hours for the restoration activity to the secondary database i,e logs will apply to the secondary databases using control-M Job.)?


2)How can I Identified what was the last restoration happened on the secondary database?


So I added additional files to the Primary database for two FG's with unrestricted growth using other mount point drives,so finished my work,next day the application team sent mail

that the secondary Database is not synch with the Primary and in the control-m logs they found errors as mentioned below-

ABC_BANKING: [Sev:CRITICAL] Job "db_restore_db_xyx.pl" (ABCDBRESESTATE_050) on Server XXXXXXXXX ABENDED: 1 <Batch>
On checking the logs we see that the restoration was successful till 08/30 13.25, however the next file to be processed caused the failure with the below error,
Processing database xyx......
Terminating all active users in database XYZ......
The result of the kill command is:
:
Transaction Log file being processed is F:\Backup\XYX\CURR\xyx_backup_201108301325.trn.
The result of the Restore command is:
Msg 5133, Level 16, State 1, Server XXXXXXX, Line 1
Directory lookup for the file "F:\Data\Data11\xyx_Primary_2.ndf"
failed with the operating system error 3(The system cannot find the path
specified.).
Msg 3156, Level 16, State 3, Server XXXXXXX, Line 1
File 'xyx_DataPrimary_2' cannot be restored to
'F:\Data\Data11\xyx_Primary_2.ndf'. Use WITH MOVE to identify a valid
location for the file.
Msg 5133, Level 16, State 1, Server XXXXXXX, Line 1
Directory lookup for the file "F:\Data\Data11\XYX_data1_2.ndf" failed
with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Server XXXXXXX, Line 1
File 'estaementes_data1_2' cannot be restored to
'F:\Data\Data11\estaementes_data1_2.ndf'. Use WITH MOVE to identify a valid
location for the file.
Msg 3119, Level 16, State 1, Server XXXXXXX, Line 1
Problems were identified while planning for the RESTORE statement. Previous
messages provide details.
Msg 3013, Level 16, State 1, Server XXXXXXX, Line 1
RESTORE LOG is terminating abnormally.
There was an error in restoring database xyx.
The .trn file to be processed are located in \\XXXXXXX\F$\Backup\xyx\CURR\ but the log show that the job failed while trying to access the F:\Data\Data11\xyx_Primary_2.ndf which is on 01n1 server.



So here is the action Plan what you have to do
-------------------------------------------------
Since on the other server the Mount points/Drives are different Name, hence the error above reported with the clear details.



1.You can execute the below query to know what is the last trn applied to the secondary database.

USE MSDB
GO

SELECT TOP 1 *
FROM RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = 'DBNAME')
ORDER BY RESTORE_DATE DESC


2.Go to the Primary database identify the next trn to be apply to the secondary.

(Simple way is ,as soon as you added the additional data files Immediately go to the Physical file location check the modified date/time of the file, then identify the

trn and issue the query restore filelistonly from disk='<Drive>:\XX\Xyz_backup_YYYYMMDDHHMM.trn' ---here you can verify what files exists in the trn .

as in our case the next day we have identified that secondary is out of sync we can execute the below query-

select database_name, backup_finish_date, type, [name], user_name, first_lsn, last_lsn, * from backupset
where database_name = 'XYZ'

to get the LSN Numbers --then using this you can compare with the Secondary.

or

check the SQL errorlogs on the secondary SQL server.


NOTE ->In our case the Primary database on different Domain and secondary database is in different domain & MSDB history totally different , how ever the good thing is that we have Control-m logs so that we can  easily identify the next trn.




The steps are as mentioned below-

1.Log on to the  Secondary server
2.Open SSMS
3.Open New query
4.Check if the Database is Single user mode (i,e always you need to check what status of the secondary database)
If it is in single user mode execute the below query to bring it to multi -user


alter database XYZ set multi_user
go;

5.Go through the restore logs to ensure that the next log to be restored is 'Xyz_backup_YYYYMMDDHHMM.trn "

6.create folder by name "Secondary" in X:             ----Optional

in new query analyser execute the below script(here we restoring trn mannually)


RESTORE log xyZ
   FROM DISK = 'F:\Backup\XYZ\CURR\Xyz_backup_YYYYMMDDHHMM.trn'
   WITH MOVE 'XYZ_DataPrimary_2' TO 'X:\Secondary\XYZ_Primary_2.ndf',
   MOVE 'XYZ_data1_2'  TO 'X:\Secondary\XYZ_data1_2.ndf',
 standby ='F:\Backup\Secondary\UNDO\XYZ_Undo.tuf'
GO

7.Ensure that log is restored and database comes back to standby mode.

8.execute sp_helpfile ->check the new files exists.

9.inform the application team to release their jobs.

So becareful when you are adding addtioanl datafile to prmiary database
i,e you need to Immediately apply next trn to manually on secondary database with Move option if it is differnt drives on secondary(Please check what the status of the secondary database.).

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++