March 29, 2005 at 1:03 am
I have a database which size around 30 GB. I have created the database backup using SQL EM Maintenance Plan. On the Maintenance Plan, I have
not include the option to delete the old backup files.
Since the database backup size is around 30 GB the job fails on deleting the
old backup.
Now I have schedulled a Job for file deletion as "master..xp_cmdshell 'DEL D:\SQLBackup\*.bak "
This job run successfull but the file is not getting deleted very often. Resulting the backup fail due to lack of space.
What is the best way delete the big old database backup files like this?
Actually the old backup file deletion job I schedulled 30 Mins before the
backup job starts.
March 29, 2005 at 4:05 am
what's reason of file not get deleted?
I would add /Q /F in del command.
Like "master..xp_cmdshell 'DEL /Q /F D:\SQLBackup\*.bak "
March 29, 2005 at 5:04 am
You could modify your SQL Backup Maintainence Plan to delete the file.
I mean if you are taking a backup at 10:00 am, you could ask the plan to delete the file 1 hour before you start your fresh backup.
I am not sure, but if you have a 30Gb database, I would recommend using combination of Full, Differential and T-Log backups.
However, it all depends on the kind of enviornment, database size, peak time and storage space available.
--Kishore
March 29, 2005 at 9:15 am
I use Transact-SQL to do the backup so that I have more control. For example, if I have two databases to backup, then my step1 is to delete the old BAK files using VB script. Then in step 2 and 3, I will code the BACKUP for each database. Our BAK files are 40GB to 80GB and VBscript did not have problem to do the delete. For each step that failed, I will direct the "On Failure" to the (last) step that will send the email to me so that I know which backup failed.
You can also add a step to your current job to delete the BAK files instead of a separate job.
March 30, 2005 at 8:42 am
Hi -- Since you mentioned space issues. Just wanted to let you know that there are compression options for backups. I was running low an space because of the backups and we started using SQLLiteSpeed (There are others as well). It does everything that they say it does and is very fast. Don't worry, I don't work for them or anything, the application just works very well.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
March 30, 2005 at 8:49 am
I believe that the deletion is done last in 'sqlmaint'. So if you say to keep 2 days online, you start with 2 backups then create the third and then the oldest is deleted. I've also noticed that 'sqlmaint' deletion does not handle backups that are not created with sqlmaint's specific naming scheme as well.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 30, 2005 at 9:05 am
I'd also second the use of SQLLiteSpeed - I don't work for them either - But it works for us on our large databases that we need to have disk backups of. Looking at your current situation. I would add the option in the SQL maint plan to delete the backup after a specified time. But, as I'm sure you are well aware of, that delete happens after the new backup is completed. And that means you need 60 GB to house two 30 GB backups for a few moments. (How many of us have had to explain storage requirements and go over this with our non-technical directors). Your current use of a job to delete the old backup is a sound option given the space crunch. You could run another job to chack the delete. I know this is reactive and not proactive but hey, it will get you by until you can get the storage you need.
Declare @command varchar (100)
Declare @result int
SELECT @command = 'dir D:\SQLBackup\*.bak'
EXEC @result = master..xp_cmdshell @command
IF (@result = 0)
BEGIN
exec master..xp_sendmail
@recipients = 'you@yourdomain.com',
@message = 'Files still exist',
@subject = 'Backup Warning'
END
Good Luck.
March 30, 2005 at 11:47 pm
March 30, 2005 at 11:55 pm
Kishore,
"
You could modify your SQL Backup Maintainence Plan to delete the file.
I mean if you are taking a backup at 10:00 am, you could ask the plan to delete the file 1 hour before you start your fresh backup.
I am not sure, but if you have a 30Gb database, I would recommend using combination of Full, Differential and T-Log backups.
However, it all depends on the kind of enviornment, database size, peak time and storage space available.
--Kishore"
Could you explain me in detail, in Maintenance plan how could you specify the option of deleting the file 1 hour before the start of fresh backup?
I am wondering is that sort of feature available in SQL Server 2000?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply