October 18, 2008 at 7:37 am
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
October 18, 2008 at 12:51 pm
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.
---------------------------------------------------------------------
October 18, 2008 at 1:08 pm
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)
---------------------------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply