SQL Server 2005/2008 : Add data 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
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
