Introduction
This discussion details how to move a MS SQL 2000 database to SQL 2005 database using backup and restore functionality.

Scope
The scope of this discussion describes if a user wants to move a database resides in MS SQL 2000 to MS SQL 2005 by backing up a database in MS SQL 2000 and restore it in MS SQL 2005. Along with the database backup which specific configurations and settings pertaining to a database, needs to backup and restore, will determined by the user.

Intended Audience
The intended audiences for this discussion are a technical group or a user who is willing to perform this task and having basic knowledge of MS SQL Server.

Moving Database
Moving a particular database from MS SQL 2000 to 2005 can be performed in following two steps.
1. Backup a database from SQL 2000
2. Restore a backup copy into SQL 2005

Let’s see one by one.

Backup a database from SQL 2000
Backing up a database can be further divided into two steps.

• Locate a database
• Backup the actual database

Locate a database
To locate a database in SQL 2000 open the enterprise manager by performing following steps

1. Go to Start in the windows OS
2. Select Programs
3. Locate 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 specific database as shown below.

Backup the actual database
To backup the actual database now execute following steps

1. Right click on the database located in the above steps
2. Select All tasks >> Backup database as shown in above image
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 (Here In the image I have selected the default Backup folder path of Sql server)
7. Give the backup file name. Generally it should be <>Give .bak as an extension to the file (All the steps shown below in the image). One can decide its own backup copy name.
8. Click OK twice
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.

Restore backup copy to SQL 2005
Restoring the database can be divided into following steps

• Moving a backup copy
• Open SQL 2005 and Restore

Moving a backup copy
Now we have backup copy of our SQL 2000 database and its time to move it to a server where SQL 2005 has installed or to the desired location from where you can restore the database in SQL 2005. Generally it is recommended to move it to the backup folder of SQL 2005 installation which is by default C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup

Open SQL 2005 and Restore
Follow these steps to open and start restoration

1. Go to Start in the windows OS
2. Select Programs >> Microsoft Sql Server 2005 >> Sql Server Management Studio
3. It may ask the authentication details based on the installation configuration, please provide it
4. Once it connects to the Sql server expand the tree and select Databases
5. Now right click on the Databases and click Restore Database
6. The Restore Database window will open
7. Give the database name by which you want to restore it (Here I have given Northwind Generally it would be same because the connection string in the application refers this name)
8. Select From devices as to locate the source and backup copy
9. Click ellipsis “…” button it will open the window to specify backup
10. Click Add button to specify backup copy and location
11. Select the .bak file as shown in the image
12. Click OK twice

13. Now the in the restore list you can see one row with empty check box
14. Check the box and select the options as shown in the image
15. Check the particular restore options which suites your needs
16. You can change the path of the database to restore at particular location
17. Select one of the Recovery state
18. Click OK and wait for restoration (It may take time based on the size of the database)
19. One green progress bar at the bottom left will show the progress of restoration in %
20. Once it will restore the database it will show you the confirmation message, click OK
21. Now under database tree you can see the Northwind database restored successfully

Users and permissions
Once you restore the database it may require inserting users or giving specific permission to a particular user.
To insert new user specific to a database

1. Expand the database
2. Selects users >> Right click and add new user
3. While creating user you can choose the its role

To insert new user at the SQL server level

1. Select Security folder
2. Select and expand logins >> Double click on particular user and set its role

Potential problems
When a user move the database from 2000 to 2005 user dose not have to write any scripts for if it is doing backup and restore because it will move everything tables, stored procedures, views, user defined functions to 2005.
User need not to write another connection string to connect to Sql 2005.
To insert IIS_WPG user follow these steps

1. Login to Sql 2005
2. Locate the application specific database
3. Expand the database tree node and select security >> Users
4. Right click the Users and click New User
5. Give the name of the user to IIS_WPG

Note : – If user can not found IIS_WPG group while adding as a database user first insert it in the Sql Server security >> logins and then try to add specific to database

6. Click on ellipsis button which will open the Select login window
7. Now use browse button or Advance to locate IIS_WPG group or user and select it
8. Once IIS_WPG is selected give the Schema (application specific Database) and Membership to db_Owner

IIS_WPG is a group not a user and basically it includes following three users

1. NT AUTHORITY\NETWORK SERVICE
2. NT AUTHORITY\SERVICE
3. NT AUTHORITY\SYSTEM

Basically moving database from Sql 2000 to 2005 is wide topic which can’t fit in single discussion but backup and restore is easy to use technique for quick results.

Link which helps to better understand the movement from Sql 2000 to 2005

---

Possibly related posts: (automatically generated)

• SharePoint 2007 – Move Content and Configuration Databases
• Moving the Resource Database in SQL Server 2005
• How to Move sql system databases