Every month I need to maintain the various sliding partition windows on several databases. If you've read some of my previous posts you may remember that this involves adding new files for the upcoming partitions. My company's databases are also logshipped as part of the disaster recovery strategy.
What invariably happens every month is I add new files make the necessary partition changes and then wonder why, a little later in the day, I'm receiving alerts for failed transaction log restores. The logshipped database obviously doesn't "know" about the new file and I end up with an error message like this:
2014-01-09 08:10:18.12 *** Error: Could not apply log backup file 'C:\SQL\Logshipping\PartWindow_20140109080941.trn' to secondary database 'PartWindow_ls'.(Microsoft.SqlServer.Management.LogShipping) ***
2014-01-09 08:10:18.12 *** Error: The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\logshiptest.ndf' cannot be overwritten. It is being used by database 'PartWindow'.
File 'logshiptest' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\logshiptest.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) ***
2014-01-09 08:10:18.16 *** Error: The log backup file 'C:\SQL\Logshipping\PartWindow_20140109080941.trn' was verified but could not be applied to secondary database 'PartWindow_ls'.(Microsoft.SqlServer.Management.LogShipping) ***
2014-01-09 08:10:18.16 Deleting old log backup files. Primary Database: 'PartWindow'
2014-01-09 08:10:18.16 The restore operation completed with errors. Secondary ID: '32ef56ad-d8ee-4437-b3a0-e5658f6d2d4b'
Fixing this is not difficult, it is just a bit fiddly. There is a really good post on mssqltips.com explaining the steps necessary to fix the problem. Basically, I've gotten bored by it all and decided to write a stored procedure that can more easily fix the problem.
You need to pass the logshipped database name and the file path to where the transaction log backups are on the failover server. I imagine the bast place for this stored proc is either on the msdb database or an admin database on the failover server. You also need xp_cmdshell to be enabled.
The stored procedure performs the following steps:
- Identifies the last file that was successfully restored
- Identifies the subsequent file - the first file to fail
- Identifies the new logical file name
- Identifies the physical location of the most recent database file
- Creates and executes the restore log script with the appropriate WITH MOVE clause
- Will move any number of files that have been added to the source database
CREATE PROC dbo.asp_LogShippingRestoreNewFile
(@DBName SYSNAME ,@Filepath VARCHAR(260))ASBEGINSET nocount ONDECLARE @FailedLogFile NVARCHAR(260)DECLARE @LastRestoredFile NVARCHAR(260)DECLARE @LogicalName SYSNAMEDECLARE @PhysicalName NVARCHAR(260)DECLARE @MoveToLocation NVARCHAR(260)DECLARE @sql VARCHAR(MAX)DECLARE @dbfilelist TABLE(LogicalName VARCHAR(256) ,PhysicalName NVARCHAR(260) ,Type CHAR(1) ,FileGroupName VARCHAR(256) ,Size NUMERIC (25,0) ,MaxSize NUMERIC (25,0) ,FileId NUMERIC (25,0) ,LSN NUMERIC (25,0),DropLSN NUMERIC (25,0),UniqueID UNIQUEIDENTIFIER ,ReadOnlyLN NUMERIC (25,0),ReadWriteLSN NUMERIC (25,0),backupsize NUMERIC (25,0) ,sourceblocksize INT ,FileGroupID INT ,LogGruopGUID UNIQUEIDENTIFIER ,DiffBaseLSN NUMERIC (25,0) ,DiffBaseGUID UNIQUEIDENTIFIER ,isreadOnly BIT ,IsPresent BIT ,TDEThumbprint VARBINARY(32))DECLARE @CopiedFiles TABLE(id INT IDENTITY(1, 1) ,filepath VARCHAR(260))SELECT @LastRestoredFile = last_restored_fileFROM msdb.dbo.log_shipping_secondary_databasesWHERE secondary_database = @DBNameSET @sql = 'xp_cmdshell ''dir "' + @Filepath + '" /b /s /o'''INSERT INTO @CopiedFilesEXEC ( @sql)SELECT TOP 1@FailedLogFile = filepathFROM @CopiedFilesWHERE id > ( SELECT idFROM @CopiedFilesWHERE filepath = @LastRestoredFile)ORDER BY idINSERT INTO @dbfilelistEXEC ( 'RESTORE filelistonly FROM DISK = N''' + @FailedLogFile + '''');SELECT @PhysicalName = LEFT(physical_name,LEN(physical_name) - CHARINDEX('\',REVERSE(physical_name),1) + 1)FROM sys.master_files AS mfJOIN sys.databases AS d ON d.database_id = mf.database_idWHERE d.name = @DBNameORDER BY file_id DESCSET @sql = (SELECT TOP 1 'RESTORE LOG ' + @DBName + ' from disk = ''' + @FailedLogFile+ ''' WITH' + substring((SELECT ', MOVE ''' + a.LogicalName + ''' TO ''' + @PhysicalName + a.LogicalName + '.ndf'', NORECOVERY'FROM @dbfilelist AS aWHERE a.Type = b.TYPEAND a.LogicalName NOT IN (SELECT mf.name FROM sys.master_files AS mfJOIN sys.databases AS d ON mf.database_id = d.database_id)FOR XML PATH('')),2,65536) AS a1 FROM @dbfilelist AS b)PRINT @sqlEXEC (@sql)ENDGO
Feel free to let me know if this has been helpful and how it has worked for you.