Tuesday, February 10, 2009

Code for all sorts off backup


Backup is one off the most important things that need to be arranged after a SQL Server database is created, restored or attached.

The advantages of the T-SQL scripts presented here over a SSIS task:
1. Newly created db's are automatically included, no additional steps are required;

2. Databases that are in recovery mode 'Full' or 'Bulk Recovery' are automatically selected when transaction log backups are sheduled;
3. The checksum option that was introduced with SQL 2005 is included;
4. After a backup is completed a verify is executed;
5. Expired backups are automatically deleted;
6. Each set of dumps are stored in their own folder: less clutter
7. Databases can be set off-line without the job-step to fail
8. Databases in standby mode are skipped


The actual backup is made in the 'usp_MakeBackup' stored proc that is not called directly,
but is executed from the parent sp's: 'usp_MakeFullBackup', 'usp_MakeDifferentialBackup' and 'usp_MakeLogBackup' all scheduled from SQL Agent.


I usally make full backup around 2300hr and schedule log backups every 2hrs or so between 0800 until 2000hr.


The differential backup sp was only used in an OLTP config with a high volume of log backups (every 25 minutes), To avoid a very long list if individual log files we scheduled a differential backup every 4hrs. So the restore sequence was: full backup, last differential backup after the full backup and one or more transaction log backup that were made after the differential.

Code to initiate a full backup (store in CodeCatalog)


-- =========================================================================

-- Author:         SQL Server Advisor
-- Create date: jan/2009
-- Description: Selects candidates for a full backup
--                 The actual backup is made in sp 'usp_MakeBackup'
-- ========================================================================
CREATE PROCEDURE [dbo].[usp_MakeFullBackup]
AS
BEGIN
        SET NOCOUNT ON;

        DECLARE @DbName NVARCHAR(100)

        DECLARE dbnames_cursor CURSOR FAST_FORWARD LOCAL FOR
           SELECT name FROM sys.databases
             WHERE name NOT IN ('tempdb','AdventureWorks') -- skip the unwanted DBs
               AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
               AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
            ORDER BY name

        OPEN dbnames_cursor
        FETCH NEXT FROM dbnames_cursor INTO @DbName

        WHILE @@FETCH_STATUS = 0
        BEGIN
             EXEC usp_MakeBackup @dbname, 'F' -- backup db here..

             FETCH NEXT FROM dbnames_cursor INTO @DbName
        END

        CLOSE dbnames_cursor
        DEALLOCATE dbnames_cursor


END
 


Code to initiate a differential backup (store in CodeCatalog)




-- ======================================================================

-- Author:    SQL Server Advisor
-- Create date: jan/2009
-- Description: Selects candidates for a differential backup
--              The actual backup is made in sp 'usp_MakeBackup'
-- ======================================================================
 
CREATE PROCEDURE [dbo].[usp_MakeDifferentialBackup]
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @DbName NVARCHAR(100)
 
--get all the on-line databases
       DECLARE dbnames_cursor CURSOR FAST_FORWARD LOCAL FOR
           SELECT name FROM sys.databases-- skip unwanted DBs
            WHERE name NOT IN ('master','model','tempdb','AdventureWorks')
              AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
              AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
           ORDER BY name
 
       OPEN dbnames_cursor
       FETCH NEXT FROM dbnames_cursor INTO @DbName
 
       WHILE @@FETCH_STATUS = 0
       BEGIN
              EXEC usp_MakeBackup @dbname, 'D' -- backup db here..
 
              FETCH NEXT FROM dbnames_cursor INTO @DbName
       END
 
       CLOSE dbnames_cursor
       DEALLOCATE dbnames_cursor
 
END
 



Code to initiate a transaction log backup (store in CodeCatalog)




-- ==============================================================

-- Author:    SQL Server Advisor
-- Create date: jan/2009
-- Description: Selects candidates for a log backup
--            The actual backup is made in sp 'usp_MakeBackup'
-- ==============================================================
CREATE PROCEDURE [dbo].[usp_MakeLogBackup]
AS
BEGIN
       SET NOCOUNT ON;

       DECLARE @DbName NVARCHAR(100)

 --get all the on-line databases
       DECLARE dbnames_cursor CURSOR FAST_FORWARD LOCAL FOR
          SELECT name FROM sys.databases
              WHERE name NOT IN ('master','model','tempdb','AdventureWorks')
                AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
                AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
                AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')
           ORDER BY name

       OPEN dbnames_cursor
       FETCH NEXT FROM dbnames_cursor INTO @DbName

       WHILE @@FETCH_STATUS = 0
       BEGIN
              EXEC usp_MakeBackup @dbname, 'L' -- backup log here..

              FETCH NEXT FROM dbnames_cursor INTO @DbName
       END

       CLOSE dbnames_cursor
       DEALLOCATE dbnames_cursor
END
 


The actual backup to disk is made here (to store in CodeCatalog). Looks bit odd, still fighting the code editors:


-- ================================================================================

-- Author:        SQL Server advisor
-- Create date: jan/2009
-- Description:    makes full or logbackup based on passed parameter @BackupType
--            databases are selected in 'usp_MakeFullBackup',
--               'usp_MakeLogBackup' and usp_MakeDifferentialBackup
--=================================================================================

 --called from usp_MakeFullBackup, usp_MakeLogBackup or usp_MakeDifferentialBackup

CREATE PROCEDURE [dbo].[usp_MakeBackup] @DbName NVARCHAR(100), -- Database name
    @BackupType CHAR(1)
-- 'F' = full backup, 'L' = log backup, 'D' = differential backup
AS
BEGIN
    DECLARE @FileName NVARCHAR(300), @Directory NVARCHAR(256)
    DECLARE @BackupRetention SMALLINT   -- backup retention level, days
    DECLARE @DateToDelete DATETIME,@DeleteDateStr NVARCHAR(30),@FileExtension NCHAR(10)

    SET @Directory = 'D:\backup\' + @DbName -- set the backup path here

    SET @BackupRetention = 4 -- set retention –in days- of backup files here

    EXECUTE master.dbo.xp_create_subdir @Directory

    SET @FileName=@Directory + '\' + @DbName+'.'+Convert(nvarchar(10),GetDate(),104) + '_' +    LTrim(Str(DatePart(hour,GetDate())))+LTrim(Str(DatePart(minute,GetDate())))

    SET @DateToDelete=DateAdd(day,-ABS(@BackupRetention),GetDate())
    SET @DeleteDateStr=Convert(nvarchar(30),@DateToDelete,1) + ' 00:00'

    IF @BackupType = 'F' -- make a full backup
    BEGIN
        SET @FileExtension = 'BAK'
        SET @FileName = @FileName + '.' + @FileExtension

        BACKUP DATABASE @DbName TO DISK = @FileName WITH CHECKSUM, INIT, STOP_ON_ERROR
    END

    IF @BackupType = 'D' -- make a differential  backup
    BEGIN
        SET @FileExtension = 'DIF'
        SET @FileName = @FileName + '.' + @FileExtension

        BACKUP DATABASE @DbName TO DISK = @FileName WITH DIFFERENTIAL,
             
 CHECKSUM, INIT, STOP_ON_ERROR
    END

    IF @BackupType = 'L' -- make a log backup
    BEGIN
        SET @FileExtension = 'TRN'
        SET @FileName = @FileName + '.' + @FileExtension

        BACKUP LOG @DbName TO DISK = @FileName WITH CHECKSUM, INIT, STOP_ON_ERROR
    END

--  check if backup was successfull
    RESTORE VERIFYONLY FROM DISK = @FileName WITH CHECKSUM, STOP_ON_ERROR

--  remove the backup files older then @BackupRetention days
    EXECUTE master.dbo.xp_delete_file 0,@Directory,@FileExtension,@DeleteDateStr,1;

END




Bookmark and Share