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
GOCREATE 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'

Backup Script - Needs to be updated

Ack, It's been a while since I've updated my SQL backup script.  I've completely reworked it.  I'm in class this week, but will post it early next week.