Backup being skipped

gsabowengsabowen Posts: 4
edited October 28, 2005 12:24AM in SQL Backup Previous Versions
I have a SP that performs a Full backup once a week on Saturday and Differential backups the other six days. It works fine on three servers but is skipping a large DB on one server. No email is produced and no log is created. The DB that is being skipped is 260 gig. Here is the SP:

-- CREATE PROCEDURE Proc_Red_Gate_BackupDBs_Differential AS

-- ****************************************************************************
-- Query Name: BackupDBs_Red_Gate_Differential_PBIMGSVR.sql
--
-- Description:
-- This script is meant to run daily. It creates a cursor and loops through it
-- to create either a full backup or incremental backup of any/all databases on
-- a server. It will create a full backup one day per week and incremental
-- backups the other six days.
-- This script stores the backups in the following format:
-- dbname_type..sqb IE: BSADB00_Full..sqb -or- BSADB00_Diff..sqb
--
-- Originaly Developed By:
-- Paul Hendryx (paulh@tekker.com) His code is copyrighted and has limited
-- warranties. For details, please visit:
-- http://www.Planet-Source-Code.com/xq/AS ... owCode.htm
--
-- Modified By:
-- GBo
-- 04/05/2005 Performs Full or Differential
-- 04/28/2005 Performs Full backup on new DBs (just created that day)
-- Otherwise it would attempt a Differential backup and fail
-- 08/18/2005 Uses Red-Gate's SQL Backup Extended Stored Procedure
-- ****************************************************************************

-- Declare our variables
DECLARE @BackupFile varchar(255)
DECLARE @DB varchar(50)
DECLARE @Description varchar(255)
DECLARE @Name varchar(50)
DECLARE @MediaName varchar(50)
DECLARE @BackupDirectory nvarchar(200)
DECLARE @SQLCmd varchar(300)
DECLARE @DayOfWeek varchar(10)
DECLARE @FullBkupDayOfWeek varchar(10)
DECLARE @BackupString varchar(1000)
DECLARE @EmailTO varchar(1000)

-- Set the backup directory don't forget the trailing \ otherwise it won't work.
SET @BackupDirectory = '\\SomeServer\backup\mssql\Daily\db\SomeDirectory\'

-- Set the day of the week you want to perform full backups
SET @FullBkupDayOfWeek = 'Saturday'

-- Set the string of email recipients
SET @EmailTO = 'SomeOne@Somewhere.com'

-- Loop thru all the databases that we should backup.
DECLARE Database_Cursor CURSOR FOR
SELECT [NAME] FROM sysdatabases
WHERE [NAME] NOT IN ('master', 'model', 'Northwind', 'pubs', 'tempdb')
ORDER BY [NAME]

-- Determine the day of the week this procedure is being run
SET @DayOfWeek = (SELECT DATENAME(dw, getdate()))

-- ****************************************************************************
-- This section creates full or differential backup
--
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @DB
@fetch_status = 0
BEGIN
-- Perform DB Integrity Check
SET @SQLCmd = 'DBCC CHECKDB (' + @DB + ')'
EXEC (@SQLCmd)

IF @DayOfWeek = @FullBkupDayOfWeek
OR -- Perform a full backup on DBs added today. Otherwise the differential will fail.
@DB NOT IN (SELECT DISTINCT Database_Name FROM MSDB.dbo.BackupSet WHERE Type = 'd')
BEGIN
-- Perform a Full Backup and initialize the media
SET @Name = @DB + '( Full Backup )'
SET @MediaName = @DB + '_Dump'
SET @BackupFile = @BackupDirectory + @DB + '_Full.sqb'
SET @Description = 'Full Backup At ' + CONVERT(varchar, CURRENT_TIMESTAMP)

-- Red-Gate SQLBackup Syntax
SET @BackupString = '-SQL "BACKUP DATABASE TO DISK = '''
+ @BackupFile + ''' WITH NAME = ''' + @Name + ''', PASSWORD = ''12345'',
DESCRIPTION = ''' + @Description + ''', INIT, VERIFY,
MAILTO_ONERROR = ''' + @EmailTO + ''', COMPRESSION = 2" -E'

EXEC master..sqlbackup @BackupString
END
ELSE
IF @DayOfWeek <> @FullBkupDayOfWeek
BEGIN
-- Perform a Differential Backup and initialize the media
SET @Name = @DB + '( Daily Backup )'
SET @MediaName = @DB + '_Dump'
SET @BackupFile = @BackupDirectory + @DB + '_Diff.sqb'
SET @Description = 'Differential Backup At ' + CONVERT(varchar, CURRENT_TIMESTAMP)

-- Red-Gate SQLBackup Syntax
SET @BackupString = '-SQL "BACKUP DATABASE TO DISK = '''
+ @BackupFile + ''' WITH NAME = ''' + @Name + ''', PASSWORD = ''12345'',
DIFFERENTIAL, DESCRIPTION = ''' + @Description + ''', INIT, VERIFY,
MAILTO_ONERROR = ''' + @EmailTO + ''', COMPRESSION = 2" -E'

EXEC master..sqlbackup @BackupString
END

-- Truncate the log file
BACKUP log @DB WITH Truncate_Only

FETCH NEXT FROM Database_Cursor INTO @DB
END

CLOSE Database_Cursor
DEALLOCATE Database_Cursor
-- ****************************************************************************

-- ****************************************************************************
-- This section creates a weekly rotation of full master DB backups
--
-- Perform a Full Backup of the Master DB (can't do differentials on it)
SET @DB = 'Master'

-- Perform DB Integrity Check
SET @SQLCmd = 'DBCC CHECKDB (' + @DB + ')'
EXEC (@SQLCmd)

-- Perform a Full Backup and initialize the media
SET @Name = @DB + '( Full BACKUP )'
SET @MediaName = @DB + '_Dump'
SET @BackupFile = @BackupDirectory + @DB + '_Full_' + @DayOfWeek + '.sqb'
SET @Description = 'Full BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP)

-- Red-Gate SQLBackup Syntax
SET @BackupString = '-SQL "BACKUP DATABASE TO DISK = '''
+ @BackupFile + ''' WITH NAME = ''' + @Name + ''', PASSWORD = ''12345'',
DESCRIPTION = ''' + @Description + ''', INIT, VERIFY,
MAILTO_ONERROR = ''' + @EmailTO + ''', COMPRESSION = 2" -E'

EXEC master..sqlbackup @BackupString

-- Truncate the log file
BACKUP log @DB WITH Truncate_Only
-- ****************************************************************************
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Can you confirm that the large database is being picked up by the cursor? Possible problems include a name that is too large for the variable (varchar(50)) or a name that has unicode characters (need nvarchar(50)) instead.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • gsabowengsabowen Posts: 4
    -SQL "BACKUP DATABASE [ITIServer] TO DISK = '\\SomeServer\backup\mssql\Daily\db\SomeDirectory\ITIServer_Full.sqb' WITH NAME = 'ITIServer( Full Backup )', PASSWORD = '12345',
    DESCRIPTION = 'Full Backup At Oct 27 2005 8:21AM', INIT, VERIFY,
    MAILTO_ONERROR = 'SomeOne@Somewhere.com', COMPRESSION = 2" -E

    -SQL "BACKUP DATABASE [msdb] TO DISK = '\\SomeServer\backup\mssql\Daily\db\SomeDirectory\msdb_Full.sqb' WITH NAME = 'msdb( Full Backup )', PASSWORD = '12345',
    DESCRIPTION = 'Full Backup At Oct 27 2005 8:21AM', INIT, VERIFY,
    MAILTO_ONERROR = 'SomeOne@Somewhere.com', COMPRESSION = 2" -E

    -SQL "BACKUP DATABASE [PDS1751] TO DISK = '\\SomeServer\backup\mssql\Daily\db\SomeDirectory\PDS1751_Full.sqb' WITH NAME = 'PDS1751( Full Backup )', PASSWORD = '12345',
    DESCRIPTION = 'Full Backup At Oct 27 2005 8:21AM', INIT, VERIFY,
    MAILTO_ONERROR = 'SomeOne@Somewhere.com', COMPRESSION = 2" -E
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Does the backup start if you run it manually using the command generated by the script?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • gsabowengsabowen Posts: 4
    petey wrote:
    Does the backup start if you run it manually using the command generated by the script?

    Yes it does.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    If you cut/paste the backup command and create a standalone SQL Server Agent job to backup that single database, and start the job manually, does it run?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.