Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

4 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Thanks for that, I tried it but it did nothing.... The user I am logging on with is the server local Admin and I connect to management studio using windows credentials. –  flaggers Jun 19 '09 at 13:17
add comment

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.

share|improve this answer
    
I have checked the old Sql server and the new, your are correct the DBID's are not the same as I did not restore exactly the same databases, plus the destination server already had a few existing DB's on it. I have never encountered this level of sql before and will have to check out the significance of the DBID's. –  flaggers Jun 23 '09 at 11:23
add comment

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.

share|improve this answer
    
thanks for that, i have tested this on a few different machines with management studio installed as well as a completely fresh server + SSMS on it, with the same issues. –  flaggers Jun 23 '09 at 11:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.