Problem
Like any organization, we are also planning the migration of a database from SQL Server 2005 to SQL Server 2008. As there is only one database which needs to be migrated as of now, I think it would be ideal to use the database backup and restore method to upgrade the user database from SQL Server 2005 to SQL Server 2008. In this tip we will go through a detailed explanation of how to use the database backup and restore method to upgrade a database.
Solution
The database backup and restore operation is an excellent method which is available to database administrators who need to upgrade a database from SQL Server 2000 or SQL Server 2005 to SQL Server 2008.
Moving a database using backup and restore method involves the following steps:
1. Perform a full and a transactional log backup of the user database
2. Copy the database backup files to the new server
3. Restore the full backup followed by the transactional log backup of the user database
4. Change the Database Compatibility Level
5. Verify Logical and Physical Integrity of the Database
Note: if the database is in SIMPLE recovery mode then you only need to perform a full backup and a full restore.
In this example we will be moving the AdventureWorks datbase from SQL 2005 to SQL 2008.
Perform a Full and a Transactional Log Backup of the User Database Using T-SQL Commands
1. Connect to SQL Server 2005 Instance using SQL Server Management Studio.
2. Execute the below mentioned T-SQL code to backup the AdventureWorks database.
TO DISK = ‘D:\Backups\AdventureWorks.BAK’
GO
BACKUP LOG AdventureWorks
TO DISK = ‘D:\Backups\AdventureWorks.TRN’
GO
Restoring the User Database from the Database Backups
1. Copy the backup files that were created above and place them on the server with SQL 2008.
2. Connect to SQL Server 2008 instance using SQL Server Management Studio.
3. Execute the below mentioned T-SQL code to restore the AdventureWorks database on a SQL Server 2008 Instance. Note: make any adjustments that may be needed to the below code.
FROM DISK =‘D:\Backups\AdventureWorks.BAK’
RESTORE DATABASE AdventureWorks
FROM DISK =‘D:\Backups\AdventureWorks.BAK’
WITH
MOVE ‘AdventureWorks_Data’ TO ‘D:\MSSQL\DATA\AdventureWorks_Data.MDF’,
MOVE ‘AdventureWorks_Log’ TO ‘D:\MSSQL\DATA\AdventureWorks_Log.LDF’,
NORECOVERY
RESTORE LOG AdventureWorks
FROM DISK =‘D:\Backups\AdventureWorks.TRN’
WITH RECOVERY
Change Compatibility Level
Once the database is upgraded to SQL Server 2008, execute the below T-SQL code to change the database compatibility to 100 (SQL 2008). You may need to check that your application works without issue after changing the compatibility level.
GO ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 100
GO
Verify Logical and Physical Integrity of Database
DBAs can check the logical and physical integrity of all the objects within the database by executing a DBCC CHECKDB.
GO
Next Steps
- If you are unsure if there will be issues by changing your compatibly level take a look at this information to see what the impact may be.
- Read these related tips:
Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.
- MSSQLTips Awards: Achiever (75+ tips) – 2013
