December 1, 2005 at 10:36 am
Maintenance Plan
When I tried to create Maintenance Plan [MP] for full DB Backup and Hourly transaction log, I was not able to do that in one plan.
I have tried both design and wizard option.
The reason for that is in 2005 it will not allow you to have 2 schedules i.e. one for full backup say every night and other for transaction logs every hour. 2005 MP only create one job for plan.
In sql2000 in one plan you can specify this 2 task and create 2 schedules, which in turn will create 2 separate jobs one for full db backup and one for transaction log.
In addition to this 2005 maintenance plan also presents another issue when it comes to deleting old backup files after specified days. If you specify to create separate dir
in plan, then deletion module will not check sub-directories and you are left without removing files.
Cleanup Task of MP uses EXECUTE master.dbo.xp_delete_file this command
is non-documented and you hardly find any references to this.
Last thing is plan is not intuitive because to save the plan you need to jump back to main menu on top File à Save All option, which is not comfortable anyway. I am familiar to .net and frequently writes application in C# and I understand this look and feel of Visual Studio integration, but even than this is not straight forward as I too have lost changes to MP moving in management console of sql2005.
I have attended Microsoft Launch event in Atlanta on 29th Nov and talked to the experts over there, they definitely were very helpful but still I am not convinced on Microsoft’s standard argument that like other features MP is different in 2005.
I too hear another argument that there are different ways of getting things accomplished. In this case writing job with T-Sql and not using MP at all. I know that and this is what we all do every now and then as DBA or developer. Question here is Maintenance plan is part of Sql server 2005 and it provides option for all types of Backup then it is supposed to work right.
I will highly appreciate time and thoughts from all of you friends here.
I might be missing something on my learning curve to this new product or something is not working properly in new product.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
December 5, 2005 at 8:00 am
This was removed by the editor as SPAM
December 14, 2005 at 2:05 pm
Havne't looked at the Maint Plans, but that's something to check out.
January 23, 2006 at 4:52 am
I encountered another issue namely if you don't do a full installation of SQL Server 2005, the creation of maintenance plans fails with an "unexpected error".
Microsoft told us recently that you need to install SQL Server Integration Services. However they conceded this is a bug and shall be fixed with SP1.
April 19, 2006 at 4:27 am
Sameer,
Thanks for your post on this. I too have had a few problems with maintenence plans like yourself, but foundthe tip by Troy Ault helpful.
Seems like you have to specify the exact backup directory as well (I have tried a wildcard with no success), so the plans have to be changed with each new database... not nice.
The point about a Fix in SP1 is good... is this available in the CTP SP1?
Many thanks
Jim
April 19, 2006 at 6:35 am
Jim,
Thank You.
I will check with specification of exact dir.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply