Tuesday, June 21, 2011

How to Upgrade SQL server 2000 database to SQL server 2005?

A database administrator can upgrade SQL server 2000 database to SQL server 2005 database by several methods. Some of the methods are given below:

1.Copy Database Wizard Method
2.Detach and Attach Method
3.Backup and Restore Method

Copy database Wizard Method
: when you are using this method for upgrade then adhere the following requirement:
  • Make sure that none of the application or service is trying to access the database.
  • Don't rename the database during the operation.
Steps for upgrading database using Copy Database wizard method:
  1. Connect any instance of SQL server engine in SQL server management studio using object explorer.
  2. Right click the database
  3. Go to the tasks
  4. Copy the database
  5. Complete
Detach & Attach Method: When you are using this method for upgrade you should follow the following steps:

1. Detach the database from instance of SQL server 2000 by sp_detach_db stored procedure.

Note: Keep all the database files until the SQL server database has been successfully attached. Copy the detach instance of SQL server database

2. Attach the copied instance of SQL server 2005 by using Create database statement with attach rebuild log or attach options.

Backup and Restore Method: It is the easiest method for upgrading the SQL server 2000 database to SQL server 2005 database. For doing this, you have to just take the full backup of SQL server database 2000 and restore SQL server 2000 database on SQL server 2005 database.

Note: I prefer this method for upgrading the SQL server database. I believe that it is very simple and quicker.

No comments:

Post a Comment