Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
    
Thanks for the replay. I took .bak file from another server, now I want to restore my system. Is it possible ? –  Shafeeq Sep 17 '13 at 13:52
    
While restoring the .bak from GUI, its showing error like Restore failed for SERVER 'my server name' . What can I do ? –  Shafeeq Sep 17 '13 at 13:53
    
What error messages are you getting? –  RLF Sep 17 '13 at 14:11
    
sorry, I forgot inform you. Somehow its worked. Thank you. –  Shafeeq Sep 17 '13 at 16:37
add comment

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
share|improve this answer
    
...and while you are at it UPDATE STATISTICS with FULL SCAN and DBCC UPDATEUSAGE after upgrade to a newer version of SQL Server. –  DaniSQL Sep 18 '13 at 15:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.