Is it possible to restore Sql Server 2008 backup in sql server 2005

Question

Is it possible to restore a backup of a SQL Server 2008 database onto an instance of SQL Server 2005?

I need to work on an sample application for which database backup is in sql server 2008. But I'll not be able to install 2008. So is it possible to restore that back up in 2005?

Answer 1

One important thing is missing in all answers and that is the fact that Generate Scripts in SSMS doesn’t order the scripts correctly.

Scripts have to be ordered in the correct dependency order so that child tables are created after parent tables and such.

This is not an issue for small databases where its easy to reorder the scripts manually but it can be a huge issue when dealing with databases that have 100+ objects.

My experience is that its most convenient to use third party tools that can read backup and generate scripts in the correct order. I’m using ApexSQL Diff and Data Diff from ApexSQL but you can’t go wrong with any popular vendor.

Answer 2

Having had no luck with the Import/Export stuff (flat file exports failed on import claiming charset mapping issues [even though same charset used throughout] and/or truncation issues [even though source and destination had exact same structure]), and having had no luck with using the "generate scripts" option suggested by Garry Shutler (it generated a script with syntax errors), I was finally able to copy the big table I wanted to copy from 2008 to 2005 using the SQL Server bcp utility. So that's another option for this situation, although for an entire database it would be table-by-table and probably doesn't help with views and such.

The steps I used:

1. On the source server, use "Script Table As...CREATE" to get the structure, run that on the target server.

2. On the target server, create a bcp format file using your newly-created table:

   bcp database.owner.table format nul -f table.fmt -n

   (If you're not using Windows auth, you may need the -U and -P options to specify username and password.)

3. Copy that format file to the source server (if necessary).

4. Export the data to file on the source server:

   bcp database.owner.table out table.dat -f table.fmt

   (Again, possibly with -U and -P.)

5. Copy the data file to the target server (if necessary).

6. Import the data on the target server:

   bcp database.owner.table in table.dat -f table.fmt

   (Again, possibly with -U and -P.)

In hopes that proves useful to someone else.

Answer 3

No. It is not possible to restore a database from a backup of a newer version.

If you are dead set on it, I think your best option is to selet the database in the Object Explorer in SQL 2008, right-click, select Tasks->Generate Scripts.In the options dialog emable about everything, including Script Data.

And make sure you select "Script for SQL 2005".

Source

When importing the objects into your target server, if the objects are large you may find that you can't open the SQL file via Management Studio (with a completely useless "The operation could not be completed" error, no less). That's okay, just load the file via sqlcmd.

Answer 4

I have had this problem for a long while.

You cannot restore SQL2008 backups onto an SQL2005 instance.

And for me, workarounds like import/export wizzard or to script the database from SQL2008 using the generate scripts with the for SQL2005 option won't work. Because simply, my databases cross-reference each other inside their views and stored procedures or udfs. They do not befall to my responsibility completely and so I cannot consolidate them into 1 database. They are a set of 6 dbs that refer to each other directly inside their views and stored procedures. When I transfer them from one SQL2005 instance onto another, I usually do full-backup/restore.

If I were to script them, even with the with dependencies option I would get errors at re-creation time as db1 will not find views inside db3 because it so happened that I executed the create db1 script first. If I tried db3 first I get similar exceptions.

The only way to script them so that I won't have such dependency exceptions, is to figure out a sequence that works and script them partially in that manner: say: db1_tables followed by db2_tables followed by db2_views followed by db1_views, sp, udfs etc.

I have such a sequence. And when I need to create a new set of such 6 dbs, I executed the smaller partial scripts in sequence.

This explains why the generate scripts, with dependencies and with data and set to SQL2005 version scripts, will just not work for me.

The import/export wizzard is better in my case because it will copy tables and then you still have to script all views, sp, udfs etc.

What I really need is a conversion tool for SQL2008 backup files, to convert them to SQL2005 format. Then my problem will go away. Or some kind of a tool that would allow restore from SQL2008 full-backup files, without asking me too many questions.

If anyone knows such tools and have used them, let me know.

Answer 5

Yes it is possible

Using the export in the SQL Server 2008. Go to All Programs --> Microsoft SQL Server 2008 --> Import and Export Data

• Then SQL Server Import AND Export Wizard window will be opened. Press Next

• Choose a Data source (in your case from SQL Server 2008). Choose a Destination( in your case from SQL Server 2005).

• Select Copy data from one or more tables or view

• Select the source's tables and destination's tables

• Click Next & Finish

to complete.

Answer 6

No you can't, but tools like red gate's SQL Compare/Data Compare can read backup files directly & transfer the info across to a live database, dealing with any syntax or settings that aren't compatible on SS2005

Answer 7

You can use DBSave, it's a great freeware tool to backup and restore ms sql server on different machines. It's verry simple to setup and to use.

Answer 8

No, not directly. SQL Server 2008 database backups are not backward compatible with SQL Server 2005. However, with SQL Server 2008 Management Studio, you can script data and schemas in SQL Server 2005 mode. This article describes the process in detail.