Sunday, October 25, 2009

Disaster Recovery


Disaster Recovery
For a while we had a disaster recovery site and once a quarter we would bring our backups and restore the databases and we would see how far we could get bringing the business backup in one day. The site just had Servers, I got them after the OS was loaded, so I had to load SQL Server, and start restoring. We did not try to get everything up and running the first try, we set a goal of the databases and the main website and try to processes some business. Then in later visits we added to what we knew we could do, adding Phones (Voice over IP), Domains etc. The first time I just restored from a backup, the next and subsequent visits, I did a point in time restore using log restores. Oh and we have a ton of SQL logins that needed to be restored as well. So once a quarter for over two years we did this, after the second time of figuring out the restore script and restoring the logins etc I thought that there has to be a better way. This is what I came up with.
The first thing I did was create a directory in the backup path so I knew it was being backed up and would be available during our restore. I put the following scripts in this directory as well as other things that were needed:
·         SQLDTSFTPTask.msi which is a FTP Task for DTS packages
·         SQLServer2005_DTS.msi – since we are on 2005 and running DTS package we need the DTS add on
·         Current SQL Service Pack that servers are on.
·         Notes.txt – General instructions, things that were encountered
·         CreateDatabaselist.sql – creates a list of databases and their logic file names. Most likely the database files will have to move
·         Databaselist.txt – List of databases and their logical file names. We only have a primary file group, so if you have more than one you have to try something different.
·         CreateRestoreScripts.sql – this reads the databaselist.txt and given the path of the backups returns the restore scripts by reading the header information of the backups.
·         CreateLogins.sql – Script to recreate the SQL logins., Output from sp_help_revlogin
We always did a postmortem after wards and as things came up that would be help I added to the Notes.txt file. For example initially I did not have the SQL logins, but I did have a back up of master, so I put instructions on restoring master in the notes.TXT. You have to be in single user mode to restore master and I’m pretty sure, like msdb, the server that you are restoring to has to be at the same service pack that the backup is from. I was not too keen of restoring the master, but I was in a crunch.

/*
"CreateDatabaselist.sql"


Put the output from this into the file Databaselist.txt
*/

SET nocount ON
SELECT  d.NAME + ',' + MAX(CASE WHEN m.name NOT LIKE '%log' THEN m.name
                                ELSE ''
                           END) + ','
        + MAX(CASE WHEN m.name LIKE '%log' THEN m.name
                   ELSE ''
              END)
FROM    sys.Master_files m
JOIN    sys.databases d
        ON m.database_id = d.database_id
WHERE   d.name NOT IN ( 'tempdb', 'model', 'distribution' )
GROUP BY d.name 


Here is some sample output:
database1,database1_Data,database1_Log
database2,database2_Data,database2_Log
database3,Special_Data,Special_Log

For the above script I put it in a job to create the Databaselist.txt, the same one I run sp_help_revlogin.
The Script that does the heavy lifting is CreateRestoreScripts.sql below. It has been a while since I ran this and one thing we did for our recovery test was put the backups onto USB drivers so it was easier to restore. And yes we did test the restores from tape, but we knew that worked and we had limited time. I have to say that this script evolved. I think the first time I did a point in time I scripted the restores of the .TRN files sequentially, knowing full well that the backup was done in the middle some were. So when I ran the script and tried to load the earlier log files it spit out errors until it hit the right LSN number and from there on it loaded all subsequent files. LSN is the Log sequence number and what is looked at for the sequences of files to restores.   Comments are sparse in the script, sorry, so here are some notes:
·         Assumes Backup path is formatted DatabaseName\backups
·         Full backups are .BAK, Differentials are .DIF, and logs are .TRN
·         The #Header table I took from a script online way back when and did not record the author, my bad, thanks to that unknown SQL person
·         This uses DOS commands to get the list of files, here are some of the commands used
o   Dir /B – gives only file names
o   For a% in (PATH) do Echo … - this loops through all files in PATH and uses %a as variable, %~ta is the timestamp of that file
·         This does the INSERT INTO “table” Exec proc – I try to avoid this because of performance issues I’ve run into, but this is such a limited use it’s OK.
·         When restoring databases to a point in time they must restored WITH STANDBY, which this script does the last thing it does is restore the database with recovery so it’s ready for use.
SET NOCOUNT ON
-- set locations

-- backups location
DECLARE @BackUpPath VARCHAR(250)
SET @BackUpPath = 'E:\SQLSERVER\BACKUPs\'
DECLARE @NewSQLPath VARCHAR(250)
SET @NewSQLPath = 'D:\SQLSERVER\Data\'

/* tables used  */
-- table of actual db data
DROP TABLE #DBINFO
CREATE TABLE #DBINFO
    (
      DBNAME SYSNAME,
      DataFile VARCHAR(250),
      PathDataFile VARCHAR(250),
      LogFile VARCHAR(250),
      PathLogFile VARCHAR(250)
    )
BULK INSERT #DBINFO FROM 'E:\SQLSERVER\BACKUPs\DR Scripts\Databaselist.txt'
    WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )
-- create table to insert from cmdshell
DROP TABLE #files
CREATE TABLE #files ( FILNAME VARCHAR(1000) )

-- this is used to get the list of backup files from the backup system
DROP TABLE  #FileList
CREATE TABLE #FileList
    (
      FileDate DATETIME,
      FILNAME VARCHAR(250)
    )

-- Restore header only Result set
DROP TABLE #Header
CREATE TABLE #Header
    (
      BackupName NVARCHAR(128),
      BackupDescription NVARCHAR(255),
      BackupType SMALLINT,
      ExpirationDate DATETIME,
      Compressed TINYINT,
      Position SMALLINT,
      DeviceType TINYINT,
      UserName NVARCHAR(128),
      ServerName NVARCHAR(128),
      DatabaseName NVARCHAR(128),
      DatabaseVersion INT,
      DatabaseCreationDate DATETIME,
      BackupSize NUMERIC(20, 0),
      FirstLSN NUMERIC(25, 0),
      LastLSN NUMERIC(25, 0),
      CheckpointLSN NUMERIC(25, 0),
      DatabaseBackupLSN NUMERIC(25, 0),
      BackupStartDate DATETIME,
      BackupFinishDate DATETIME,
      SortOrder SMALLINT,
      CodePage SMALLINT,
      UnicodeLocaleId INT,
      UnicodeComparisonStyle INT,
      CompatibilityLevel TINYINT,
      SoftwareVendorId INT,
      SoftwareVersionMajor INT,
      SoftwareVersionMinor INT,
      SoftwareVersionBuild INT,
      MachineName NVARCHAR(128),
      Flags INT,
      BindingID UNIQUEIDENTIFIER,
      RecoveryForkID UNIQUEIDENTIFIER,
      Collation NVARCHAR(128),
      FamilyGUID UNIQUEIDENTIFIER,
      HasBulkLoggedData BIT,
      IsSnapshot BIT,
      IsReadOnly BIT,
      IsSingleUser BIT,
      HasBackupChecksums BIT,
      IsDamaged BIT,
      BeginsLogChain BIT,
      HasIncompleteMetaData BIT,
      IsForceOffline BIT,
      IsCopyOnly BIT,
      FirstRecoveryForkID UNIQUEIDENTIFIER,
      ForkPointLSN NUMERIC(25, 0) NULL,
      RecoveryModel NVARCHAR(60),
      DifferentialBaseLSN NUMERIC(25, 0) NULL,
      DifferentialBaseGUID UNIQUEIDENTIFIER,
      BackupTypeDescription NVARCHAR(60),
      BackupSetGUID UNIQUEIDENTIFIER
    )


DECLARE @sql VARCHAR(3000)
-- pulls double duty as a SQL command string and a DOS command string


-- loop through the databases and load them
DECLARE @dbname VARCHAR(250),
    @DbbackupFilename VARCHAR(250),
    @lsn VARCHAR(50),
    @DataFileName VARCHAR(250),
    @DataFilePath VARCHAR(250),
    @LogFileName VARCHAR(250),
    @logFilePath VARCHAR(250),
    @filedate DATETIME


DELETE  FROM #DBINFO
WHERE   dbname <> @DBNAME
SET @dbname = ( SELECT  MIN(dbname)
                FROM    #DBINFO
              )


WHILE EXISTS ( SELECT TOP 1
                        *
               FROM     #DBINFO )
    BEGIN
        PRINT '/******           ' + @dbname
            + '                 ************/'
                       
        SELECT  @DataFileName = DataFile,
                @DataFilePath = PathDataFile,
                @LogFileName = LogFile,
                @logFilePath = PathLogFile
        FROM    #DBINFO
        WHERE   dbname = @dbname

            -- get the list of backup files
        SET @sql = 'dir ' + @BackUpPath + @dbname + ' /B'
        INSERT  INTO #files
                EXEC master..xp_cmdshell @sql
            -- CLEAN UP NULL VALUE
        DELETE  FROM #files
        WHERE   filname IS NULL

            -- find the lastest .BAK file
        SET @DbbackupFilename = ( SELECT TOP 1
                                            FILNAME
                                  FROM      #files
                                  WHERE     filname LIKE '%.bak'
                                  ORDER BY  FILNAME DESC
                                )
           
            -- get last Lsn number from backup
        SET @sql = 'restore headeronly from disk=''' + @BackUpPath + @dbname
            + '\' + @DbbackupFilename + ''''

        DELETE  FROM #Header
        INSERT  INTO #Header
                EXEC ( @sql
                    )
        SET @lsn = ( SELECT MAX(LastLSN)
                     FROM   #header
                   )
        SET @filedate = ( SELECT    BackupFinishDate
                          FROM      #header
                        )


        SET @SQL = 'RESTORE DATABASE ' + @DBname + ' FROM DISK = '''
            + @BackUpPath + @dbname + '\' + @DbbackupFilename + '''
      WITH MOVE ''' + @DataFileName + ''' TO ''' + @NewSQLPath + @DataFileName
            + '.mdf'',
         MOVE ''' + @LogFileName + ''' TO ''' + @NewSQLPath + @LogFileName
            + '.ldf'',
       REPLACE, STANDBY = ''' + @BackUpPath + @DBname + '\' + @DBname
            + '_Undo.dat'''
        PRINT @SQL

            -- remove ALL FULL BACKUPS FROM LIST
        DELETE  FROM #files
        WHERE   filname LIKE '%.bak'
        TRUNCATE TABLE #files
        SET @sql = 'for %a in (' + @BackUpPath + @dbname
            + '\*.dif) do @echo INSERT INTO #FileList Values(''%~ta'',''%a'')'
        INSERT  INTO #files
                EXEC master..xp_cmdshell @sql
        DELETE  FROM #files
        WHERE   ISNULL(FILNAME, 'XX') NOT LIKE 'INSERT%'

        WHILE EXISTS ( SELECT   *
                       FROM     #files )
            BEGIN
                SET @sql = ( SELECT MIN(FILNAME)
                             FROM   #files
                           )
                PRINT @sql -- just to verify it looks at all the .dif files
                EXEC ( @sql
                    )
                DELETE  FROM #files
                WHERE   @sql = FILNAME
            END
        DELETE  FROM #FileList
        WHERE   filedate < @filedate
            -- Load differentials
        WHILE EXISTS ( SELECT TOP 1
                                *
                       FROM     #FileList
                       WHERE    filname LIKE '%.dif' )
            BEGIN
                  -- get next file name
                SET @DbbackupFilename = ( SELECT TOP 1
                                                    FILNAME
                                          FROM      #FileList
                                          WHERE     filname LIKE '%.dif'
                                          ORDER BY  FILNAME ASC
                                        )
                 
                  -- get last Lsn number from backup
                SET @sql = 'restore headeronly from disk='''
                    + @DbbackupFilename + ''''

                  -- clear out header table and repopulate
                DELETE  FROM #Header
                INSERT  INTO #Header
                        EXEC ( @sql
                            )

                SET @filedate = ( SELECT    BackupFinishDate
                                  FROM      #header
                                )
                  -- if LSN is greater than last back up then load
                IF ( SELECT LastLSN
                     FROM   #header
                   ) >= @LSN
                    BEGIN
                        SET @sql = 'RESTORE DATABASE ' + @DBName
                            + ' FROM DISK=''' + @DbbackupFilename
                            + ''' WITH STANDBY=''' + @BackUpPath + @DBname
                            + '\' + @DBname + '_Undo.dat'''
                        PRINT @SQL

                        SET @lsn = ( SELECT LastLSN
                                     FROM   #header
                                   )           
                    END
                DELETE  FROM #FileList
                WHERE   filname = @DbbackupFilename  
            END        
            -- load logs

        TRUNCATE TABLE #files
        TRUNCATE TABLE #FileList
        SET @sql = 'for %a in (' + @BackUpPath + @dbname
            + '\*.trn) do @echo INSERT INTO #FileList Values(''%~ta'',''%a'')'
        INSERT  INTO #files
                EXEC master..xp_cmdshell @sql
        DELETE  FROM #files
        WHERE   ISNULL(FILNAME, 'XX') NOT LIKE 'INSERT%'

        WHILE EXISTS ( SELECT   *
                       FROM     #files )
            BEGIN
                SET @sql = ( SELECT MIN(FILNAME)
                             FROM   #files
                           )
                PRINT @sql --just to verify that it looks at all .trn files
                EXEC ( @sql
                    )
                DELETE  FROM #files
                WHERE   @sql = FILNAME
            END
        -- remove old files
        DELETE  FROM #FileList
        WHERE   filedate < @filedate
       
        WHILE EXISTS ( SELECT TOP 1
                                *
                       FROM     #FileList
                       WHERE    filname LIKE '%.trn' )
            BEGIN
                  -- get next file name
                SET @DbbackupFilename = ( SELECT TOP 1
                                                    FILNAME
                                          FROM      #FileList
                                          WHERE     filname LIKE '%.trn'
                                          ORDER BY  FILNAME ASC
                                        )
                 
                  -- get last Lsn number from backup
                SET @sql = 'restore headeronly from disk='''
                    + @DbbackupFilename + ''''

                  -- clear out header table and repopulate
                DELETE  FROM #Header
                INSERT  INTO #Header
                        EXEC ( @sql
                            )

                  -- if LSN is greater than last back up then load
                IF ( SELECT LastLSN
                     FROM   #header
                   ) >= @LSN
                    BEGIN
                        SET @sql = 'RESTORE LOG ' + @DBName + ' FROM DISK='''
                            + @DbbackupFilename + ''' WITH STANDBY='''
                            + @BackUpPath + @DBname + '\' + @DBname
                            + '_Undo.dat'''
                        PRINT @SQL

                        SET @lsn = ( SELECT LastLSN
                                     FROM   #header
                                   )           
                    END
                DELETE  FROM #FileList
                WHERE   filname = @DbbackupFilename  
            END        

        SET @sql = 'Restore Database ' + @DBName + ' with recovery'
        PRINT @SQL

        DELETE  FROM #DBINFO
        WHERE   dbname = @dbname
        SET @dbname = ( SELECT  MIN(dbname)
                        FROM    #DBINFO
                      )
    END


No comments:

Post a Comment