December 9, 2011 at 10:52 am
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
December 10, 2011 at 1:09 am
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?
December 10, 2011 at 1:29 am
May I know the reason for appending the database backups in single file?
December 10, 2011 at 10:16 am
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.
December 11, 2011 at 5:00 am
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
December 11, 2011 at 6:21 am
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
December 12, 2011 at 10:52 am
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?
December 13, 2011 at 6:10 am
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
December 13, 2011 at 6:41 am
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
December 16, 2011 at 11:15 am
Just as a general question, couldn't you use a maintenance plan to do the backups and deletes of the old backups?
December 20, 2011 at 8:12 am
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