Function BuildTlogSQL

recipes/Python/578977_Python_SQLSMO_/recipe-578977.py:157–207

- check for log backups DECLARE backupFiles CURSOR FOR SELECT backupFile FROM @fileList WHERE backupFile LIKE '%.TRN' AND backupFile LIKE @dbName + '%' AND backupFile > @lastFullBackup OPEN backupFiles -- Loop through all the files for the database FETCH NEXT

(dbname, sourcedb, bkfolder, recovery = 'NORECOVERY', BACKUP_MASK = '_backup_20*.BAK', LOG_MASK = '_backup_20*.TRN')

def BuildTlogSQL(dbname, sourcedb, bkfolder, recovery = 'NORECOVERY', BACKUP_MASK = '_backup_20*.BAK', LOG_MASK = '_backup_20*.TRN'):
    """
    - check for log backups
    DECLARE backupFiles CURSOR FOR
    SELECT backupFile
    FROM @fileList
    WHERE backupFile LIKE '%.TRN'
    AND backupFile LIKE @dbName + '%'
    AND backupFile > @lastFullBackup
    OPEN backupFiles
    -- Loop through all the files for the database
    FETCH NEXT FROM backupFiles INTO @backupFile
    WHILE @@FETCH_STATUS = 0
    BEGIN
       SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
           + @backupPath + @backupFile + ''' WITH NORECOVERY'
       PRINT @cmd
       FETCH NEXT FROM backupFiles INTO @backupFile
    END
    CLOSE backupFiles
    DEALLOCATE backupFiles
    -- 6 - put database in a useable state
    SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
    PRINT @cmd
    """

    BKFILE = '____.BAK'
    try:
        BKFILE = GetLatestBackup(bkfolder, '\\' + sourcedb + BACKUP_MASK)[-1]   # BACKUP_MASK = '_backup_20*.BAK'
    except IndexError:
        pass

    s = ''

    filter = '\\' + sourcedb + LOG_MASK
    filelist = GetLatestBackup(bkfolder, filter )

    if len(filelist) == 0:
        print('No log files for ' + sourcedb)
        if recovery == 'RECOVERY':
            s = 'RESTORE DATABASE ' + dbname + ' WITH RECOVERY' + chr(13) + chr(10) + 'GO ' + chr(13) + chr(10)
            return s
    else:
        for x in filelist:
            if x[:-4] > BKFILE[:-4]:        # this means the log file is older than the latest full backup file
                s += 'RESTORE LOG ' + dbname + ' FROM DISK = ' + chr(39) + x + chr(39) + \
                    ' WITH NORECOVERY' + chr(13) + chr(10) + 'GO ' + chr(13) + chr(10)
        if x[:-4] > BKFILE[:-4]:
            if recovery == 'RECOVERY':
                s += 'RESTORE DATABASE ' + dbname + ' WITH RECOVERY' + chr(13) + chr(10) + 'GO ' + chr(13) + chr(10)
        return s

# ----------------End of functions----------------------------------------