Friday, February 17, 2012

Backup Script (2.0)

So, as promised, I am posting my new backupscript.  I've completely reworked it.  You know call it with a specific database, backup type (log, full, or differential), and a backup path.

Here's the basic logic:

If we try to backup tempdb, bail (You can't back up tempdb)

If you try to backup master with differential or log, bail

If you try to take a log backup of a non full recovery db, bail
  (I dont have any bulk logged DBs, but I probably should change this)


Get the DB Guid (This was for databases that I restored with the same name, but were different databases altogether.  I using the Database Name to query the history.  the GUID is much more robust.

Force the BackupPathRoot to have a trailing \  - So the path works properly.

Get the last full backup information

If there was never a full backup, switch the backup type to full

If the last full is not in the backup path root, or is missing, switch to full (I'm thinking about changing this to only happen if we're taking a differential)

If we're taking a log backup, Check the log chain (just need to check from the last full or the last differential)

In the backup path root, make a subfolder for the database if it doesn't exist.

Backup the database with the following format:  %BackupPathRoot%\%ServerName%\%DatabaseName%\%DatabaseName% YYYYMMDD HHMMSS %BackupType%.bak

so, there ya go...

here's the link : BackupScript

Here's the source:


USE AdministrationIF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='BackupDatabase')
  
DROP PROC BackupDatabase
GO
CREATE PROC BackupDatabase
  
@database SYSNAME,
  
@backuptype VARCHAR(20),
  
@backupPathRoot VARCHAR(255)  WITH ENCRYPTION
AS
/*
--Test Data
declare
  @database sysname,
  @backuptype varchar(20),
  @backupPathRoot varchar(255)

set @database = 'robtest'
set @backuptype = 'log'
set @backupPathRoot='C:\Backup'
--*/
SET NOCOUNT ON
DECLARE
  
@databaseGuid UNIQUEIDENTIFIER,
  
@debugmessages INT,
  
@DifferentialBackupLocation NVARCHAR(260),
  
@DifferentialBackupSetId INT,
  
@FileExists INT,
  
@filename VARCHAR(255),
  
@folder VARCHAR(255),
  
@FullBackupLocation NVARCHAR(260),
  
@FullBackupSetId INT,
  
@now DATETIME,
  
@LogMessage VARCHAR(MAX),
  
@BackupLogID INT
  
--select @BackupLogID=MAX(id) from BackupLog -- Just for TestingSET @debugmessages = 0-- Dont Back up TempDBIF (@database = 'TempDB') RETURN-- Only Full backups for MasterIF (@database = 'Master' AND @backuptype <> 'Full') RETURN-- Don't take Log backups when the database isn't in full recoveryIF (@backuptype = 'Log' AND EXISTS (SELECT 1 FROM sys.databases WHERE recovery_model_desc <> 'FULL' AND name=@database))
  
RETURN-- Get the Database GuidSELECT @databaseGuid = database_guid FROM sys.database_recovery_status WHERE database_id = DB_ID(@database)-- Make sure the @backupPathRoot has a trailing \IF (@backupPathRoot NOT LIKE '%\') SET @backupPathRoot = @backupPathRoot + '\' -- Get the last full backup location and idSELECT @FullBackupLocation=physical_device_name, @FullBackupSetId=backup_set_idFROM msdb.dbo.backupmediafamily fJOIN msdb.dbo.backupset s ON f.media_set_id=s.media_set_idWHERE backup_set_id=(
  
SELECT MAX(msdb.dbo.backupset.backup_set_id)
  
FROM msdb.dbo.backupset
  
WHERE msdb.dbo.backupset.TYPE = 'D'
    
AND database_name=@database
    
AND database_guid=@databaseGuid )
IF (@FullBackupLocation IS NULL) BEGIN SET @backuptype='Full'  EXEC BackupLogEntry 'BackupDatabase', @database, 'No Full backup exists' END
ELSE IF
( @FullBackupLocation NOT LIKE @backupPathRoot + '%') BEGIN SET @backuptype='Full' EXEC BackupLogEntry 'BackupDatabase', @database, 'Full backup not in the correct folder' END
ELSE BEGIN
  EXEC MASTER
..xp_fileexist @FullBackupLocation, @FileExists output
  
IF (@FileExists = 0) BEGIN
    SET
@backuptype='Full'
    
SET @LogMessage = 'Full Backup Location (' + @FullBackupLocation + ') missing from the folder'
    
EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
  
END ELSE BEGIN
    IF
(@debugmessages=1) EXEC BackupLogEntry 'BackupDatabase', @database, 'Full backup exists'
  
END
END
-- Check to make sure the files are in the correct locationIF (@backuptype = 'Log') BEGIN

  
--Get differential backup location
  
SELECT @DifferentialBackupLocation=physical_device_name, @DifferentialBackupSetId=backup_set_id
  
FROM msdb.dbo.backupmediafamily f
  
JOIN msdb.dbo.backupset s ON f.media_set_id=s.media_set_id
  
WHERE backup_set_id=(
    
SELECT MAX(msdb.dbo.backupset.backup_set_id)
    
FROM msdb.dbo.backupset
    
WHERE msdb.dbo.backupset.TYPE = 'I'
      
AND backup_set_id > @FullBackupSetId
      
AND database_name=@database
      
AND database_guid=@databaseGuid
  
)

  
-- Check differential backup location
  
IF (@DifferentialBackupLocation IS NOT NULL) BEGIN
    IF
(@debugmessages=1) BEGIN SET @LogMessage = 'Checking Differential Backup Location (' + @DifferentialBackupLocation + ')' EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage END
    IF
( @DifferentialBackupLocation NOT LIKE @backupPathRoot + '%' ) BEGIN
      SET
@LogMessage = 'Differential Backup (' + @DifferentialBackupLocation + ') in wrong folder'
      
EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
      
SET @DifferentialBackupLocation = NULL
    
END
    ELSE BEGIN
       EXEC MASTER
..xp_fileexist @DifferentialBackupLocation, @FileExists output
      
IF (@FileExists = 0) BEGIN
        SET
@LogMessage = 'Differential Backup (' + @DifferentialBackupLocation + ') missing'
        
EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
        
SET @DifferentialBackupsetId = NULL
      
END ELSE BEGIN
        IF
(@debugmessages=1) EXEC BackupLogEntry 'BackupDatabase', @database, 'Differential backup found'
      
END
    END
  END

  IF
(@debugmessages=1) EXEC BackupLogEntry 'BackupDatabase', @database, 'Checking Log Chain'

  
DECLARE cur CURSOR FOR
    SELECT
physical_device_name
    
FROM msdb.dbo.backupmediafamily f
    
JOIN msdb.dbo.backupset s ON f.media_set_id=s.media_set_id
    
WHERE backup_set_id > COALESCE(@DifferentialBackupsetId, @fullbackupsetid)
      AND
database_name=@database
      
AND database_guid=@databaseGuid
      
AND TYPE = 'L'

  
OPEN cur
  
FETCH next FROM cur INTO @filename

  
WHILE @backuptype='log' AND @@FETCH_STATUS=0 BEGIN
    IF
(@debugmessages=1) BEGIN SET @LogMessage = 'Checking Log Backup Location (' + @filename + ')' EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage END
    IF
(@filename NOT LIKE @backupPathRoot + '%') BEGIN
      SET
@backuptype = ''
      
SET @LogMessage = 'Differential Backup (' + @filename + ') in wrong folder'
      
EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
    
END ELSE BEGIN
      EXEC MASTER
..xp_fileexist @filename, @FileExists output
      
IF (@FileExists = 0) BEGIN
        SET
@backuptype = ''
        
SET @LogMessage = 'Differential Backup (' + @filename + ') missing'
      
EXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
      
END
    END
    IF
@backuptype = '' BEGIN
      IF
@fullbackupsetid IS NULL SET @backuptype='Full'
      
ELSE SET @backuptype='Differential'
    
END
    FETCH
next FROM cur INTO @filename
  
END
  CLOSE
cur
  
DEALLOCATE curEND


SET
@now = GETDATE()SET @folder = @backupPathRoot + @@SERVERNAME + '\' + @database + '\'SET @folder = REPLACE(@folder,'/','')EXEC MASTER..xp_create_subdir @folderSET @filename = @folder + @database + ' ' + REPLACE(CONVERT(VARCHAR,@now,121),':','-') + ' ' + @backuptype + '.bak'SET @filename = REPLACE(@filename,'/','')
SET @LogMessage = 'Taking a ' + @BackupType + ' Backup,  Filename: ' + @filenameEXEC BackupLogEntry 'BackupDatabase', @database, @LogMessage
IF @backuptype = 'Log'
  
BACKUP LOG @database TO DISK=@filename ELSE IF @backuptype = 'Differential'
  
BACKUP DATABASE @database TO DISK=@filename WITH  differentialELSE IF @backuptype = 'Full'
  
BACKUP DATABASE @database TO DISK=@filename GO--alter database robtest set recovery full

--BackupDatabase 'RobTest','Differential','C:\Backup'

2 comments:

  1. i have no idea wtf this means.. :)

    ReplyDelete
    Replies
    1. Anything in particular that I can help to clarify for you?

      Delete