SQL Server Performance Forum – Threads Archive

Restoring SQL 2000 MSDB into SQL Server 2005

I wish to restore SQL 2000 msdb to SQL Server 2005. other than the taking and backup and stop the agent, what are the precautions that I should take.
—————————————-
http://spaces.msn.com/members/dineshasanka

Check this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;224071 for steps to take in case of system database restore/attach. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

thanks for the help satya. But does it covers msdb from 2000 to 2005 —————————————-
http://spaces.msn.com/members/dineshasanka

I think the section Moving the MSDB database (SQL Server 2000 and SQL Server 2005) would help you and I’m also guessing you may need to upgrade and normal detach/attach or backup/restore wouldn’t work.
(I donot have SQL 2005 instances in hand to test though) Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I also think that back and restore will not work. Anyway I will try that option
where can I find such upgrade tool —————————————-
http://spaces.msn.com/members/dineshasanka

I don’t 2005 db format compatible with 2005, therefore restore/detach doesn’t work. the only solution is to bulk copy or use the third-party tool

sorry, typo. it should read: I don’t think 2005 db format compatible with 2000 db.

No you can’t restore. I got following error TITLE: Microsoft SQL Server Management Studio
—————————— Restore failed for Server ‘DINESHSQL2005’. (Microsoft.SqlServer.Smo)
For help, click:http://go.microsoft.com/fwlink?Prod…ceptionText&EvtID=Restore Server&LinkId=20476
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup of the system database on the device D:Workmsdb cannot be restored because it was created by a different version of the server (8.00.0194) than this server (9.00.1399). (Microsoft.SqlServer.Smo) For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.1399.00&LinkId=20476 ——————————
BUTTONS: OK
——————————
—————————————-
http://spaces.msn.com/members/dineshasanka

Refer to Rob’shttp://www.sqlservercentral.com/columnists/rpearl/installingandupgradingtosqlserver2005.asp link for more information. http://www.microsoft.com/downloads/…7A-662C-4319-AFE7-B52D1568C30A&displaylang=en too fyi. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

thanks for the links.
I was able to get all the dts which are saved in sql 2000 by usign import wizard in the sql 2005. Now the question is with jobs?? —————————————-
http://spaces.msn.com/members/dineshasanka

WHy not script them and use it on new server (but check hard coded servername values if any). Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Yes I think that is the only solution left —————————————-
http://spaces.msn.com/members/dineshasanka

satya, I tried this but I need to script one job each. As there is no way I can select set of jobs to scritps —————————————-
http://spaces.msn.com/members/dineshasanka

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Sometimes you cannot achieve with GUI tools and your best bet is to use SQL-DMO to obtain a list of jobs. Use the JobServer object and iterate through the Jobs collection. To obtain each job’s script, you can call the Scripts method for that job. I hope you know about Nigel’s website about such useful scripts. <hr noshade size="1">Satya SKJ Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

Yes true —————————————-
http://spaces.msn.com/members/dineshasanka

]]>