Downgrade SQL Server 2008 R2 to SQL Server 2008

Question

question

Blackhawk-17 asked Oct 19, '10

Downgrade SQL Server 2008 R2 to SQL Server 2008

Due to support issues with a 3rd-party vendor our installation of SQL Server must be changed. In an nutshell we'll be uninstalling R2 and installing R1 clean. Will we be able to get away with a backup/restore or detach/attach solution with regards to the databases? Or will an entire ETL process be required? Has anyone attempted this? I know 2008 -> 2005 won't work, but if both are 2008...

sql-server-2008-r2 downgrade compatibility

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-10-19T05:46:28Z

WilliamD answered Oct 19, '10

Blackhawk - it doesn't look good. I just tested it here (backup/restore): ![alt text][1] [1]: /upfiles/Capture.PNG

8

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim commented · Oct 19, 2010 at 05:49 AM

@William, since you have the tools there in front of you, can you try changing the compatibility of an R2 db, back it up, then try the restore to R1.

0 ·

Blackhawk-17 commented · Oct 19, 2010 at 05:49 AM

I have really got to get a lab!

0 ·

DaniSQL commented · Oct 19, 2010 at 05:52 AM

@TRAD compatibility is 100 for both 2008r2 and 2008 and there is nothing to change

0 ·

WilliamD commented · Oct 19, 2010 at 05:54 AM

@Blackhawk - there is only the compat level SQL 2008 (100). It seems that a db probably could go from R2 to R1 (the engine changes appear to be minor enough to me, especially for a vanilla db). I think the problem lies in the header for the DB itself. IIRC that is set when the DB is created/upgraded and refuses to go backwards. I didn't bother setting it to 2005/2000 because compat level has little/no bearing on the real internals of the DB, just the feature-set that will run against it. Looks like you could go for either SSIS, Replication, Linked Server or Source Control for moving your DB back a version.

0 ·

Tim commented · Oct 19, 2010 at 05:55 AM

@DaniSQL, I haven't had the pleasure of working with R2 yet. I would have thought MS would have given it a different compatibility version since it is a different version of SQL. I really need to get my hands on a copy.

0 ·

Show more comments

Answer 2 · 2010-10-19T05:45:56Z

DaniSQL answered Oct 19, '10

Idk with attach/detach but I have tried to restore a 2008R2 backup in to 2008 server and it didnt work for me

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-10-19T05:47:35Z

Tim answered Oct 19, '10

I have really only dealt with editions of the same version, going from Ent to Standard in which I have had no problems. Since R2 is a different version I suspect issues. You would of course want to try this on a test server before doing it in production, but try changing the compatibility of the user databases from R2, then detach and attach them to an R1 version. Run through you tests. If all is well move forward.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2010-10-19T05:53:59Z

Cyborg answered Oct 19, '10

you cant restore a backups from higher version of SQL Server, The same applies for attach method also. as William suggest you can use various method to transfer data, By using Redgate tool called SQL Object Level Recovery Native you can restore individual DB objects except Users and Logins from SQL Server 2008 R2 backup to SQL Server 2008 (i am not sure for 2005 and below).

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD commented · Oct 19, 2010 at 05:59 AM

Not quite the only option - Linked Server, bcp, Replication, DTS (if you still want to use it!).

1 ·

Cyborg commented · Oct 19, 2010 at 06:03 AM

Yes william i agree with you

0 ·

Answer 5 · 2010-10-19T05:59:00Z

ramesh 1 answered Oct 19, '10

i can give you a tip, if your database is of less than 256mb, then you can generate the script , then execute the script.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ramesh 1 commented · Oct 19, 2010 at 06:02 AM

or else create replication the databases between

0 ·

Answer 6 · 2010-10-19T06:28:35Z

ozamora answered Oct 19, '10

With @Cyborg on this one. There is no way for you to attach, restore, or mirror to a lower edition. If you find a way, you will be way unsupported by Microsoft. Like @ramesh and @Cyborg said, your best bet is to export the data with a 3rd party tool or generating scripts. That is a painful process though. You can always go up, except if you enabled [15,000 partitions support on 2008 SP2][1]. [1]: http://ozamora.com/2010/10/sql-server-can-i-upgrade-to-2008-r2-from-2008-2005-or-2000-tsql2sday/

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 7 · 2011-11-07T21:47:13Z

Ashish Kumar Mehta answered Nov 7, '11

Read the following article [How to Downgrade SQL Server Database from a higher version to a lower version][1] which has an example to downgrade a SQL Server database from SQL Server 2008 R2 to SQL Server 2008 Using SQL Server Integration Services. [1]: http://www.myTechMantra.com/LearnSQLServer/Downgrade_SQL_Server_Database_P1.html

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question