Differential backup file cleaning | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Differential backup file cleaning

I have a differential backup plan which creates differential backups every one hour from 6AM-6PM during Week days.
Can any of You let me know the right SQL syntax if any on
"how to retain the latest 7 days differential backups and delete rest of them" please?
I have problem with the ‘delete" class of the backup statement.
Thanks in advance.
Gopi
What is the naming convention given to the backupname? You can still take help of db maintenance plan to retain x number of days backup. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thanks Satya,
The name is "Scheduler Differential backups(Morning)".
The script inside the job is
backup database scheduler to disk = ‘F:prod_backupschedulerscheduler_db_[STRTDT][STRTTM].DIF’ WITH DIFFERENTIAL,RETAINDAYS = 7,init The job is run from a Service account which is a member of domain admin group and has full control (ReadWrite etc) on the f: drive itself. This is one of such differential backups I take here and this one runs from Morning 7A.M -11 A.M.
But the files are not getting deleted even after 7 days
(Probably because my SQl statement does not instruct to do so! I suspect)
Thanks in advance
Gopi
IN that case add another step in the job schedule to delete the backups as per your requirement.
It seems to be an issue for maint.plan. Addition:
Take help from Umachander’s linkhttp://www.umachandar.com/technical/SQL70Scripts/Main30.htm for diff. backups script and add another job step to delete those files which are more than 7 days old or as per your requirement.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>