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.
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.
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.
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.
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.
Next, execute the DBCC CHECKDB command in Figure 5 to verify the logical and physical integrity of the user database.
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.
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