0

My team needs to update an automated content process running over SQL Server 2008 servers to include the content of a SQL Server 2008 R2 database delivered by a third-party vendor. The request to third-party to deliver us on SQL Server 2008 is NOT an option.

My common sense indicates there is no way to downgrade from 2k8 R2 to 2k8 as in most of the software products I know. However, I'm wondering if I would be able to do something like this in a script:

  • Restore SQL Server 2008 R2 .bak file onto our SQL Server 2008 R2 instance
  • Update compatiblity level to SQL Server 2008 (100). Here is the bug in my plan: I've not found any compatiblity level distinctions between 2008 versions in internet. Any clue on this?
  • Backup the database.
  • Restore the database in SQL Server 2008

There may be some other ideas you may want to share with me.

Just recall there should not be manual intervention on this database conversion since this operation needs to be incorporated to an automated process.

Any help/suggestions/comments will be welcome!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
G21
  • 1,297
  • 2
  • 18
  • 39

3 Answers3

2

No, you cannot restore a database backwards, regardless of compatibility level. Compatibility level affects certain database engine behaviors, but does not downgrade the database version. You will never be able to restore onto 2008 a database that ever existed in a 2008 R2 instance.

For workarounds, you can:

  • restore the database on R2, then use the import/export data wizard (or a third party tool) to transfer schema / data to the 2008 instance (and you should be able to automate this to some extent, e.g. Red-Gate's tools have command-line interfaces)
  • upgrade your 2008 servers to 2008 R2 or 2012
  • push back on the vendor
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
1

By design, you can only restore a DB backup to the same or newer version of SQL Server. A backup created with SQL Server 2008 R2 can only be restored with SQL Server 2008 R2 or SQL Server 2012. This is regardless of the compatibility level of the database. It's a limitation of the backup and restore procedures. This has been a limitation since prior to SQL Server 2000 (i.e., every version I've ever used).

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
0

Maybe you can script out the creation of new database, instead of backing up and restoring you basically just do a data dump with script.

Try looking at this How can I get a SQL dump of a SQL Server 2008 database?

Community
  • 1
  • 1
Jeremy A. West
  • 2,162
  • 4
  • 27
  • 40