Manage Learn to apply best practices and optimize your operations.

Upgrade SQL Server 2014 SP1 in eight steps

Upgrade from SQL Server 2008 R2 to SQL Server 2014 SP1 using backup and restore. This eight-step plan takes you through everything from log backups to end-to-end testing.

My organization decided to upgrade one of its financial databases from SQL Server 2008 to SQL Server 2014. In May,...

Microsoft released SQL Server 2014 Service Pack 1 (SP1). This gave us the perfect opportunity to upgrade a mission-critical database and leverage the new features available in SQL Server 2014 SP1.

You can use one of three methods to upgrade SQL Server: backup and restore, detach and attach, or copy only wizard. In this tip I will use backup and restore to demonstrate a SQL Server upgrade.

A database backup and restore is among the easiest methods available for a database administrator who wants to upgrade a single user database from SQL Server 2008 or SQL Server 2008 R2 to SQL Server 2014 SP1. In this example, I will back up and restore a finance database from SQL Server 2008 SP4 to SQL Server 2014 SP1. Here are the high-level steps for moving a database to a higher version of SQL Server using backup and restore.

1. Perform full and transaction log backups

Using SQL Server Management Studio (SSMS) connect to the SQL Server 2008 instance and execute the T-SQL script in Figure 1 to perform a full and transactional log backup of the finance user database.

Perform a full transactional log backup.
Figure 1. Perform a full transactional log backup.

2. Moving database files

After a successful backup, manually copy the full and transactional log backup files to the SQL Server 2014 SP1 instance.

3. Restoring a database on a SQL Server 2014 instance

Using SSMS, connect to the SQL Server 2014 SP1 instance and execute the T-SQL command in Figure 2 to restore the finance database to the SQL Server 2014 SP1 instance. First, use the NORECOVERY statement to restore the full backup; then use the RECOVERY statement to restore the transactional log backup file.

Restore the full backup and the transactional backup.
Figure 2. Restore the full backup and the transactional backup.

4. Transfer user logins

The next step is to transfer or create logins to the new SQL Server 2014 SP1 instance for end user connectivity. Verify the login access to the finance database so that, once the database goes live, all users can connect to the database successfully.

5. Change the database compatibility level

Now that you have successfully upgraded your user database to a SQL Server 2014 SP1 instance, you need to change the compatibility level to 120. You can achieve this by executing the T-SQL code in Figure 3.

Change the compatibility level.
Figure 3. Change the compatibility level.

After upgrading the database, execute the DBCC UPDATEUSAGE command in Figure 4. This command will report and correct pages and row count inaccuracies in the catalog views, if there are any.

DBCC UPDATEUSAGE command.
Figure 4. DBCC UPDATEUSAGE command.

Next, execute the DBCC CHECKDB command in Figure 5 to verify the logical and physical integrity of the user database.

DBCC CHECKDB command.
Figure 5. DBCC CHECKDB command.

6. Rebuild all Indexes

Once the database is successfully upgraded, rebuild all indexes before the database goes live.

7. Update application configuration files

Update all application configuration files to reflect the new SQL Server name.

8. Perform end-to-end testing

Perform end-to-end testing before releasing this environment to application users.

Next Steps

How to avoid the pitfalls when installing SQL Server 2014

In-memory OLTP and columnar storage are just two of SQL Server 2014's new features

Some companies are picking SQL Server 2012 over 2014 when it comes time to upgrade

This was last published in October 2015

Dig Deeper on SQL Server Backup and Recovery

Join the conversation

3 comments

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

What method do you plan to use for upgrading to SQL Server 2014 SP1?
Cancel
- Check unmapped users
sp_change_users_login 'Report';
- Change database owner if needed
Cancel
How many people are still using SQL Server 2008, anyway?
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close