SQL Server Performance

Restoring SQL 2000 MSDB into SQL Server 2005

Discussion in 'SQL Server 2005 General DBA Questions' started by dineshasanka, Dec 30, 2005.

  1. dineshasanka Moderator


    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
  2. satya Moderator

  3. dineshasanka Moderator

  4. satya Moderator

    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.
  5. dineshasanka Moderator

  6. LanLan New Member

    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
  7. LanLan New Member

    sorry, typo. it should read: I don't think 2005 db format compatible with 2000 db.
  8. dineshasanka Moderator

    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
  9. satya Moderator

  10. dineshasanka Moderator

    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
  11. satya Moderator

    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.
  12. dineshasanka Moderator

  13. dineshasanka Moderator

  14. satya Moderator

    [<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.<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  15. dineshasanka Moderator

Share This Page