Nov 15

How to restore a SQL Server 2008 backup into SQL Server 2005 

You Can’t! Yes, you heard me right, you can’t do a straight restore of a backup file from a SQL Server 2008 into a SQL Server 2005. Restoration of back up files can only be done from an old version to a new version. For example , a .bak file from a SQL Server 2000 machine can be restored in 2005 or 2008.

However, don’t lost hope! There’s a work around (just like any other programming challenge).

Do a Generate Script in your 2008 Server. This only works for the full SQL Server 2008 Management Studio Tool. 

1. Open SQL Server 2008 Management Studio Tool

2. Right Click on the database that you want to backup.

3. Look for the Generate Scripts menu after the right click event.

4. A form will pop up, there’s an option on the DATA to be included in the generate script. Choose data along with the structure to be generated.

5. Choose compatibility with SQL 2005 in one of the options.

6. Choose all tables you want to backup restore.

voila

you will have each table having one CREATE TABLE / INSERT RECORDS sql files.


My name is Sanborj.
and this is my life.