I have been restoring sql 2005 databases from one server to another (similar spec, same version and service pack etc.) and have encountered the following.
The problem starts with the restore of the database –
The restore process works fine I use the management studio and perform a restore which completes without a problem, all of the data is there and can be used.
However when I try to run an existing maintenance plan backup I get the following error:
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
Additional information -> Job'Full_Backup.Subplan_1'failed. (SQL ManagerUI)
The additional information gives me the following
Program Location:
at Microsoft.SqlServer.Management.SqlManagerUI.MaintenancePlanMenu_Run.PerformActions()
At this time I tried to recreate the maintenance plan, when I get as far as choosing the databases to be part of a full backup the database that I have just restored is missing from the selection pane.
I have replicated this issue on 2 'fresh' servers both with new installs of SQl server 2005. To my reckoning the restore operation is the culprit so if there was some way to trace what is was doing perhaps to one of the system databases that I could then investigate.
This has been an annoyance for a few weeks and any help would be appreciated.
Best Answer
I seem to have stumbled upon the answer. It would appear that the original SQL server was an upgraded from 7 to 2000 to 2005! crazy but apparently has been in use for 8 years well before my time.
The problem seems to be related to the database compatibility, see the following msoft article
link text
"Databases set to compatibility level 70 or lower are not displayed"
I have tested this by changing the level to 90 and sure enough it now works.
thanks to all who replied, this site is going to be very useful.