Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Upgrading to SQL Server 2008 Using Backup and Restore Method


By:   |   Read Comments (4)   |   Related Tips: More > Upgrades and Migrations


SQL Server Conference Giveaway - click to learn more


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.

BACKUP DATABASE AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks.BAK'
GO

BACKUP
LOG AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks.TRN'
GO

Perform a Full and a Transactional Log Backup of the User Database Using T-SQL Commands


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.

RESTORE FILELISTONLY
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

Restoring the User Database from the Database Backups


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 [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.

DBCC CHECKDB ('AdventureWorks')
GO
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, December 20, 2012 - 4:30:55 AM - SIVA Back To Top

i crated Backup  jan 2012 with full recovery mode

i not done any  back up 2012 dec.now the client asking the data mar-29 what data is there. how i can recover the data upto march 26

 

 

note log not deleted for till date

 

 

 

 


Wednesday, March 14, 2012 - 8:03:56 AM - frozenfishes Back To Top

You should also run DBCC UPDATEUSAGE(dbname) after changing compatibility Level.


Tuesday, December 14, 2010 - 12:02:15 PM - Gennadiy Chornenkyy Back To Top

Agreed - logins transfer is missed.

Secondly SQL 2000 securirty model is different from the SQL2005/SQL2008 - you must be careful with that


Friday, November 19, 2010 - 10:14:40 AM - Carl Back To Top

Good article, but it forgot to mention to scriopt out user logins and move them as well to new server. That is unless you WANT all of your users to have to change their passwords on ititial log in.


Learn more about SQL Server tools