Restoring .bak file SQL Server 2005 in SQL Server 2008

Question

I don't have enough knowledge in SQL Server back and restore. I just want to know is it possible to restore an SQL Server 2005 .bak file in SQL Server 2008? If yes, how can I do it?

Answer 1

All restore operations in SQL Server are backward compatible. You can restore a 2005 backup on a 2008 instance just like you would restore a 2008 backup on that same instance.

There are some limitations to older SQL Server versions, but a backup that was taken on a 2005 or newer instance can currently be restored on any supported SQL Server version.

However, the backups themselves are not backwards compatible. So on a 2005 instance you cannot restore a backup that was taken on a 2008 instance.

Answer 2

Yes, it is quite possible to restore a SQL Server 2005 backup to SQL Server 2008.

The restore command is the same as of restoring to SQL Server 2005. E.g.

RESTORE DATABASE [Xyzzy] 
  FROM  DISK = N'\\Server\Share\Path\Xyzzy_FULL_20130915.bak' WITH  FILE = 1,  
  MOVE N'Xyzzy_Data' TO N'D:\Data\Xyzzy_Data.MDF',  
  MOVE N'Xyzzy_Log' TO N'F:\Data\Xyzzy_log.LDF',  RECOVERY,  NOUNLOAD,  STATS = 10

The restore will be followed by some upgrade steps to bring the SQL Server 2005 database format up to the SQL Server 2008 database format.

(Note: You will not be able to restore the 2008 database back to 2005.)

There are limits to how early a version of SQL Server can be restored to 2008, but 2005 should work fine. Here is a link to the supported upgrade paths to SQL Server 2008:

http://technet.microsoft.com/en-us/library/ms143393(v=sql.100).aspx

Answer 3

Yes you can do it As you want to restore the database of SQL server 2005 to SQL server 2008 I will say it as a "Migration" of Database. How you can do it?

You Can follow this 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.

*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 Adventure Works database.

BACKUP DATABASE AdventureWorks 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 Adventure Works 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 .

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

DBCC CHECKDB ('AdventureWorks') GO

Note :--When you restore a SQL Server 2005 backup file in SQL Server 2008, the operation takes much longer than when you restore the same backup file in SQL Server 2005. Hot fix is available at

http://support.microsoft.com/kb/967178.

Thank you,
Sam