I need to confirm the process for upgrading a standard SQL 2000 database to SQL 2005. As I understand it, there are at least 2 methods for upgrading. Back\Restore and Detach\Attach DB. In my case, I need to leave the original SQL 2000 Database online, so I believe it is best to do a Back\Restore.
Can someone confirm the process below and shed some light on my questions?
- On the SQL 2000 Server
- Take a Full Backup of the Database
- Databases->[Database]->All Tasks->Backup Database…, selecting 'Full', and saving the file (.bak)
- Move the backup file (.bak) to the new SQL 2005 server
- On the New SQL 2005 Server
- Copy the .MDF and .LDF files from the old SQL 2000 Server to the New SQL 2005 Server.
- Rename files if desired to something more standard
- Create a New SQL 2005 Database.
- Perform a Restore of the backup file (.bak)
- Select “Options” tab. This will have the mdf and ldf locations that were in the backup file. Change these to legitimate directories on your new Server.
- Change the Compatibility to “SQL Server 2005 (90)”
Questions:
- What performs the Upgrade?
- Is the SQL 2000 Database automatically UPGRADED during the restore, by the SQL 2005 Engine?
- Do I need to copy the .MDF and .LDF files from the old SQL 2000 Server to the New SQL 2005 Server?
- Do I need to create a New SQL 2005 database?
Thanks in advance for all of your help.
Jorge
Best Answer
Since you are using the Backup/Restore method, you don't need to copy the database (mdf) and log file (ldf) to the new server, just the backup files. You do not need to recreate the database either. The database, database files and log file will all be created during the restore process. The upgrade process is done by SQL Server when restoring your database backup.
Here is the method I use:
On SQL Server 2000
On SQL Server 2005
Once the database is restored on the new server. I run the following commands in a new query window
USE [mydb]
sp_updatestats -- updates statistics
This is because statistics are not automatically updated during the upgrade process.DBCC CHECKDB WITH DATA_PURITY
Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. This is important when upgrading from SQL Server 2000 to 2005 or 2008.
Associating database users with Logins
Now that your database has been restored, you need to associate its orphaned users with an actual login.
Method A: Fixing orphaned users
EXEC sp_change_users_login 'Report'
-- find orphaned users-- Login exists
-- Login doesn't exist
Method B: sp_help_revlogin
You also have an alternative called sp_help_revlogin which can help you to restore not only the login but the original password that went with it.
http://support.microsoft.com/kb/246133/en-us
It essentially entails creating a stored procedure on your old server to generate a script that will output all of your existing logins. You then copy the output of that stored procedure to your new server and restore the logins that are associated with the database you restored.