question

Blackhawk-17 avatar image
Blackhawk-17 asked

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-r2downgradecompatibility
10 |1200

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

WilliamD avatar image
WilliamD answered
Blackhawk - it doesn't look good. I just tested it here (backup/restore): ![alt text][1] [1]: /upfiles/Capture.PNG
8 comments
10 |1200

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

Tim avatar image Tim commented ·
@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 Likes 0 ·
Blackhawk-17 avatar image Blackhawk-17 commented ·
I have really got to get a lab!
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
@TRAD compatibility is 100 for both 2008r2 and 2008 and there is nothing to change
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
@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 Likes 0 ·
Tim avatar image Tim commented ·
@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 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
@TRAD that was my initial thought too.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Unfortunately, based on everything I've read and my own experiences, going backwards on this is the same as going between other versions. I think @WilliamD has it nailed.
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
@Blackhawk-17 - just as an extra resource - I answered a similar question here not long back: http://ask.sqlservercentral.com/questions/20574/database-revert-back-from-sql-server-2008-to-sql-server-2005 Snapshot Replication can be quite useful if the scripting out is to laborious
0 Likes 0 ·
DaniSQL avatar image
DaniSQL answered
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

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

Tim avatar image
Tim answered
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

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

Cyborg avatar image
Cyborg answered
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 comments
10 |1200

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

WilliamD avatar image WilliamD commented ·
Not quite the only option - Linked Server, bcp, Replication, DTS (if you still want to use it!).
1 Like 1 ·
Cyborg avatar image Cyborg commented ·
Yes william i agree with you
0 Likes 0 ·
ramesh 1 avatar image
ramesh 1 answered
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 comment
10 |1200

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

ramesh 1 avatar image ramesh 1 commented ·
or else create replication the databases between
0 Likes 0 ·
ozamora avatar image
ozamora answered
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

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

Ashish Kumar Mehta avatar image
Ashish Kumar Mehta answered
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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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