Restore a SQL 2000 DB to SQL server 2005

• jean.mesidor

  Grasshopper

  Points: 18

  More actions

  June 23, 2009 at 12:02 pm

  #201707

  Hello,

  We have a Database that used to be on a Windows NT server box that crashed and won't come back. Luckily, we have a backup and was able to restore the SQL 2000 database to the SQL 2005 server database. Since this is new to me, I would like to know if there are any other steps that I need to do to make the new database work successfully.

  Thanks for all the help!

• MannySingh

  SSChampion

  Points: 12465

  More actions

  June 23, 2009 at 12:16 pm

  #1014163

  as of now, do not change the compatibility level to 90 from 80 as there might be some things like join predicates you will want o to check before making it cmptlevel 90.

  also run the DBCC checkdb (database name) with all_ERRROMSGS

  run DBCC UPDATEUSAGE..

  And you should be good.

  immediately get the database in a daily full backup plan. and trans log if required.

  Maninder
  www.dbanation.com

• jean.mesidor

  Grasshopper

  Points: 18

  More actions

  June 23, 2009 at 12:31 pm

  #1014170

  Both commands completed successfully. Is it ok to change the compatibility level now? Like I said, I am pretty new to SLQ. Can you also give the command to change the compatibility level?

  Thanks for the help!

• Johan Bijnens

  SSC Guru

  Points: 135381

  More actions

  June 23, 2009 at 1:25 pm

  #1014215

  Indeed, perform full db maintenance

  start with:

  DBCC CHECKDB (0) WITH ALL_ERRORMSGS, DATA_PURITY;

  go

  print '' ;

  print '*********************************'

  print '**'

  print '** If DBCC CheckDB returned DATA_PURITY errors, you must view http://support.microsoft.com/kb/923247 !!! and repair the issues !!!'

  print '*********************************'

  print '**'

  print '' ;

  print '--' ;

  Then:

  dbreindex / sp_updatestats / dbcc updateusage(0) with count_rows

  AND MAKE A NEW BACKUP to a new file !! (always keep your old copy !)

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me