Some SQL Server Agent Jobs will not run after restore.

Recently we had a data drive containing our SQL databases fail and had to restore from backups.  Everything is up and running fine except this problem with scheduled jobs.  

We have some jobs scheduled to backup and perform maintenance on the databases.  Those jobs run fine as scheduled.  

However we have some DTS packages that are scheduled but will not run since the restore.  I can run the packages maunally and they work fine, they just wont run as scheduled.  When I try to start the scheduled job from within the management console they hang.  I've tried stopping and startng the SQL Server Agent but that hasn't helped.

Thank you for your suggestions.
Vitamin1Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DFW_EdConnect With a Mentor Commented:
have you tried dropping and recreating the jobs/schedules?
0
 
ShareefHuddleCommented:
Are you all patched and service packed?

0
 
Vitamin1Author Commented:
We're on SP2.
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Vitamin1Author Commented:
Yes, the job status hangs on "Executing Job Step 1".
0
 
dquebeCommented:
When you restored, did you restore the "master" database? Did you restore to a computer with the same extact name or a different name? Did you change the named instance of the SQL server (e.g. default to something). Usually this type of failure points to a restore of the master to a different computer. The master database is sensitive to that. There are ways to fix it.
0
 
Vitamin1Author Commented:
dquebe,
Thank You.  Yes, that is exactly what we did.  We could not restore to the target machine because SQL Server would not start and our backup solution (Symantec Backup Exec) runs on SQL.  So we restored to a different machine and moved the files over.  I don't believe the name of the SQL server has changed.  So, how do we fix it?
0
 
Vitamin1Author Commented:
dquebe,
Will a rebuild of the master database resolve this issue?  
0
 
dquebeConnect With a Mentor Commented:
Take a look at this to ensure that your computer name matches in the master database: http://msdn.microsoft.com/en-us/library/ms143799.aspx.

The other issue is if you restored to another computer (even if the computer names at the same), the computer account and new local accounts (i.e. local administrator) will have different SIDs. These security references might need to be reset in master/msdb, but I have to keep looking around on that.

I had this problem once, and it came down to a small fix script to the master (it was computer name issue). I'll see if I can dig up how I did it.
0
 
Vitamin1Author Commented:
I'm not clear on how to identify the computer name in the Master database.  What table is it stored in?
0
 
dquebeCommented:
sys.servers

SELECT @@SERVERNAME AS 'Server Name'


0
 
Vitamin1Author Commented:
The server name is the same as the computer name.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.