Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Some SQL Server Agent Jobs will not run after restore.

Posted on 2010-11-22
11
Medium Priority
?
350 Views
Last Modified: 2012-07-11
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.
0
Comment
Question by:Vitamin1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 8

Expert Comment

by:ShareefHuddle
ID: 34191163
Are you all patched and service packed?

0
 

Author Comment

by:Vitamin1
ID: 34191240
We're on SP2.
0
 
LVL 3

Accepted Solution

by:
DFW_Ed earned 1000 total points
ID: 34191361
have you tried dropping and recreating the jobs/schedules?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:Vitamin1
ID: 34191482
Yes, the job status hangs on "Executing Job Step 1".
0
 
LVL 4

Expert Comment

by:dquebe
ID: 34192796
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
 

Author Comment

by:Vitamin1
ID: 34196406
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
 

Author Comment

by:Vitamin1
ID: 34200193
dquebe,
Will a rebuild of the master database resolve this issue?  
0
 
LVL 4

Assisted Solution

by:dquebe
dquebe earned 1000 total points
ID: 34200450
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
 

Author Comment

by:Vitamin1
ID: 34200900
I'm not clear on how to identify the computer name in the Master database.  What table is it stored in?
0
 
LVL 4

Expert Comment

by:dquebe
ID: 34201209
sys.servers

SELECT @@SERVERNAME AS 'Server Name'


0
 

Author Comment

by:Vitamin1
ID: 34205184
The server name is the same as the computer name.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question