How to upgrade lower version of SQL server database to upper SQL server versions

The most important question severally asked by clients or junior DBAs, why should they upgrade to newer version of SQL server?

Upgrading to newer version of SQL server requires money, resources, time, training, and many more. So first decide you will have to need all these things when you are planning to upgrade to newer version of SQL server. As Microsoft stated SQL server 2005 is out of mainstream support that means you are not getting any security upgrade, support for SQL server 2005 & all lower versions. If you are still using SQL server 2005 or any lower version then I recommend you to upgrade latest version of SQL server that is SQL server 2014. You can get SQL server 2014 standard edition from here.

If you are upgrading to New version of SQL server then you are not going to get security updates only, you will have so many newer features in the newer versions like AlwaysOn, High-Availability, New features in database engine any many more.

If you have decided to upgrade to newer version of SQL server then don’t worry about your older database!!!

I will tell you different methods to upgrade your older databases on newly upgraded SQL server with step by step guide.

You can upgrade your lower version of SQL server database to newer version by any of the following methods:

• Copy Database Wizard Method
• Detach and Attach Method
• Backup and Restore Method

Copy Database Wizard: Copy database wizard helps you in upgrading your SQL server to later version of SQL server. If you have chosen this method to upgrade later version of SQL server then adhere the following requirement:
 

• No-one is trying to access the database during copy database wizard.
• Don't try to rename your database during operation.

Here is step by step guide to perform database upgradation through copy database wizard method:

• Connect to instance of database engineusing object explorer in SSMS.
• Click Databases ->Right click on database -> Click on Task -> Click on Copy database.
• Finish all the steps in database wizard.

After performing above steps, your database is available and upgraded automatically.

Note: To verify optimal performance of upgraded database, runs stored procedure (sp_updatestats) against the newly upgraded database.

Detach & Attach: It is another method to upgrade older SQL server database on newly upgraded SQL server. The database is immediately available & automatically upgraded to the newer version after performing Attach & Detach operations on the database.

Before using this method, please adhere the following restrictions:

• Master, Model & MSDB databases created using lower version of SQL server cannot attached in the new version.
• Make copy of data & log files. It helps in reattaching the original database on instances of original SQL server versions.

Here is step by step guide to perform database upgradation through Detach & Attach method:

• Detach the SQL server database from instance of SQL serverby using stored procedure sp_detach_db.

EXEC sp_detach_db 'Your_Database_database;

• Copy & move the data and log file of detached database.
• Attach the copied data & log file to an instance of upgraded SQL server version using create statement of SQL server with for attach or for attach rebuild log options.

Note: You have the copied of log file then use For Attach option otherwise use For Attach-Rebuild-Log Option.

Backup and Restore: It is the best and easiest method to upgrade a SQL server database from lower version to later version of SQL server. For this, you have to just take the full backup of SQL server database (Read my previous article on: how to take full backup of SQL server database) from lower version and restore it on the newer version of SQL server.

Note: It is the preferred method by most of the DBAs for upgrading lower version of database on later version of SQL server.

Now point comes, what is the difference between Detach/Attach and Backup/Restore method?

As I have described above most the expert DBAs recommend BACKUP/RESTORE method to upgrade a database from lower version to higher version. It helps in disaster recovery scenario because you have backup of your database at some location. But this is not absolute, because if you want to upgrade a very large database from lower version to higher version then backup & restore method may take a lot of time which is very frustrating. At this situation, detaching/attaching a SQL server database is a better way to upgrade a database. Detaching & Attaching operation is very fast but you need to aware that detaching a SQL server database will bring your database offline for a short time. And it does not provide disaster recovery option also.