Move SQL Server 2000 database to SQL 2005

This discussion is to assist those user wants to move a database in MS SQL 2000 to MS SQL 2005 by backing up a database in MS SQL 2000 and restore it in MS SQL 2005.

Moving a particular database from MS SQL 2000 to 2005 can be performed in following two steps.


1. Open and Backup a database from SQL 2000
2. Open and Restore a backup copy into SQL 2005
 
1. In SQL 2000

Open database
To open the database in SQL 2000 open the enterprise manager by performing following steps


1. Go to Start in the windows OS
2. Select Programs
3. Microsoft Sql Server
4. Once sub menu pops up click on the Enterprise Manager
5. Once Enterprise Manager Window opens, expand the tree and select Databases and then select your application.

To backup the actual database in SQL2000 by the following steps
1. Right click on the database located in the above steps
2. Select All tasks >> Backup database
3. SQL Server backup window will open >> Select Complete in backup
4. For the Destination click on Add button
5. The Destination window will open to give the file name and to decide the location of the backup file. Generally it should be C:\Program Files\Microsoft SQL Server\MSSQL\Backup
6. Click on the button with ellipsis “…” which will open to select the folder and to give a name to the backup file
7. Give the backup file name. Generally it should be Name.bak as an extension to the file
8. Click OK
9. Select Overwrite option (Whether to append or overwrite)
10. Now on the top select Options tab and check the necessary options for your database
11. Click OK to backup the database
12. Once the message comes which shows the backup operation has been completed successfully, click OK.




2. Restore backup from SQL2000 to SQL 2005