SQL Server 2005 error related to SQL Agent and Database Restores

Question

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.

Answer 1

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.

Answer 2

One problem may be the users that exist in those databases and may not have the same SID as on the other server. Maby that maintainance plan is executed using the credentials of an orphaned user. MS SQL has a stored procedure that will fix those orphan users: sp_change_users_login

Just go from SQL Management Studio to the database on the new server, go to Security and see what logins you have on that database. Then perform the repair command as bellow for each user.

use database

go

sp_change_users_login 'auto_fix' , 'username'

go

-- Where 'database' is the name of your restored database.

Answer 3

Since these databases are being moved from one server to another maybe you could check the sysdatabases table in Master to see if there are inconsistencies. If the dbids are not in sync, it might be possible that the old maintenance plans are having trouble viewing the restored databases.

Answer 4

You may find that it's as simple as refreshing the object explorer after you restore the database. SSMS caches a lot of info so that it doesn't have to keep going back to the server for the same data over and over, not to mention the performance improvement of already having the data in cache. Creating a maintenance plan is probably one place where cached info is read.