Maintenance plan is over-writing and deleting previous backup
-
Good Morning,
I have created a maintenance plan to backup a very small database every night. I would like to keep the backup from the night before. I would like to keep the backups of Monday thru Friday for a week. Ex. backup_monday.BAK, backup_tuesday.BAK, backup_wednesday.BAK and do on. Is there a way to do this?
Right now I have the backup set to run every night. I have it set up to delete backups that are a week old. Also, I have the backup set to over write because I thought it meant to over write the one from the previous week (not the previous night). For example, Tuesday backup from this week would over right Tuesdays backup from last week. Unfortunately this is not happening.
The backup from Monday night was deleted and only Tuesdays backup exists.
If I use append wont Tuesdays backup be added to Mondays and then Wednesdays added to Monday and Tuesdays? I do not want that. I want each backup for each day to be separate and kept for a week.
Below is the t-sql for the backup and maintenance plan clean up.
--to backup the data
BACKUP DATABASE [tam_help] TO DISK = N'D:\LAN2005\BACKUP\tam_help_2005\tam_help_nightly.BAK' WITH NOFORMAT, INIT, NAME = N'tam_help_backup_20070627100110', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'tam_help' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'tam_help' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''tam_help'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\LAN2005\BACKUP\tam_help_2005\tam_help_nightly.BAK' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
---to delete the backup from the week before.
EXECUTE master.dbo.xp_delete_file 0,N'D:\LAN2005\BACKUP\tam_help_2005\tam_help_nightly',N'',N'06/20/2007
-
There is a couple of diffrent ways to accomplish what I think you want.
The easiest would be to set up a maintenace plan that creates a new file every night, they would be in the form of dbname_YYYYMMDDTIME.bak. Do not append to the file, do not overwrite. THen set up a delete file tasks that cleans up files older than 7 days. This would leave a rolling 7 day archive of backups.
The other way is to have a seperate maintenance plan for every night of the week that overwrites the file, you would then call the file something like databasename_Monday.bak etc.
First one is a lot less work though
-
Thank you so much for your quick response. I think I understand. But have a few questions. 🙂
Can you tell me what is the T-SQL for the task to create a file every night? Can I just modify the T-SQL I have in my maintenance plan right now? Should this be done in the maintenance job or in the form of a SQL Server agent job that is run every night? Can the delete job also be done in the maintenace job or should it be done in a separate SQL server agent job and scheduled to run? Please advise.
-
It would be easiest to use the Maintenance Plan, for the first solution it should be able to do everything I said .
I run the deletion as the second step of my backup, and make it dependant on the backups finishing succesfully. I.e. just in case I do not want my backups deleted until I have a good new backup of the databases.
I don't use the Maintenance Plan Wizard, and I don't think it allows for deleting files (I might be wrong on this). But building your own is fairly straightforward.
-
Okay, I modified the T-SQL of the job and it's putting dbname_YYYYMMDDTIME exactly like that. When backing up it's putting the dbname then 'YYYYMMDDTIME', instead of the actual data and time when the backup occured. Below is the SQL for the backup. What needs to be changed?
BACKUP DATABASE [tam_help] TO DISK = N'D:\LAN2005\BACKUP\tam_help_2005\tam_help_YYYYMMDDTIME.BAK' WITH NOFORMAT, NAME = N'tam_help_backup_YYYYMMDDTIME', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'tam_help' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'tam_help' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''tam_help'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\LAN2005\BACKUP\tam_help_2005\tam_help_YYYYMMDDTIME.BAK' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
-
Yeah that was not literal, you have to get the datetime in there. Not sure why you keep insisting on changing the SQL code directly instead of using the maintenance plan, the format I put is what the maintenance plan will produce for file names.
If there is a good reason why you have to use SQL directly, there is a post by me on here somewhere with the code I use to backup my systems. (was a response to someone else having problems with the maintenance plan)
-
I did create the backup using the maintenance plan. That was in my original post. The maintenace plan I'm using is over writing the backup from the night before. When you select to backup to a disk across one or more files it gives you the option to append or over write if files exist. You have to choose one.
The other choice is to create a backup file for every database. This selection does not ask you to over write or append. Is this the one I should choose? If so, this one backs up the database to a folder and not a device.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply