June 13, 2016 at 11:19 am
I have two types of maintenance backup plans for my data warehouse.
1) Backups of the warehouse database and framework database
2) Backups of the intake database and staging database, where all I really care about are my database setup and schema.
I've chosen to use the maintenance plans to back these up, by database.
My problem is that when I do maintenance cleanup, I'd like to keep just 1 copy of the database online (we also pull copies to a remote location), which means that I'd like to keep files < 1 day for the warehouse and framework, but < 1 month for the intake and staging database.
Currently, I have the maintenance task set up to "search folder and delete files based on an extension" of bak.
BUT, I would like to "delete specific file" but provide a wildcard, so that the embedded backup date is not needed. It also looks like I have to have one task for each of my databases (warehouse and framework).
When I embed my wildcard, I get the TSQL:
EXECUTE master.dbo.xp_delete_file 0,N'F:\SQL Backup\DW\My_DW_backup*.bak',N'',N'2016-06-11T14:20:21'
and the error message:
Msg 22049, Level 16, State 1, Line 0
xp_delete_file() returned error 2, 'The system cannot find the file specified.'
Am I missing something?
Thanks in advance
June 13, 2016 at 11:23 am
What you are missing is that you should never, EVER use maintenance plans!! 🙂
Ola.Hallengren.com. Every single client I ever visit starts using the awesomeness found there.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 13, 2016 at 11:44 am
I'm working on integrating them. I've gotten permission to include the indexing and statistics portion. But not the backup maintenance one (yet). So, I need a solution for today 😎
Best I've seen in my digging is to put the backups in two different folders and then go back to folder processing.
Any other hints?
June 14, 2016 at 8:13 am
AFAIK you will need to write your own file processor either in TSQL or externally with the scripting language of your choice. I would do it with the latter.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 14, 2016 at 9:09 am
There are lots of scripts here on the site that manage deleting old backups. I'd look through one of them and modify: http://www.sqlservercentral.com/search/?q=delete+old+backup&t=s&sort=relevance
As Kevin said, other languages do this better. PowerShell would excel at this. Look at the PowerShell Tool Time series: http://www.sqlservercentral.com/articles/PowerShell+Tool+Time/132606/
June 14, 2016 at 10:24 am
Those suggestions are making the Hallengren scripts look better all the time. I'll see if I can get approval to use those. I was hoping for a better understanding of the existing maintenance plan tasks, but I'm guessing that the functionality just isn't there.
Thank you for responding
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply