Friday, February 10, 2012

SQL Server 2005/2008 : Add data or log file to log shipping primary database


Today, I had to add a new data file to the primary databases which was configured for Log shipping.
As soon as I edited the database properties and added a new data file, the log shipping started to fail with the following error.

*** Error: Directory lookup for the file "P:\Datafiles\Sample_archive3.mdf" failed with the operating system error 3(The system cannot find the path specified.).
File 'Sample_archive3' cannot be restored to 'P:\DataFiles\Sample_archive3.mdf'. Use WITH MOVE to identify a valid location for the file.

The problem was that the new data file that was added on primary needs to be added to the secondary database too. But this cannot be done with the regular RESTORE command that is used by the Log shipping restore job. So as suggested in the ERROR I had to use the WITH MOVE option in the RESTORE command. I checked what was the next log backup file that had to be restored and I manually executed the following RESTORE command to fix the problem.

RESTORE LOG [SAMPLE]
FROM  DISK = N'E:\TranlogBkups\SAMPLE_20101101162500.trn'
with move 'SAMPLE_archive3' to 'E:\MSSQL\Data\SAMPLE_DATA22.mdf',
  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

While making this change I also discovered the RESTORE FILELISTONLY command which lists the logical file name of all the data and log files that are part of the database backup. So in case you had lost the database that was backed up and now wanted to restore it to a different location then you can use the command to find out all the data and log files.
You could then proceed to restore the database and move all the old logical files to the new physical paths.

You could run the command as follows
RESTORE FILELISTONLY FROM DISK = 'E:\TranlogBkups\SAMPLE_20101101162500.trn' WITH FILE = 1
GO

2 comments:

  1. Could you please let me know for which version you are talking about?, Because i dont find any issue with SQL Server 2005 Standard Edition with SP4 on it.

    ReplyDelete
  2. I have noted this issue with both SQL Server 2005 and 2008. I have provided the error that comes up the in Log shipping job history for the restore job.
    If you add a file on the primary database then you have to manually run a restore command on the secondary to add that file.

    ReplyDelete