Re: Moving msdb database fron one server to another

[gromit114]

We recently moved are database from one server to another both are running SQL Server 7.0. In order to save time we also detached the msdb database and moved it over to the new server.

However we have now found that the xp_sendmail and maintenace are no longer working also DTS packages take ages. Does anyone know if moving the msdb database would affect it!

 

[tlbroadbent]

All the jobs and pacakges point to the old server when you move MSDB in this way. That was not a wise decision, actually. You should have scripted the jobs or used the DTS Job Transfer task. You can move packages as described at ...

Transferring DTS Packages

http://www.sqldts.com/main.asp?nav=1,6,204,0

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[gromit114]

Thank You for your comments Terry we realised this was not a wise move however we have serious performance issues now and also xp_sendmail does not work.

It also takes twice as long to DTS a text file into the database than our old server and the new one is double the spec.

Do you think that moving the msdb database would cause this to happen!

 

[calidrake]

Do you happen to have the old server in working order??

 

[gromit114]

Yes we do!

 

[calidrake]

You can probably restore the master database, and then the msdb database onto the new server. You would probably have to have the new server name be the same as the old server name. To do this you'll have to put the SQL server in single-user mode.

sqlservr.exe -c -m

To do it with NT get into the service manager, and highlight the MSSQLServer service. On the bottom of the window there is a textbox marked "startup options". put in the "-c -m". Then stop and start the service. You are now in single-user mode.

This might take a little time, you'll have to schedule an outage with your user base.

But this is the best solution I would think. Hope this helps

 

[tlbroadbent]

Have all the connections in the DTS packages been modified to reflect the new table and import file locations if these changed? Have you verified the DTS options such as "Use Fast Load, Table Lock and Insert Batch Size?

WHat happens if you recreate a DTS import package? Is the perfromance good or bad compared to the old server? Could the issue be differences in table configurations, indexes, available RAM or disk space?

Have you reviewed the the following knowledgebase articles regarding SQLMail?

How to Configure SQL Mail [ignore]

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q263556

Frequently Asked Questions - SQL Server - SQL

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q311231

Common SQL Mail Problems

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q315886

[/ignore] Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[calidrake]

You probably figured this out, but just create a full backup of the Master and MSDB databses from the original server. If you already knew this, I don't mean to insult you. I just wanted to cover all the points.

 

[gromit114]

We have reconfigured all the DTS Packages with the new server name.

We also this morning created a new server on a bog standard stand alone PC and installed SQL Server 7.0 with the same spec as the new server and carried out the same process and we have recieved no problems on this server.

One other thing we did before was we had SQL 2000 installed on the new one but uninstalled this and put SQL Server 7.0 back on. Could this have affected the settings.

I have referred to the SQL Books online with regard to the SQL Mail configuration and followed it to the letter. However this has no affect. What is strange is that the SQL Mail Agent Email for operators works without any problem.

We are gettign to the point now where we are planning to rebuild the entire server and start from strach!

 

[tlbroadbent]

We've gone through many of the same steps and have not encountered problems. For example, we installed SQL 2000 on a server, uninstalled it and installed SQL 7 on the server without problems. We've upgraded from SQL 7 to SQL 2000. We've installed a new SQL 2000 server and transferred databases, jobs, packages, logins, etc without problems.

It is beginning to appear that you may need to rebuild the server. There may be some configuration issue that isn't readily apparent. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[gromit114]

We are currently in the process of rebuilding the server. We have detached the database and also the msdb database and saved teh files in the directory.

Once the rebuild is finished we are going toi reattach these systems. With any luck we will have solved the problem.

Many thanks for your help and suggestions will let you know of the result!