Tuesday, 4 March 2014

Modify Logshipping Restore Job

In my previous post I shared a stored procedure that dynamically creates and executes a restore log script in order to ease the recovery of logshipping after one or more files have been added to a logshipped database.

As I described in that post I was running into this problem on a monthly basis due to a sliding partition window scenario I maintain on several databases. After I wrote the stored procedure it was a lot easier to fix the problem after new files were added. But I still needed to wait for the logshipping restore job to fail and then intervene and execute the SP manually to recover from the failure. Being busy, not to mention forgetful, there were times when logshipping would be broken for several hours at a time.

Unfortunately, you cannot pro-actively inform the failover server that new files are on their way. But what finally did occur to me was to modify the job that restores the transaction log to include the stored procedure. In short, I added a second step that is run only if the first step fails. It works a treat.

You can find the stored procedure I wrote in my post An SP to Fix Logshipping After Adding a New Database File. See the screenshots below for details on adding the step to the logshipping restore job.
  • In SSMS expand the SQL Server Agent Jobs tree and double-click on the LSRestore_xxxx job
  • Click on 'Steps' and then click 'New'
  • Add a title, select the database the stored procedure has been deployed to and then enter the EXEC statementinto the 'Command' box, for example: EXEC dbo.LogShippingAutoRestore @DBname = 'AdventureWorks',@Filepath = 'J:\Logshipping\AdventureWorks\'
  • Click on the 'Advanced' tab and modify the 'On success action' to 'Quit the job reporting success' and click 'Ok'

  • Select step 1 and click on 'Edit'
  • Click on the 'Advanced' tab and modify the 'On failure action' to 'Go to step: [2] exec LogshippingAutoRestore' or whatever you named that step and click 'Ok'
  • Now finally, click ok to save the job





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.

Monday, 18 November 2013

Database Snapshot Stored Procedure

We've been trying to tighten up the database release process at work. This involves lots of big and small changes including things like using version control software better, documenting database code as well as putting together some default scripts that run with every release.

It occurred to me that even if you have a good process and set of check lists for the release cycle it is still quite easy to miss out on small but ultimately important steps. Things get hectic, last minute changes are made, etc, etc., and the next thing you know you've forgotten a basic, but important, step like creating a database snapshot or disabling scheduled jobs or revoking user access for the duration of the release. There are probably a dozen other small tasks that can be left out.

As part of this effort to make releases less problematic, I've created the stored procedure below that will create a database snapshot for a specific database. There are three parameters:

  • @db sysname - The name of the database to be backed up
  • @CheckDriveSpace bit
    • 1 = Checks the for the disk with the most space. It will only look for disks that have database files on them already, so the snapshot files won't be placed willy-nilly on the server
    • 0 = Will not run a check and will create the snapshot in the same directory as the database being backed up
  • @DropPreviousSnapshots bit 
    • 1 = Will check for existing database snapshots of the database specified and drop them
    • 0 = Will not check for and drop existing database snapshots
As with all code, I did not put this together without any help. The Powershell section was taken and adapted from myharpreet.blogspot.co.uk. And the section of the script that finds a database file's directory came from stackoverflow.com.

The code for the stored procedure is:
/***************************************************************
UPDATE: Added a check for xp_cmdshell. If it is disabled, 
the SP will enable it and then disable it again at the end
***************************************************************/
USE AdminDB
GO
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'asp_CreateDBSnapshot')
BEGIN
       DROP PROC dbo.asp_CreateDBSnapshot
END
GO
CREATE PROC dbo.asp_CreateDBSnapshot
    (
      @db SYSNAME ,
      @CheckDriveSpace BIT = 0 ,
      @DropPreviousSnapshots BIT = 0
    )
AS
/********************* DEBUG ***************************
        DECLARE @db sysname = 'PartWindow'
              DECLARE @CheckDriveSpace BIT = 0
              DECLARE @DropPreviousSnapshots BIT = 0 ;
--********************* DEBUG ***************************/
    DECLARE @date VARCHAR(20) = CONVERT(VARCHAR(20), GETDATE(), 112)
        + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '');
    DECLARE @directory VARCHAR(200);
    DECLARE @file VARCHAR(50);
    DECLARE @SQL VARCHAR(MAX);
    DECLARE @xpcmdshell int;
       DECLARE @freespace TABLE
        (
          drive CHAR(2) ,
          FreeSpaceMB dec(17, 2) ,
          SizeMB dec(17, 2) ,
          PercentFree dec(17, 2) ,
          DBDirectory VARCHAR(255)
        );
    DECLARE @psinfo TABLE ( data NVARCHAR(100) );

       SET @xpcmdshell = (SELECT CONVERT(INT,ISNULL(value,value_in_use)) AS config_value
                                     FROM sys.configurations
                                     WHERE name = 'xp_cmdshell');
       IF @xpcmdshell = 0
       BEGIN
              EXEC sp_configure 'show advanced options', 1;
              RECONFIGURE;
              EXEC sp_configure 'xp_cmdshell', 1;
              RECONFIGURE;
       END
    IF @DropPreviousSnapshots = 1
        BEGIN
            DECLARE @DropSnapshot TABLE
                (
                  id INT IDENTITY(1, 1) ,
                  DBName SYSNAME ,
                  CreateDate DATETIME
                )
            INSERT  INTO @DropSnapshot
                    ( DBName ,
                      CreateDate
                    )
                    SELECT  name ,
                            create_date
                    FROM    sys.databases
                    WHERE   source_database_id = DB_ID(@db)
            WHILE ( SELECT  COUNT(*)
                    FROM    @DropSnapshot
                  ) > 0
                BEGIN
                    DECLARE @Snapshot SYSNAME
                    SET @Snapshot = ( SELECT TOP 1
                                                DBName
                                      FROM      @DropSnapshot
                                      ORDER BY  CreateDate DESC
                                    )
                    SET @SQL = ( 'DROP  DATABASE [' + @Snapshot + ']' )
                    EXEC (@SQL)
                    DELETE TOP ( 1 )
                    FROM    @DropSnapshot
                    WHERE   DBName = @Snapshot;
                END
        END
    IF @CheckDriveSpace = 1
        BEGIN
            INSERT  INTO @psinfo
                    EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID, FreeSpace, Size"';
                DELETE  FROM @psinfo
                WHERE   data IS NULL
                        OR data LIKE '%DeviceID%'
                        OR data LIKE '%----%';
                UPDATE  @psinfo
                SET     data = REPLACE(data, ' ', ',');
            WITH    DriveSpace
                      AS ( SELECT   SUBSTRING(data, 1, 2) AS [Drive] ,
                                    REPLACE(( LEFT(( SUBSTRING(data,
                                                              ( PATINDEX('%[0-9]%',
                                                              data) ),
                                                              LEN(data)) ),
                                                   CHARINDEX(',',
                                                             ( SUBSTRING(data,
                                                              ( PATINDEX('%[0-9]%',
                                                              data) ),
                                                              LEN(data)) ))
                                                   - 1) ), ',', '') AS [FreeSpace] ,
                                    REPLACE(RIGHT(( SUBSTRING(data,
                                                              ( PATINDEX('%[0-9]%',
                                                              data) ),
                                                              LEN(data)) ),
                                                  PATINDEX('%,%',
                                                           ( SUBSTRING(data,
                                                              ( PATINDEX('%[0-9]%',
                                                              data) ),
                                                              LEN(data)) ))),
                                            ',', '') AS [Size]
                           FROM     @psinfo
                         )
                INSERT  INTO @freespace
                        ( drive ,
                          FreeSpaceMB ,
                          SizeMB ,
                          PercentFree ,
                          DBDirectory
                        )
                        SELECT DISTINCT
                                Drive ,
                                CONVERT(dec(17, 2), CONVERT(dec(17, 2), FreeSpace)
                                / ( 1024 * 1024 )) AS FreeSpaceMB ,
                                CONVERT(dec(17, 2), CONVERT(dec(17, 2), d.Size)
                                / ( 1024 * 1024 )) AS SizeMB ,
                                ( CONVERT(dec(17, 2), FreeSpace)
                                  / CONVERT(dec(17, 2), d.Size) ) * 100 AS PercentFree ,
                                REVERSE(RIGHT(REVERSE(f.physical_name),
                                              ( LEN(f.physical_name)
                                                - CHARINDEX('\',
                                                            REVERSE(f.physical_name),
                                                            1) ) + 1))
                        FROM    sys.master_files AS f
                                JOIN DriveSpace AS d ON d.Drive = SUBSTRING(f.physical_name,
                                                              1, 2)
                        WHERE   type_desc = 'ROWS';
                SET @directory = ( SELECT TOP 1
                                            DBDirectory
                                   FROM     @freespace
                                   ORDER BY FreeSpaceMB DESC ,
                                            PercentFree DESC
                                 );
        END
    IF @CheckDriveSpace = 0
        BEGIN
            SET @directory = ( SELECT TOP 1
                                        REVERSE(RIGHT(REVERSE(f.physical_name),
                                                      ( LEN(f.physical_name)
                                                        - CHARINDEX('\',
                                                              REVERSE(f.physical_name),
                                                              1) ) + 1))
                               FROM     sys.master_files AS f
                               WHERE    DB_NAME(database_id) = @db
                             );
        END
    SELECT  @SQL = 'create database ' + @db + '_' + @date + '_ss ON '
            + SUBSTRING(( SELECT    ',' + ' ( NAME = [' + name
                                    + '], FILENAME = ''' + @directory + name
                                    + '_' + @date + '.ss' + ''')'
                          FROM      sys.master_files AS mf
                          WHERE     mf.database_id = d.database_id
                                    AND physical_name NOT LIKE '%.ldf'
                        FOR
                          XML PATH('')
                        ), 2, 65536) + ' AS SNAPSHOT OF ' + @db
    FROM    sys.databases AS d
    WHERE   DB_NAME(database_id) = @db;
    BEGIN TRY
        EXEC (@SQL);
        SELECT  'Snapshot of ' + @db + ' created in directory ' + @directory
                + '.';
    IF @xpcmdshell = 0
       BEGIN
              EXEC sp_configure 'xp_cmdshell', 0;
              RECONFIGURE;
              EXEC sp_configure 'show advanced options', 0;
              RECONFIGURE;
       END
       END TRY
    BEGIN CATCH
              IF @xpcmdshell = 0
              BEGIN
                     EXEC sp_configure 'xp_cmdshell', 0;
                     RECONFIGURE;
                     EXEC sp_configure 'show advanced options', 0;
                     RECONFIGURE;
              END
        PRINT 'Error Occured creating snapshot of ' + @db
            + ' created in directory ' + @directory + '.';
            PRINT @SQL;
            THROW;      
                     RETURN;

    END CATCH;
I hope this helps!