August 25, 2009 at 2:13 pm
Looks like a familiar one.One of the reasons can be that this backup was taken from a higher version and it can be restored to a version equal or greater than this.Did you get any clue about the original backup version?
August 13, 2010 at 9:33 am
I am thinking it is a SQL 2005 SSMS vs SQL 2008 SSMS issue because I am getting that error now. The DB is 90 but because it is SSMS 2008 it seems to screw up the backup file when restoring on SSMS 2005. Still looking for a solution beside SSMS 2005
August 13, 2010 at 9:40 am
Please post new questions in a new thread. Thank you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2010 at 9:42 am
Not 100% sure about 2008 to 2005 but past experience says you cannot restore to a lesser version of SQL (if I understand your statement), regardless of the DB settings (8.0, 9.0, etc). You can export the table structure and the data contents but not backup on 2008 and restore to 2005.
-- You can't be late until you show up.
August 13, 2010 at 9:48 am
From what I see you are correct and there is no way around it. So I installed SSMS express 2005 and did the backup. Then it works
August 13, 2010 at 9:52 am
It's got nothing to do with the version of SSMS. It's got to do with the version of SQL Server engine.
I regularly take backups of my 2005 databases using SSMS 2008, and restore to the 2005 instance. That works fine. What does not work is to backup a database on SQL 2008 and try to restore it on 2005.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2010 at 11:13 am
Can you explain how you are doing that because I can not get it to work and it is frustrating. My database is 2005 compatible but was created on a 2008 engine. I just go to tasks back up and then I can not restore on a developers machine. Since they have SSMS 2005.
August 13, 2010 at 11:19 am
GilaMonster (8/13/2010)
It's got nothing to do with the version of SSMS. It's got to do with the version of SQL Server engine.
JKSQL (8/13/2010)
Can you explain how you are doing that because I can not get it to work and it is frustrating. My database is 2005 compatible but was created on a 2008 engine. I just go to tasks back up and then I can not restore on a developers machine. Since they have SSMS 2005.
What do you not understand? The version of the database engine is the key, NOT the compatability level of the database.
-- You can't be late until you show up.
August 13, 2010 at 11:28 am
The compatibility is set to SQL Server 2005 (90). That is why I thought the .bak would be formatted correctly using the backup utility from SQL 2008 R2. When I take the .bak over to the developers box I can not restore using SSMS 2005. I get the message that this post is originally about.
August 13, 2010 at 11:31 am
JKSQL (8/13/2010)
The compatibility is set to SQL Server 2005 (90). That is why I thought the .bak would be formatted correctly using the backup utility from SQL 2008 R2.
The only thing compatibility level does is affect how certain T-SQL constructs are interpreted. Nothing else. It does not affect or change the structure of the database, the structure of the backup or the internal database version.
A database attached to SQL 2008 R2 is a SQL 2008 R2 database. The structure of the database is 2008 R2. SQL 2005 does not understand that structure and cannot restore or attach such a database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2010 at 1:14 pm
ok I understand what you are saying. Is there anyway to create a DB on 2008 and still be able to restore it to a 2005 engine. Meaning is there a setting or script I can run or do I need to be running both engines in order to be able to restore into a 2005 environment? Also thanks for all the help.
August 14, 2010 at 3:25 am
JKSQL (8/13/2010)
Is there anyway to create a DB on 2008 and still be able to restore it to a 2005 engine.
No. None whatsoever. There's no downgrade ability for SQL databases.
Export data, script objects, recreate on SQL 2005.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply