Moving databases from one SQL 2000 server to a SQL 2005

• dharker

  Newbie

  Points: 0

  More actions

  October 18, 2008 at 7:37 am

  #199808

  Hi

  Forgive a naive question from a newbie. I have a very old server running SQL 2000 currently and have now built a new SQL 2005 server. What method should I use to move the databases from the old to the new?

  Currently I have backed up the user databases and restored them on the new server setting NORECOVERY, but the relatively small databases are still showing as "restoring" despite the logs saying the restore completed OK. Where have I gone wrong?

  Many thanks

  Dave

• george sibbald

  Newbie

  Points: 0

  More actions

  October 18, 2008 at 12:51 pm

  #886788

  you should have used the 'recovery' option rather than nocovery, you have left the database in a state where it has not done the 'recovery' part of restore (roll back uncommited tranasactions), and so would be able to accept later transaction log backups if required.

  simple to fix - for each database(dbname) issue this command:

  restore database dbname with recovery

  will only take seconds per database

  look up restoring databases in BOL - lots of info.

  ---------------------------------------------------------------------

• george sibbald

  Newbie

  Points: 0

  More actions

  October 18, 2008 at 1:08 pm

  #886789

  one other thing............

  as you are upgrading to SQL 2005 for each database you should :

  change the compatibility mode to 90 (sp_dbcmptlevel )

  run sp_updatestats

  run dbcc updateusage

  set default schema for users to dbo (or user that owned db objects if not dbo)

  run dbcc checkdb if not done before backup

  rebuild the indexes.

  then take a backup of the databases

  If you have not already done so remember you also have to transfer all those objects not held in the user databases:

  logins from master (use sp_help_revlogin - the 2000to2005 version)

  set the dafault languages correctly

  msdb jobs

  if you have them linked servers and dts packages (a whole different ball game)

  ---------------------------------------------------------------------