I have been working on setting up a maintanence plan on a few servers that I maintain and have been getting this error message whenever I try to use my SQL Server 2000 script on my 2005 servers. I am trying to create a maintenance plan that has a scheduled full backup and transactional logs backup.
"Msg 14262, Level 16, State 1, Procedure sp_add_maintenance_plan_db, Line 21
The specified @db_name ('RadiaProdInv') does not exist."
First off, could you please verify that this problem has to do with the sp not existing?
Second, could you please suggest some solutions to the problem?
If you need the rest of the sql please mention so in a follow up post. Or, if you have a working script, that would be great.
I found this information on another forum and it might help the problem. If anybody could explain this out in more simple terms or document the steps to perform this that would help out a lot. --Kyle
The sqlmaint.exe utility and the msdb tables that support SQL 2000 maintenance plans are going away. SQL 2005 includes the stored procs you mention, but the tables are there only if SQL 2005 was installed as an upgrade. A new SQL 2005 installation won't have the tables that those stored procs use.
SQL 2005 maintenance plans are implemented in SSIS tasks. You can automate the creation of maintenance plans, but probably not in T-SQL. Look at the "Working with packages programmatically" topic under "SQL Server programming reference" - "Integration Services programming" in BOL. The quickest way to figure it out might be to create a maintenance plan using the wizard, then write code to load the package and explore the objects in it. This should give you enough information to write code to create new packages on other servers. You may need to throw in some SMO code to create SQL Agent jobs to run the maintenance packages.