Fixing SQL Server Log Shipping after new DB file has been added to Primary

If you are maintaining a standby copy of your PRD DB enabled with log shipping mechanism, and it breaks after a data file was added to your Primary database, refer to the below to overcome this kind of situation.

You might see restore errors like these on your secondary DB server-

Error: Could not apply log backup file ‘\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’ to secondary database ‘DBName’.(Microsoft.SqlServer.Management.LogShipping) ***  2015-11-12 13:30:11.98 *** Error: Directory lookup for the file “E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.ndf” failed with the operating system error 3(The system cannot find the path specified.).  File ‘DBName_Data3’ cannot be restored to ‘E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.ndf’. Use WITH MOVE to identify a valid location for the file.  Problems were identified while planning for the RESTORE statement. Previous messages provide details.  RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Check which transaction log has been last applied on Secondary DB (Via T-SQL or Log Shipping Status Report).
You may use this t-sql to get the status:

SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC
Now to validate which secondary file was lately added to your DB, use the below T-SQL:
RESTORE FILELISTONLY FROM DISK=’\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’

And the final step would be to restore the transaction log with norecovery and move option:
RESTORE log DBName FROM Disk=’\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’
with norecovery,
MOVE ‘DBName_Data3’
TO ‘E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.ndf’

Once DB is restored, you should be able to restart your log shipping restore automated jobs.

Happy learning!

Cheers!
Aman Kharbanda

Advertisements

Avoid a reboot on your server while installing multiple updates or patches

Ever wondered how cool would it be to avoid server reboot prompts should you be installing multiple updates or deploying patches on your Windows Servers? I’m sure many of you must have encountered this error sometime before-

“This error indicates that a restart from a previous install or update is pending and the system must be restarted before setup can continue”

Here’s one trick up your sleeve which you may use to avoid such errors-HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations

If PendingFileRenameOperations registry key has any value then we got to clean it, in order to overcome this issue.

To delete the PendingFileRenameOperations registry key value-

  1. Open a registry editor, such as Regedit.exe or Regedt32.exe.
  2. Navigate to HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\
  3. In the right navigation pane, right-click the PendingFileRenameOperations key and select Delete.
  4. Close Registry Editor.

Happy learning!!

Cheers!
Aman Kharbanda