May 29, 2007 at 12:05 pm
I am in the process of trying to create a full maintenance plan by using the Business Intelligence Development Studio in SQL Server 2005. I have been trying to create a blank package that would include a nightly full backup and hourly transaction log backups. I am trying to create a package that I could reuse on multiple databases over many servers. It won't let me do this however, because I have to create a new connection for each new time I want to run the package. This is in fact slower than if I just ran the wizard. Is there a quick way to create maintenance plans in SQL Server 2005 without the wizard? If there is, could you please give me a step by step to do it?
May 29, 2007 at 2:15 pm
Your on the right track w/ using VS as opposed to the wizard. Create a string variables for your connection strings. You can then pass these in the /SET parameter with DTEXEC. If your new to SSIS and unfamilar with this, SQLIS.com has some good information out there.
Tommy
Follow @sqlscribeMay 29, 2007 at 3:50 pm
WHy? Why aren't you just using the Maintenance Plan in SSMS? That is why it's there.
-SQLBill
May 29, 2007 at 4:19 pm
If your working in a distributed environment i.e. running MSX, a single SSIS package running against multiple TSX (targets) is much easier to maintain.
Tommy
Follow @sqlscribeMay 30, 2007 at 7:18 am
My problem with having a single SSIS package running is that if there is a problem with that package then there becomes a problem with each server. We're not talking about 10 or 15 servers. We're talking about 50-100 servers that would have problems. It thus becomes easier to recover from disaster if there is a single maintenance plan on each server. In SQL Server 2000 I had a sql script that I could use the took advantage of the maintenance_plan_sp. This made it very efficient to create a maintenance plan on many servers. However, Microsoft removed the bloody sp from 2005 and I have been trying to create a suitable replacement.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply