21

Got myself in a bit of a pickle here ... working on a CMS project, under the assumption that sql server 2008 was greenlighted as the db of choice. Well it wasn't, we now have to backport all of our content out SQL Server 2008 and into SQL Server 2005.

A simple backup/restore procedure yields: "RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)".

Unfortunately, exporting the data to an excel spreadsheet yields multiple OLE errors which I believe is actually a problem in the db of the cms.

Does anyone out there have other approaches they would like to recommend for this task? Thanks in advance

Improve this question
1
  • Grab the sql compare/data compare tools as suggested by ocdecio. Works a treat for this kinda thing. stackoverflow.com/questions/413699/… . Commented Jan 5, 2009 at 17:58

9 Answers 9

Reset to default
15

Use RedGate:

tool for comparing and deploying SQL Server database contents.

You can work with live databases, backups, or SQL scripts in source control. Damaged or missing data can be restored to a single row, without the need for a full database recovery.

SQL Data Compare helps you compare and deploy changes quickly, simply, and with zero errors...

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

SQL Compare & SQL Data Compare rock. Their free 14 day trial (which they'll extend if you ask nicely) got me out of a hole like this. Defo on the shopping list.
Please if this saves you a day buy the software don't use the trial for commercial gain. In my last role we purchased the software and it paid for itself many times over.
In my experience RedGate tools have been a life-saver. Although if you only need it for a one-off job why would you purchase it? If you need it again (which I think you will do), then buy it!
9

There is no way to do this by default. You can generate scripts for 2008 database on 2008 server and then execute these scripts on 2005 version. Note that you’ll have to manually review scripts and remove all parts that are unique to 2008 version.

Another way is to use third party tools such as Red Gate or ApexSQL Diff (move schema) and ApexSQL Data Diff (move data).

Improve this answer

Comments

6

Use the Generate SCripts to create the database and schema and ensure you target SQL Server 2005 and script data.

Improve this answer

1 Comment

To generate the script, see this SO answer.
5

Rather than do a backup and restore you might try using SQL 2005's Import/Export Data wizard.

http://support.microsoft.com/default.aspx?scid=kb;en-us;314546

http://msdn.microsoft.com/en-us/library/ms140052(SQL.90).aspx

Improve this answer

Comments

4

I've just hit the same problem and here is how I worked around it.

The problem was to copy a database from an operational SQL Server 2008 database to a new SQL Server 2005 database.

I scripted the database using Management Studio on the 2008 server. I only scripted the database design, not the data. I should add also that the DB only has tables and indexes, so I haven't tried this with any cleverer objects although I can't think why they wouldn't work.

On the 2005 server I created a new database by hand and then ran the script to set up all the tables and indexes.

The in Management Studio back on the 2008 server I used the Export Data wizard to export the data from the 2008 server to the 2005 server. It's currently running and seems quite happy moving the data.

Once all the data is across I'll have a couple of small things left to do - create the users and set-up the security in the 2005 DB, but all-in-all it doesn't look like a bad way of doing it. It's not quite point-and-click but it's not too strenuous.

So it seems that the Copy Database wizard won't work (I think because the package ultimately runs on the 2005 server and 2005 Management Studio can't talk to 2008) but the Export Data wizard is quite happy moving data ... as long as the DB already exists on the target server.

Hope that's useful.

Improve this answer

Comments

1

It's not possible to restore to previous versions in SQL Server

Is there no SQL 2005 backup around? Otherwise you really are limited to export the entire database in 2008, and re-import back into 2005, or the Import/Export wizard in 2008

Or rely on 3rd party tools. e.g. Red Gate Data Compare is able to sync. the DATA between 2 servers/databases

Improve this answer

Comments

0

I only use mysql but can you export your data into sql statments, and then import then into sql2005? Just a thought..

Improve this answer

1 Comment

Ah I did try that. The sql then ran for 12+ hours last night. The DB isn't that large so i think something is wrong.
0

I faced a similar problem (sql 2005 to sql 2000), and found that I happened to have a blank database at the older version. I used bcp.exe to copy all the data.

Improve this answer

Comments

0

check this link click here You can create the database script in sql server 2008 and you can use it sql server 2005 and lower version....

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.