Backup maintenance issue

  • I'm using the below procedure for backups. I keeping the backups on disk for 2 days and created the maintenace plan to delete backup files older than 2 days.

    This backup job starts at 9pm and completes by 1am.

    Problem:

    Day1(12/05/11) backup of mydb databases starts at 9pm and finishes by 1am and creates the backup files as Mydb__12-05-2011.bak (200GB)

    Day2(12/06/11) backup of the same database mydb starts at 9pm and finishes by 1am BUT adding to the same backup file Mydb__12-05-2011.bak and making it to 400 GB bak file.

    Before,Day3(12/07/11) backup starts at 9pm, there is a maintenance plan job runs at 8:55 which should delete backup files older than 2 days i.e backup file created on 12/05/11

    As the backup running over 12 am, the day 2 back file adding up to day1 backup file and maintenance plan is not deleting the older than 2 days backup

    If the backup finishes before 12 am, then everything is working as expected.

    Please advice how to solve this issue? (I know that I can change the backup schedule to start at 12 am to fix the issue. But I have the maintenance window for backups from 9pm to 2am only)

    Here is the procedure:

    USE [master]

    GO

    /****** Object: StoredProcedure [dbo].[BackupDatabase] Script Date: 12/09/2011 09:27:45 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE [dbo].[BackupDatabase]

    @DBName VARCHAR(100),

    @BackupPath VARCHAR(2000),

    @BackupType VARCHAR(4) = 'FULL'

    AS

    BEGIN

    DECLARE @BackupName VARCHAR(255)

    DECLARE @Prefix VARCHAR(50)

    DECLARE @backupSetId AS INT

    -- Make sure database exists on server

    IF NOT EXISTS (SELECT 1 FROM master.dbo.sysdatabases WHERE name = @DBName)

    BEGIN

    SELECT 'Database name does not exist'

    RETURN(1)

    END

    -- Make sure one one is trying to take tempdb backup

    IF @DBName = 'tempdb'

    BEGIN

    SELECT 'tempdb cannot be backedup'

    RETURN(1)

    END

    -- full, Differential and log backups are allowed, other wise do not do any thing.

    IF @BackupType NOT IN ('FULL', 'LOG', 'DIFF')

    BEGIN

    PRINT 'Invalid type of Backup selected, only FULL and LOG backup is allowed'

    RETURN(1)

    END

    -- If user did not give the back slash, add one.

    IF RIGHT(@BackupPath,1) <> '\'

    SET @BackupPath = @BackupPath + '\'

    IF @BackupType = 'FULL'

    BEGIN

    SET @BackupName = @DBName + ' Full Backup'

    SET @BackupPath = @BackupPath + @DBName +'_'+ CONVERT(VARCHAR(10),GETDATE(),110)+'.bak'

    SELECT @BackupPath AS Full_BackupPath_of_The_Database

    BACKUP DATABASE @DBName

    TO DISK = @BackupPath

    WITH NOFORMAT, NOINIT, NAME = @BackupName,

    SKIP, NOREWIND, NOUNLOAD, STATS = 10

    SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName

    AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset

    WHERE database_name = @DBName )

    IF @backupSetId IS NULL

    BEGIN

    RAISERROR (N'Verify failed for database', 16, 1)

    END

    RESTORE VERIFYONLY

    FROM DISK = @BackupPath

    WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

    END

    IF @BackupType = 'DIFF'

    BEGIN

    SET @BackupName = @DBName + ' Differential Backup'

    SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE(),120)

    SELECT @Prefix = REPLACE(@Prefix, '-', '')

    SELECT @Prefix = REPLACE(@Prefix, ':', '')

    SELECT @Prefix = REPLACE(@Prefix, ' ', '')

    SET @BackupPath = @BackupPath + @DBName + '_' + @Prefix +'.bak'

    SELECT @BackupPath AS Diff_Backup_Path

    BACKUP DATABASE @DBName

    TO DISK = @BackupPath

    WITH DIFFERENTIAL ,NOFORMAT, INIT, NAME = @BackupName,

    SKIP, NOREWIND, NOUNLOAD, STATS = 10

    SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName

    AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset

    WHERE database_name = @DBName )

    IF @backupSetId IS NULL

    BEGIN

    RAISERROR (N'Verify failed for database', 16, 1)

    END

    RESTORE VERIFYONLY

    FROM DISK = @BackupPath

    WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

    END

    IF @BackupType = 'LOG'

    BEGIN

    SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE(),120)

    SELECT @Prefix = REPLACE(@Prefix, '-', '')

    SELECT @Prefix = REPLACE(@Prefix, ':', '')

    SELECT @Prefix = REPLACE(@Prefix, ' ', '')

    SET @BackupName = @DBName + ' Log Backup'

    SET @BackupPath = @BackupPath + @DBName + '_Log' + @Prefix + '.trn'

    SELECT @BackupPath AS Log_Backup_Path

    BACKUP log @DBName

    TO DISK = @BackupPath

    WITH NOFORMAT, NOINIT, NAME = @BackupName,

    SKIP, NOREWIND, NOUNLOAD, STATS = 10

    SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName

    AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset

    WHERE database_name = @DBName )

    IF @backupSetId IS NULL

    BEGIN

    RAISERROR (N'Verify failed for database', 16, 1)

    END

    RESTORE VERIFYONLY

    FROM DISK = @BackupPath

    WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

    END

    END

  • So, every other day you alternate between adding to the same backup file and creating a new one? i.e. 200GB, 400GB, (delete), 200GB, 400GB?

    Am I missing something?

  • May I know the reason for appending the database backups in single file?

  • So, every other day you alternate between adding to the same backup file and creating a new one? i.e. 200GB, 400GB, (delete), 200GB, 400GB?

    Am I missing something

    Yes.

    And I do not want to append to the same file and the backup file older than 2 days should be deleted.

  • Mani-584606 (12/10/2011)


    So, every other day you alternate between adding to the same backup file and creating a new one? i.e. 200GB, 400GB, (delete), 200GB, 400GB?

    Am I missing something

    Yes.

    And I do not want to append to the same file and the backup file older than 2 days should be deleted.

    Then you must take care of NOINIT in following piece.

    BACKUP log @DBName

    TO DISK = @BackupPath

    WITH NOFORMAT, NOINIT, NAME = @BackupName,

    SKIP, NOREWIND, NOUNLOAD, STATS = 10

    { NOINIT | INIT }

    Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).

    NOINIT

    Indicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied. NOINIT is the default.

    INIT

    Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set on that device is overwritten, if conditions permit. By default, BACKUP checks for the following conditions and does not overwrite the backup media if either condition exists:

    •Any backup set has not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.

    •The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME option, earlier in this section.

    To override these checks, use the SKIP option.

    For More: http://msdn.microsoft.com/en-us/library/ms186865.aspx

  • your backup database command isn't initializing the bak file.

    Things to check:

    - the @BackupPath backup file name composition (as it still has the same name as the day before, and you aim for different file names)

    - check your backup statement so it initializes the bak file and that is what you want

    BACKUP DATABASE @DBName

    TO DISK = @BackupPath

    WITH NOFORMAT, NOINIT, NAME = @BackupName,

    SKIP, NOREWIND, NOUNLOAD, STATS = 10

    If the backup file names defer, there should indeed be no need to init the file.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hi ALZDBA,

    I'm able to resolve the backup file NOT to append to the existing files even if it crosses 12:00am by changing CONVERT(VARCHAR(50), GETDATE(),110) to CONVERT(VARCHAR(50), GETDATE(),120)

    IF @BackupType = 'FULL'

    BEGIN

    SET @BackupName = @DBName + ' Full Backup'

    SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE(),120)

    SELECT @Prefix = REPLACE(@Prefix, '-', '')

    SELECT @Prefix = REPLACE(@Prefix, ':', '')

    SELECT @Prefix = REPLACE(@Prefix, ' ', '')

    SET @BackupPath = @BackupPath + @DBName + '_' + @Prefix +'.bak'

    SELECT @BackupPath AS Full_Backup_Path

    But the problem is deleting the bak files older than 2 days..

    Here is the problem now:

    DeletebOldakfiles job runs at 8:55 pm and deletes the files older than 2 days and the script, I'm using looks for the DateLastModified < (Date() - iDaysOld (Please see the attached script that I'm using) .

    Backup job starts at 9pm. But the job finish time is over 12am and that is where the problem is. Please see the below what is happening

    day1 (12/8/11):

    The backup started at 9 pm and finished at 11:55pm and created the bak file Mydb_.201108210000.bak thus the Date modified is 12/08/2011 11:55 PM

    On day2: (12/9/11):

    DeleteOldbakfiles job runs at 8:55pm and deleted the bak files older than 2 days properly.

    Backup job starts at 9pm and finished at 12:10 am and created the bak file Mydb_.201109210000.bak and the Date modified stamp is 12/10/2011 12:10 AM

    On day3: (12/10/11)

    DeleteOldbakfiles job runs at 8:55pm and deleted the bak files older than 2 days(12/08/11) properly.

    Backup job starts at 9pm and finished at 12:15 am and created the bak file Mydb_.201110210000.bak and the Date modified stamp is 12/11/2011 12:10 AM

    On day4: (12/11/11)

    DeleteOldbakfiles job runs at 8:55pm and DID NOT delte the file Mydb_.201109210000.bak (olderthan 2 days, i.e 12/09/11)as the Date modified stamp was 12/10/2011 12:10 AM

    Backup job starts at 9pm and finished at 12:15 am and created the bak file Mydb_.201111210000.bak and the Date modified stamp is 12/12/2011 12:15 AM

    And the problem continues...please advice...is there any other script which will take address this issue?

  • How about using the oFile.DateCreated property to determine the age of your bak file ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • EXECUTE master.dbo.xp_delete_file 0,N'C:\Backup',N'.bak',

    N'12/11/2011 19:08:47'

    In this can u send the date time parameter that you want to delete and append it you job.

    Regards
    Durai Nagarajan

  • Just as a general question, couldn't you use a maintenance plan to do the backups and deletes of the old backups?

  • Hello,

    he is facing issue due to timelines in Maintanence Plan.

    Regards
    Durai Nagarajan

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply