Friday, 10 January 2014

An SP to Fix Logshipping After Adding a New Database File

UPDATE: 22 Jan 2014 - Fixed some bugs, added multi-file support and added a few notes

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:

Message
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)
    )
AS
    BEGIN
        SET nocount ON
        DECLARE @FailedLogFile NVARCHAR(260)
        DECLARE @LastRestoredFile NVARCHAR(260)
        DECLARE @LogicalName SYSNAME
        DECLARE @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_file
        FROM    msdb.dbo.log_shipping_secondary_databases
        WHERE   secondary_database = @DBName
        SET @sql = 'xp_cmdshell ''dir "' + @Filepath + '" /b /s /o'''
        INSERT  INTO @CopiedFiles
                EXEC ( @sql
                    )
        SELECT TOP 1
                @FailedLogFile = filepath
        FROM    @CopiedFiles
        WHERE   id > ( SELECT   id
                       FROM     @CopiedFiles
                       WHERE    filepath = @LastRestoredFile
                     )
        ORDER BY id

        INSERT  INTO @dbfilelist
                EXEC ( '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 mf
                JOIN sys.databases AS d ON d.database_id = mf.database_id
        WHERE   d.name = @DBName
        ORDER BY file_id DESC
       
              SET @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 a
                                  WHERE a.Type  = b.TYPE
                                  AND a.LogicalName NOT IN (SELECT mf.name FROM sys.master_files AS mf
                                  JOIN sys.databases AS d ON mf.database_id = d.database_id)                 
                                  FOR XML PATH('')),2,65536) AS a1 FROM @dbfilelist AS b
              )

              PRINT @sql
        EXEC (@sql)

    END
GO



 Feel free to let me know if this has been helpful and how it has worked for you.

1 comment:

Anonymous said...

Thomas superb!!!
Thank you so much.
It saved time :)