Backup all databases & check backups

,

This procedure backs up all user or all system DBs. It does a full / diff or log backup. It was tested on SQL 2000 & 2008.

After backing up it tests full backups (performs a test restore).

You can log actions to a temp table with the @log param.

Other params are explained in the code.

--------------------------------------------------------------------------
-- BACKUP PROCEDURE
-- v1.0
-- EXEC dbo.P_Backup_DB @DBType = 'U', @BackupType = 'D', @BackupPath = 'e:\test', @log = 1
-- select * from tempdb.dbo.BackupLog
--------------------------------------------------------------------------
USE master
GO

IF OBJECT_ID('dbo.P_Backup_DB') IS NOT NULL
    DROP PROCEDURE dbo.P_Backup_DB
GO

CREATE PROCEDURE dbo.P_Backup_DB(
    @DBType            CHAR(1),        -- U - User, S - System
    @BackupType        CHAR(1),        -- F - Full, D - Diff, L - Log
    @BackupPath        NVARCHAR(500) = 'E:\Test\', -- Specify absolute path
    @Log            BIT = 0
)
AS
BEGIN

    SET NOCOUNT ON

    -- Parameter checks
    IF @DBType NOT IN ('U','S')
    BEGIN 
        RAISERROR('Invalid @DBType parameter value', 16, 1) WITH LOG
        RETURN -1;
    END
    IF @BackupType NOT IN ('F','D','L')
    BEGIN 
        RAISERROR('Invalid @BackupType parameter value', 16, 1) WITH LOG
        RETURN -1;
    END    
    IF @DBType = 'S' AND @BackupType IN ('D','L')
    BEGIN 
        RAISERROR('Only Full backup is supported for system databases', 16, 1) WITH LOG
        RETURN -1;
    END    
        
    -- Declare variables
    DECLARE @DBName                VARCHAR(50)        -- DB to backup    
    DECLARE @BKPFileName        VARCHAR(256)    -- filename for backup 
    DECLARE @TestDBName            VARCHAR(50)        -- DB to backup
    DECLARE @FileTimeStamp        VARCHAR(20)        -- used to generate the filename
    DECLARE @Error                VARCHAR(250)

    DECLARE @DBDataLogicalName    VARCHAR(100)    -- used for test restore
    DECLARE @DBLogLogicalName    VARCHAR(100)    -- used for test restore
    DECLARE @DBDataTestFileName VARCHAR(100)    -- used for test restore
    DECLARE @DBLogTestFileName    VARCHAR(100)    -- used for test restore
    DECLARE @Sql                NVARCHAR(500)
    DECLARE @Sql2                NVARCHAR(500)
    DECLARE @Cmd                NVARCHAR(500)
    DECLARE @LogText            NVARCHAR(500)

    -- Check if BKP folder exists and create if necessary
    DECLARE @FilePath            NVARCHAR(500)
    DECLARE @FolderExists        INT

    SET @FilePath = REPLACE (@BackupPath + '\nul','\\','\')
    
    EXEC master.dbo.xp_fileexist @FilePath, @FolderExists OUTPUT 
    
    IF @FolderExists = 0
    BEGIN 
        SET @Cmd = 'MD '+ @BackupPath
        EXEC xp_cmdshell @Cmd
    END    

    -- Set variables
    SELECT @FileTimeStamp = LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),GETDATE(),120),'-',''),':','h'),' ','_'),14) 

    IF @Log = 1
    BEGIN
        IF OBJECT_ID('tempdb.dbo.BackupLog') IS NULL
        BEGIN
            CREATE TABLE tempdb.dbo.BackupLog (
                ID INT IDENTITY(1,1),
                LogText NVARCHAR(500),
                ExecTime DATETIME
            )
        END

        SET @LogText = '-------- Starting backup @DBType = ' + @DBType + ', @BackupType = ' + @BackupType
        
        INSERT INTO tempdb.dbo.BackupLog (LogText, ExecTime)
        VALUES (@LogText, GETDATE())            
    END
    ---------------------------------------------------------------------------------------------------------
    -- USER DATABASES
    ---------------------------------------------------------------------------------------------------------
    IF @DBType = 'U'
    BEGIN
        -- Go through all user DBs
        DECLARE db_cursor CURSOR FOR 
            SELECT name 
            FROM master.dbo.sysdatabases 
            WHERE name NOT IN ('master','model','msdb','tempdb')
            AND name NOT LIKE 'Test%' 

        OPEN db_cursor 
        FETCH NEXT FROM db_cursor INTO @DBName 

        WHILE @@FETCH_STATUS = 0 
        BEGIN 
            IF @Log = 1
            BEGIN
                SET @LogText = 'Processing 1: ' + @DBName
                INSERT INTO tempdb.dbo.BackupLog (LogText, ExecTime)
                VALUES (@LogText, GETDATE())            
            END            
            -- Get Data & Log logical names
            IF @@VERSION LIKE '%2000%' -- use old system tables
            BEGIN
                SET @Sql = N'SELECT @DBDataLogicalName = name FROM ' + @DBName + '.dbo.sysfiles WHERE status & 0x40 = 0'
                SET @Sql2 = N'SELECT @DBLogLogicalName = name FROM ' + @DBName + '.dbo.sysfiles WHERE status & 0x40 <> 0'
            END
            ELSE
            BEGIN
                SET @Sql = N'SELECT @DBDataLogicalName = name FROM ' + @DBName + '.sys.database_files WHERE type = 0'
                SET @Sql2 = N'SELECT @DBLogLogicalName = name FROM ' + @DBName + '.sys.database_files WHERE type = 1'
            END
            
            EXEC sp_executesql @Sql, N'@DBDataLogicalName VARCHAR(100) OUTPUT', @DBDataLogicalName = @DBDataLogicalName OUTPUT
            EXEC sp_executesql @Sql2, N'@DBLogLogicalName VARCHAR(100) OUTPUT', @DBLogLogicalName = @DBLogLogicalName OUTPUT
                    
            SET @TestDBName            = 'Test' + @DBName
            SET @DBDataTestFileName = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_Test.mdf'
            SET @DBLogTestFileName    = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_Test.ldf'
            
            -- Check if BKP folder exists and create if necessary            
            SET @FilePath = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\nul'
            
            EXEC master.dbo.xp_fileexist @FilePath, @FolderExists OUTPUT 
        
            IF @FolderExists = 0
            BEGIN 
                SET @cmd = 'MD '+ REPLACE (@BackupPath + '\' + @DBName,'\\','\')
                EXEC xp_cmdshell @cmd
            END    
            
            IF @Log = 1
            BEGIN
                SET @LogText = 'Processing 2: ' + @DBName
                INSERT INTO tempdb.dbo.BackupLog (LogText, ExecTime)
                VALUES (@LogText, GETDATE())            
            END            
 -------------------------------------------------------------------------------------------------            
            IF @BackupType = 'F'
            BEGIN
                SET @BKPFileName = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_F_' + @FileTimeStamp + '.BAK' 
                
                -- Backup
                BACKUP DATABASE @DBName 
                TO DISK = @BKPFileName 
                SET @Error = @@ERROR
                
                -- First quick check (check file)
                RESTORE VERIFYONLY 
                FROM DISK = @BKPFileName;
                
                SET @Error = @@ERROR
                IF @Error <> 0
                    RAISERROR('Full backup failed for DB: %s', 16, 1, @DBName) WITH LOG

                -- Second in depth check (check data)
                RESTORE DATABASE @TestDBName
                FROM DISK = @BKPFileName
                WITH MOVE @DBDataLogicalName TO @DBDataTestFileName,
                    MOVE @DBLogLogicalName TO @DBLogTestFileName;
                
                SET @Error = @@ERROR
                IF @Error <> 0
                    RAISERROR('Test restore failed for DB: %s', 16, 1, @DBName) WITH LOG

                -- The restore worked, drop the test DB
                SET @Sql = N'DROP DATABASE ' + @TestDBName 
                EXEC sp_executesql @Sql
            END 
     -------------------------------------------------------------------------------------------------                
            ELSE IF @BackupType = 'D'
            BEGIN
                SET @BKPFileName = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_D_' + @FileTimeStamp + '.DIF' 
                
                -- Log
                IF @Log = 1
                BEGIN
                    SET @LogText = 'Will backup: ' + @DBName + ' to ' + @BKPFileName
                    INSERT INTO tempdb.dbo.BackupLog (LogText, ExecTime)
                    VALUES (@LogText, GETDATE())            
                END
                
                -- Backup
                BACKUP DATABASE @DBName 
                TO DISK = @BKPFileName
                WITH DIFFERENTIAL
                
                -- First quick check (check file)
                RESTORE VERIFYONLY 
                FROM DISK = @BKPFileName;
                
                SET @Error = @@ERROR
                IF @Error <> 0
                    RAISERROR('Diff backup failed for DB: %s', 16, 1, @DBName) WITH LOG
                    
            END
     -------------------------------------------------------------------------------------------------                
            ELSE IF @BackupType = 'L'
            BEGIN
                -- Log
                IF @Log = 1
                BEGIN
                    SET @LogText = 'Will backup: LOG ' + @DBName + ' to ' + @BKPFileName
                    INSERT INTO tempdb.dbo.BackupLog (LogText, ExecTime)
                    VALUES (@LogText, GETDATE())            
                END
                            
                SET @BKPFileName = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_L_' + @FileTimeStamp + '.LOG' 
                
                -- Backup
                BACKUP LOG @DBName 
                TO DISK = @BKPFileName
                
                -- First quick check (check file)
                RESTORE VERIFYONLY 
                FROM DISK = @BKPFileName;
                
                SET @Error = @@ERROR
                IF @Error <> 0
                    RAISERROR('Log backup failed for DB: %s', 16, 1, @DBName) WITH LOG
            END    

            FETCH NEXT FROM db_cursor INTO @DBName 
        END 

        CLOSE db_cursor 
        DEALLOCATE db_cursor
    END
    ---------------------------------------------------------------------------------------------------------
    -- SYSTEM DATABASES
    ---------------------------------------------------------------------------------------------------------        
    ELSE IF @DBType = 'S'
    BEGIN
        -- Go through all system DBs
        DECLARE db_cursor CURSOR FOR 
            SELECT name 
            FROM master.dbo.sysdatabases 
            WHERE name IN ('master','model','msdb')

        OPEN db_cursor 
        FETCH NEXT FROM db_cursor INTO @DBName 

        WHILE @@FETCH_STATUS = 0 
        BEGIN 

            -- Get Data & Log logical names
            IF @@VERSION LIKE '%2000%' -- use old system tables
            BEGIN
                SET @Sql = N'SELECT @DBDataLogicalName = name FROM ' + @DBName + '.dbo.sysfiles WHERE status & 0x40 = 0'
                SET @Sql2 = N'SELECT @DBLogLogicalName = name FROM ' + @DBName + '.dbo.sysfiles WHERE status & 0x40 <> 0'
            END
            ELSE
            BEGIN
                SET @Sql = N'SELECT @DBDataLogicalName = name FROM ' + @DBName + '.sys.database_files WHERE type = 0'
                SET @Sql2 = N'SELECT @DBLogLogicalName = name FROM ' + @DBName + '.sys.database_files WHERE type = 1'
            END
            
            EXEC sp_executesql @Sql, N'@DBDataLogicalName VARCHAR(100) OUTPUT', @DBDataLogicalName = @DBDataLogicalName OUTPUT
            EXEC sp_executesql @Sql2, N'@DBLogLogicalName VARCHAR(100) OUTPUT', @DBLogLogicalName = @DBLogLogicalName OUTPUT
                    
            SET @TestDBName            = 'Test' + @DBName
            SET @DBDataTestFileName = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_Test.mdf'
            SET @DBLogTestFileName    = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_Test.ldf'            

            -- Check if BKP folder exists and create if necessary            
            SET @FilePath = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\nul'
            
            EXEC master.dbo.xp_fileexist @FilePath, @FolderExists OUTPUT 
        
            IF @FolderExists = 0
            BEGIN 
                SET @cmd = 'MD '+ REPLACE (@BackupPath + '\' + @DBName,'\\','\')
                EXEC xp_cmdshell @cmd
            END    

            SET @BKPFileName = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_F_' + @FileTimeStamp + '.BAK' 
                
     -------------------------------------------------------------------------------------------------                
            -- Backup
            BACKUP DATABASE @DBName 
            TO DISK = @BKPFileName 
            SET @Error = @@ERROR
            
            -- First quick check (check file)
            RESTORE VERIFYONLY 
            FROM DISK = @BKPFileName;
            
            SET @Error = @@ERROR
            IF @Error <> 0
                RAISERROR('Full backup failed for system DB: %s', 16, 1, @DBName) WITH LOG

            -- Second in depth check (check data)
            RESTORE DATABASE @TestDBName
            FROM DISK = @BKPFileName
            WITH MOVE @DBDataLogicalName TO @DBDataTestFileName,
                MOVE @DBLogLogicalName TO @DBLogTestFileName;
            
            SET @Error = @@ERROR
            IF @Error <> 0
                RAISERROR('Test restore failed for DB: %s', 16, 1, @DBName) WITH LOG

            -- The restore worked, drop the test DB
            SET @Sql = N'DROP DATABASE ' + @TestDBName 
            EXEC sp_executesql @Sql
            
            FETCH NEXT FROM db_cursor INTO @DBName 
        END    
        
        CLOSE db_cursor 
        DEALLOCATE db_cursor
    END
END    
GO

Rate

3 (1)

Share

Share

Rate

3 (1)