September 30, 2010 at 9:20 am
Hi friends,
I'm attaching mdf & ldf files on my sql server 2005 server using SSMS, but I get this following error.. Not sure why
The database '....MDF' cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.
Our client is also using sql server 2005 and they took a backup of the files from SSMS.. Will it work if they send us a logical backup of the database?
Any suggestions please.. thanks
September 30, 2010 at 10:25 am
sounds like their version is in fact SQL2008, so you need a 2008 instance as well
---------------------------------------------------------------------
September 30, 2010 at 10:59 am
Database version 661 is SQL 2008 R2. So the client does not have a 2005 server, they have a SQL 2008 R2 server. The only versions you'll be able to attach that to is SQL 2008 R2. (note, only R2, not straight 2008)
Backups will be the same, there's no downgrade path for SQL backups or files
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
September 30, 2010 at 11:25 am
its R2? oops, apologies.
---------------------------------------------------------------------
September 30, 2010 at 11:33 am
Yup. You can get the version of a database with this:
DBCC TRACEON(3604)
DBCC DBINFO
DBCC TRACEOFF(3604)
The field of interest is dbi_version. Run that against master on a 2008 instance and the result is 655.
p.s. That means that the OP's database version is 2008, not 2005 and their client has 2008 R2, not 2005. 2005 is version 611 (or 612) and that version is not mentioned in the error that the OP posted.
p.p.s The dbi_version will ALWAYS match the instance that the DB is attached to. 611/612 for SQL 2005, 655 for SQL 2008, 661 for SQL 2008 R2
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
September 30, 2010 at 1:03 pm
Thanks for the info Gail (or dbinfo) 🙂
there is also the version column in master.sys.sysdatabases. Interestingly though MSDN (from 2008 onwards) says 'Not supported. Future compatibility is not guaranteed.'
wonder why.
---------------------------------------------------------------------
September 30, 2010 at 1:14 pm
Because the sysdatabases view is deprecated, included only for backward compatibility with SQL 2000 and will be removed in a future version of the product.
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
September 30, 2010 at 1:31 pm
GilaMonster (9/30/2010)
Yup. You can get the version of a database with this:
DBCC TRACEON(3604)DBCC DBINFO
DBCC TRACEOFF(3604)
The field of interest is dbi_version. Run that against master on a 2008 instance and the result is 655.
p.s. That means that the OP's database version is 2008, not 2005 and their client has 2008 R2, not 2005. 2005 is version 611 (or 612) and that version is not mentioned in the error that the OP posted.
p.p.s The dbi_version will ALWAYS match the instance that the DB is attached to. 611/612 for SQL 2005, 655 for SQL 2008, 661 for SQL 2008 R2
Thanks for the question and answer by GilaMonster.
If I have taken backup in SQL 2008 R2 - is it possible to restore the database to Sql 2008/2005 anyhow?
If database compatibility is 90 and server is 2008 R2 then once I take backup, is it possible to restore the db to sql 2008/2005?
Thanks
September 30, 2010 at 1:57 pm
GilaMonster (9/30/2010)
Because the sysdatabases view is deprecated, included only for backward compatibility with SQL 2000 and will be removed in a future version of the product.
doh!
:blush:
---------------------------------------------------------------------
September 30, 2010 at 2:06 pm
Hardy21 (9/30/2010)
If I have taken backup in SQL 2008 R2 - is it possible to restore the database to Sql 2008/2005 anyhow?
No.
If database compatibility is 90 and server is 2008 R2 then once I take backup, is it possible to restore the db to sql 2008/2005?
No.
Compatibility just affects how some T-SQL constructs are interpreted. It does not change the database version. Newer versions have different file structures, different system tables and that cannot be changed.
There is no backward compat for SQL data files or backups. There is no way to take a backup or file from 2008 R2 and restore or attach it to a lower version.
You will either need to install 2008 R2 and then attach the database to that, or get a script of all the objects and exported copies of all the data and then recreate the database on a lower version
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
September 30, 2010 at 11:54 pm
Thanks Gail Shaw for clarification, its clear my doubts.
Thanks
October 1, 2010 at 7:32 am
You will either need to install 2008 R2 and then attach the database to that, or get a script of all the objects and exported copies of all the data and then recreate the database on a lower version
Oh! I can probably recreate the objects in 2005 but to import all the data do I need to use export/import wizard? Or is there anything else that I can use for faster import? Or do I need to use DTS?
Thanks so much
October 1, 2010 at 9:11 am
DTS (deprecated and shouldn't be used any more), SSIS, BCP, import/export wizard. All about the same.
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
October 1, 2010 at 11:45 am
Thanks.
We now generated a script with 'data' from SQL server 2008. I'm trying to run this script against our 2005 database, but I'm not able to copy & paste the script on the query window, the sql script size is 1GB.. I tried to do file-open, but I get this message:
The operation could not be completed. Not enough storage is available to complete this operation.
Any help please? Thanks
October 1, 2010 at 12:28 pm
Generate script with data is NOT one of the ways I'd recommend for moving the data. SSIS, mcp or import/export wizard are far more effective.
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 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply