Restoring MSDB to another server

We moved a server from one box to another. I restored
MSDB to the new server and some of the jobs didn't run as
scheduled. What should I have done to avoid this? It
appears the timing was thrown off. Any experience with
this shared would be appreciated.
Thanks.
Hi,
1. Stop the SQL Agent service
2. Since you restored the MSDB from a differnt server you need to update
the SYSJOBS table wth current sql server name,
Because the servername in the table will be source server and all the jobs
will fail.
How to update:-
UPDATE MSDB..SYSJOBS
SET Originating Server = @.@.Servername
WHERE Originating Server <> @.@.Servername
3. start sql agent service
Thanks
Hari
MCDBA
"metoonyc" <metoonyc
"Sandi" <sandra_richardson@.harvardpilgrim.org> wrote in message
news:bc8f01c4895a$65be0370$a401280a@.phx.gbl...
> We moved a server from one box to another. I restored
> MSDB to the new server and some of the jobs didn't run as
> scheduled. What should I have done to avoid this? It
> appears the timing was thrown off. Any experience with
> this shared would be appreciated.
> Thanks.
|||We had already changed the name of the new server to what the server
name was. So this shouldn't apply to my problem. However, if I
restored msdb while SQL Server Agent service was still running, would
this have caused the problem?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Restoring MSDB to another server

We moved a server from one box to another. I restored
MSDB to the new server and some of the jobs didn't run as
scheduled. What should I have done to avoid this? It
appears the timing was thrown off. Any experience with
this shared would be appreciated.
Thanks.Hi,
1. Stop the SQL Agent service
2. Since you restored the MSDB from a differnt server you need to update
the SYSJOBS table wth current sql server name,
Because the servername in the table will be source server and all the jobs
will fail.
How to update:-
UPDATE MSDB..SYSJOBS
SET Originating Server = @.@.Servername
WHERE Originating Server <> @.@.Servername
3. start sql agent service
Thanks
Hari
MCDBA
"metoonyc" <metoonyc
"Sandi" <sandra_richardson@.harvardpilgrim.org> wrote in message
news:bc8f01c4895a$65be0370$a401280a@.phx.gbl...
> We moved a server from one box to another. I restored
> MSDB to the new server and some of the jobs didn't run as
> scheduled. What should I have done to avoid this? It
> appears the timing was thrown off. Any experience with
> this shared would be appreciated.
> Thanks.|||We had already changed the name of the new server to what the server
name was. So this shouldn't apply to my problem. However, if I
restored msdb while SQL Server Agent service was still running, would
this have caused the problem?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Restoring MSDB to another server

We moved a server from one box to another. I restored
MSDB to the new server and some of the jobs didn't run as
scheduled. What should I have done to avoid this? It
appears the timing was thrown off. Any experience with
this shared would be appreciated.
Thanks.Hi,
1. Stop the SQL Agent service
2. Since you restored the MSDB from a differnt server you need to update
the SYSJOBS table wth current sql server name,
Because the servername in the table will be source server and all the jobs
will fail.
How to update:-
UPDATE MSDB..SYSJOBS
SET Originating Server = @.@.Servername
WHERE Originating Server <> @.@.Servername
3. start sql agent service
Thanks
Hari
MCDBA
"metoonyc" <metoonyc
"Sandi" <sandra_richardson@.harvardpilgrim.org> wrote in message
news:bc8f01c4895a$65be0370$a401280a@.phx.gbl...
> We moved a server from one box to another. I restored
> MSDB to the new server and some of the jobs didn't run as
> scheduled. What should I have done to avoid this? It
> appears the timing was thrown off. Any experience with
> this shared would be appreciated.
> Thanks.

Restoring Master on another machine...

We set up a test box and having been trying to move an entire instance
from our production server to this one. I have moved all my created
dbs using the RESTORE WITH MOVE. Now I am trying to move the Master,
Model, and msdb. This is where I am having trouble. On the production
box the dbs are stored on D:\mssql$instancename\data. On the test
server the data is stored in C:\Program Files\Microsoft SQL
Server\mssql$instancename\data. Also the server names are different.
So I finally got the Master from production to restore over the test's
copy. Now the instance will not start. I was trying to use the
following code in cmd line to connect to the instace so I could then
copy model and msdb:

sqlservr -c -f -T3608 -T4022 -sINSTANCENAME

When this is trying to connect I read it is failing and saying that the
MDF and LDF may be corrupt or not there. The problem is it is now
trying to look in D:\mssql$instancename\data instead of C:\Program
Files\Microsoft SQL Server\mssql$instancename\data. Any ideas on how I
can change this, or if I have to reinstall the entire instace, not run
into this again?(murrayb3024@.gmail.com) writes:
> We set up a test box and having been trying to move an entire instance
> from our production server to this one. I have moved all my created
> dbs using the RESTORE WITH MOVE. Now I am trying to move the Master,
> Model, and msdb. This is where I am having trouble. On the production
> box the dbs are stored on D:\mssql$instancename\data. On the test
> server the data is stored in C:\Program Files\Microsoft SQL
> Server\mssql$instancename\data. Also the server names are different.
> So I finally got the Master from production to restore over the test's
> copy. Now the instance will not start. I was trying to use the
> following code in cmd line to connect to the instace so I could then
> copy model and msdb:
> sqlservr -c -f -T3608 -T4022 -sINSTANCENAME
> When this is trying to connect I read it is failing and saying that the
> MDF and LDF may be corrupt or not there. The problem is it is now
> trying to look in D:\mssql$instancename\data instead of C:\Program
> Files\Microsoft SQL Server\mssql$instancename\data. Any ideas on how I
> can change this, or if I have to reinstall the entire instace, not run
> into this again?

Before you set off, did you look at
http://support.microsoft.com/defaul...b;EN-US;224071?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The article is really useful
http://support.microsoft.com/defaul...kb;en-us;224071

Also attach & detach should save your lot of time.

Thanks
Ajay Rengunthwar
MCDBA

restoring from a backup

Using SS2000. I made a backup of a database using EM. I wanted to move the
database to another server. I moved the backup file to the new server and
created the database. Using EM, when I tried to restore from the file, it
didn't know about the file.
How do I restore from the file?
Thanks,
Dan D.
I figured out what I was doing wrong.
"Dan D." wrote:

> Using SS2000. I made a backup of a database using EM. I wanted to move the
> database to another server. I moved the backup file to the new server and
> created the database. Using EM, when I tried to restore from the file, it
> didn't know about the file.
> How do I restore from the file?
> Thanks,
> --
> Dan D.

restoring from a backup

Using SS2000. I made a backup of a database using EM. I wanted to move the
database to another server. I moved the backup file to the new server and
created the database. Using EM, when I tried to restore from the file, it
didn't know about the file.
How do I restore from the file?
Thanks,
--
Dan D.I figured out what I was doing wrong.
"Dan D." wrote:
> Using SS2000. I made a backup of a database using EM. I wanted to move the
> database to another server. I moved the backup file to the new server and
> created the database. Using EM, when I tried to restore from the file, it
> didn't know about the file.
> How do I restore from the file?
> Thanks,
> --
> Dan D.

restoring from a backup

Using SS2000. I made a backup of a database using EM. I wanted to move the
database to another server. I moved the backup file to the new server and
created the database. Using EM, when I tried to restore from the file, it
didn't know about the file.
How do I restore from the file?
Thanks,
--
Dan D.I figured out what I was doing wrong.
"Dan D." wrote:

> Using SS2000. I made a backup of a database using EM. I wanted to move the
> database to another server. I moved the backup file to the new server and
> created the database. Using EM, when I tried to restore from the file, it
> didn't know about the file.
> How do I restore from the file?
> Thanks,
> --
> Dan D.