October 14, 2008 at 3:21 pm
I have tried several ways to get this to run - it is a DIFFERENTIAL backup so I cannot use a canned maintenance plan for it. I cannot for the life of me get my old files to purge when older than 6 days...any help is greatly appreciated.
/*****************Script For Differential Back Up of Database****************/
--##STEP 1 --> Declaration of Variables
declare @dirPath varchar(60)
declare @fileName varchar(60)
declare @destFilePath varchar(60)
declare @timeStamp varchar(30)
--##STEP 2--> Assigning values to variables
select @dirPath = 'E:\MSSQL\Backup'
select @timeStamp = convert(char(8),getdate(),112)
select @fileName = 'CedarRapids_W_Diff_Backup'
select @destFilePath = @dirPath + '\' + @filename + '.bak'
--##STEP 3--> Creating the subdirectory if it doesn't exist
--EXECUTE master.dbo.xp_create_subdir @dirPath
--##STEP 4--> Differential BackUp
BACKUP DATABASE [CedarRapids_W]
TO DISK = @destFilePath WITH DIFFERENTIAL, INIT, RETAINDAYS = 6, NOFORMAT, NAME = @fileName , NOSKIP, NOUNLOAD, STATS = 10
Thank you,
SSkaar07
October 14, 2008 at 4:47 pm
The RETAINDAYS won't actually cause the files to delete, it just specifies when the file can be overwritten (i.e. with a new backup), and only SQL Server honors the setting - you could go into the filesystem and delete the file if you were so inclined. I haven't used this setting before, so I don't know much about it.
What you want is the equivalent of the DelBkUps option for the maintenance plan, but I can't remember what procedure or SQL that option ends up triggering. Essentially it spins through the backup directory looking for files that are past their date (either by filename, since the datetime is appended to the backup file or by creation date, I don't remember which there either) and deletes the files, but it is not part of the BACKUP statement itself. It run separately.
If you create a maintenance plan with DelBkUps set, then profile it, I think you'll see the statement it runs, and you can copy that or create your own.
Good Luck!
Chad
October 14, 2008 at 5:11 pm
I found it - it's an extended stored proc in the master database: master.dbo.xp_sqlmaint. It's specific for maintenance plans, but you might be able to use it to just to the deletes.
Good Luck!
Chad
October 15, 2008 at 6:44 am
SUPER! Thank you for your reply - I looked at Books Online, my Admin Pocket Guide, etc. and it appeared that the RetainDays was the way to delete, with INIT as a parameter. I wonder if Init will only work though when the backups have the same name - I always append a date to the end of mine.
I will try the extended proc and see if that works.
Thanks again,
Sandy
October 15, 2008 at 6:52 am
INIT only works when backups have the same name. Using the INIT flag simply deletes the previous backup before writing to the file (as long as the names are equivalent). If you do not use INIT, backups will be appended into a single file.
RETAINDAYS is used to ensure that SQL Server cannot overwrite a backup file untill it is N days old. Trying to use INIT with a backup file that has not expired yet will result in the following message:
Msg 4030, Level 16, State 1, Line 1
The medium on device 'c:\qwd.bak' expires on Oct 18 2008 8:50:54:000AM and cannot be overwritten.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Again, this does not physically delete the file after N days, just makes sure that SQL Server cannot overwrite it until after N days have passed. If you want physical file deletion, you are best off using maintenance plans as the previous poster mentioned.
October 15, 2008 at 7:08 am
No luck 🙁 I get the syntax for the following. Do you know is there a way to do Differential backups in a maintenance plan in 2000? I may just do the delete in a .bat file and run that each day.
Thanks again!
Sandy
---------------------------
sqlmaint
[-?] |
[
[-S PW-ASSESSOR]
[-U cityassessor [-P CRCityAdmin_ASSESSOR!]]
{
[ -D 'CedarRapids_C' | -PlanName name | -PlanID guid ]
[-Rpt text_file]
[-To operator_name]
[-HtmlRpt html_file [-DelHtmlRpt ] ]
[-RmUnusedSpace threshold_percent free_percent]
[-CkDB | -CkDBNoIdx]
[-CkAl | -CkAlNoIdx]
[-CkCat]
[-UpdOptiStats sample_percent]
[-RebldIdx free_space]
[-WriteHistory]
[
{-BkUpDB [backup_path] | -BkUpLog [backup_path] }
{-BkUpMedia
{DISK [ [-DelBkUps ]
[-CrBkSubDir ] [ -UseDefDir ]
]
| TAPE
}
}
[-BkUpOnlyIfClean]
[-VrfyBackup]
]
}
]
October 15, 2008 at 9:15 am
One final note on this thread -
I decided to use shell script to delete. Actually, I deployed a solution posted on SQL Server Central from http://qa.sqlservercentral.com/scripts/Backup+%2F+Restore/30066/
The shell command uses dynamic Sql. It makes me a bit nervous however, I'm only using it until I get everything migrated to 2005.
Thanks to the author Wesley, for this script.
June 30, 2011 at 1:47 pm
you can delete .bak file as part of full backup maint plan. it works as long as full and diff have same file extension.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply