Upgrading to SQL Server 2008 Using Backup and Restore Method

Published on September 14 2016 by LakshmiSaahul,Dhana Royal

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 SBIDB 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 sbidb database.

BACKUP DATABASBIDB
TO DISK = 'D:\Backups\SBIDB.BAK'
GO

BACKUP LOG SBIDB
TO DISK = 'D:\Backups\SBIDB.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 SBIDB database on a SQL Server 2008 Instance. Note: make any adjustments that may be needed to the below code.

RESTORE FILELISTONLY
FROM DISK ='D:\SBIDB.BAK'

RESTORE DATABASE NEWSBIDB
FROM DISK ='D:\Backups\SBIDB.BAK'
WITH
MOVE 'SBIDB_Data' TO 'D:\MSSQL\DATA\NEWSBIDB_Data.MDF',
MOVE 'SBIDB_Log' TO 'D:\MSSQL\DATA\NEWSBIDB_Log.LDF',
NORECOVERY

RESTORE LOG NEW SBIDB
FROM DISK ='D:\Backups\SBIDB.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.

USE [master]
GO ALTER DATABASE [NEW SBIDB] 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.

DBCC CHECKDB ('NEW SBIDB')
GO

Advertising