February 9, 2009 at 11:38 am
Hi all,
I am fairly new to this. I need a way to back up a database and then delete just the older backup of that file if the backup is successful. Idera's SQL Safe does it like this, I am just not sure how to do it in SQL 2000 and 2005. I would delete all the old backups before I do the backup but what it the backup failed. Also I could delete the old backups when the entire backup has completed successfully. But both those options leave me in a delimia. If I delete them before the backup is complete and the job fails I loose all the backups. If I delete them after the job has completed and the backup does not complete successfully all the files remain. If that happens a few days in a row I would run out of drive space.
So the order for each file would be:
1. Backup
2. Check the backup
3. Delete older backup(older than 1 hour or anything)
...
Next file
1. Backup
2. Check the backup
3. Delete older backup(older than 1 hour or anything)
This possible through a osql script or maintenance plan?
Thank you.
February 9, 2009 at 11:47 am
In maintenance plans, there's a "Cleanup Backup task" that can do that for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2009 at 1:08 pm
Thank you for the quick reply. I tried that but must be missing something. I still have double the files until the job completes. Is there a specific switch that allows the previous named (older backup file) file of the current file being backed up to be deleted before sql proceeds to the next file?
February 9, 2009 at 9:38 pm
Do you mean:
- backup db 1
- delete old backup of db 1
- backup db 2
- delete old backup of db 2
...
The way the maintenance plans work, this doesn't happen. Most scripts that clean up backup files aren't t-sql, they're a scripting language that knows files. T-SQL doesn't work with files.
You could code something, but honestly, you shouldn't be that pressed for space. You want to have enough space to have an old backup until you are sure you have a new one.
February 10, 2009 at 8:01 am
You'd have to build something like that yourself, or set up your maintenance plan to have one backup and cleanup task per database. You can do that, but keep in mind that it'll have to be maintained manually if you add/remove databases from the server.
It should be possible to build a proc that will do what you need. You could use a cursor to step through the databases on your server, back up each one, and call a CLR proc that would delete the old backup once the new one is done, then go on to the next database. Could also use xp_cmdshell, but that opens up a whole can of security issues, so I'd definitely recommend CLR for that part.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 10, 2009 at 8:09 am
Hi.
I actually have started piecing something together. It works so far for what we need. I am trying to figure out how to return VERIFYONLY results and get the raiseerror script to except my @name variable properly. Strings sometimes baffle me in SQL.
What I am using now:
USE master
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @delfileName VARCHAR(256) -- used for delete backup
DECLARE @Command NVARCHAR(256) -- Script for delete backup
declare @backupSetId as int
SET @path = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
SET @delfileName = @path + @name + '_' + CONVERT(VARCHAR(20), DATEADD(day, -1, GETDATE()),112) + '.BAK'
BACKUP DATABASE @name TO DISK=@fileName
WITH RETAINDAYS=14, NOFORMAT, INIT, NAME=@name,
SKIP, REWIND, NOUNLOAD, STATS=10
select @backupSetId = position from msdb..backupset
where database_name=@name and backup_set_id=(select max(backup_set_id)
from msdb..backupset where database_name=@name )
[highlight=#ffff11]if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database '' + @name + '' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = @fileName WITH FILE = @backupSetId, NOUNLOAD, NOREWIND[/highlight]--GO
PRINT 'Purging ' + @delfileName
SET @Command = 'xp_cmdshell ''DEL "' + @delfileName + '"'''
EXEC (@Command)
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
When there is a need code it. I would not worry about any of this if we could upgrade to .NET 2.0 and use SQLSafe. Ugh.
Thanks.
February 10, 2009 at 8:21 am
Steve Jones - Editor (2/9/2009)
You could code something, but honestly, you shouldn't be that pressed for space. You want to have enough space to have an old backup until you are sure you have a new one.
More space is what we need but on a few of our servers we do not have luxury yet. We backup to disk then use veritas to backup which purges the files afterwards. In some rare case we have a failure and end up with multiple files. We want to keep it a clean as possible but still have some flexibility.
As for coding it out I may be close to figuring out something but it will only be temporary.
Thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply