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