March 24, 2009 at 8:14 am
First, to answer all the 'why did you do it this way to begin with' questions - I didn't - I inherited it.
Second, I am fairly new to SQL 2005. I currently have one SQL 2005 server running several DBs in Comp. level 80. Does this mean that the DBs could ever be recovered on a SQL 2000 server? Or does this simply mean that they are compatible at an application/SQL query level?
I will hold my next question until I know that answer to this one.
March 24, 2009 at 8:46 am
david.griswold (3/24/2009)
Second, I am fairly new to SQL 2005. I currently have one SQL 2005 server running several DBs in Comp. level 80. Does this mean that the DBs could ever be recovered on a SQL 2000 server?
No.
Or does this simply mean that they are compatible at an application/SQL query level?
Yes
Compatibility mode just affects what T-SQL keywords are valid and how they're interpreted. As soon as a DB is attached/restored to a SQL 2005 instance, the file structure and system tables are converted to SQL 2005 format. SQL 2000 doesn't understand them.
Downgrading a database is never possible
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
March 25, 2009 at 8:25 pm
I find this interesting. I was installing an application today that specified SQL 2000, and instead of SQL scripts to build the databases, it shipped with prebuild DBs in the form of MDF and LDF, with instructions to simply attach them in Enterprise manager. So, I copied them to my SQL 2005 server put the MDFs on the data drive, the LDFs on the Logs drive, fired up SQL Server Management Studio (who else hates this interface?) and was able to attach the DBs with no issues.
So, I guess what I am saying here is, if I can simple attached SQL 2000 DBs, why is their no way to 'export' an existing SQL 2005 DB running in 'SQL 2000' mode in a format that SQL 2000 could then attach? Is this function just not built in?
David
March 25, 2009 at 8:56 pm
When you attach the database to SQL Server 2005, the database engine upgrades the files. If it did not perform this upgrade - the engine would not be able to work with the data in the files.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 25, 2009 at 10:57 pm
david.griswold (3/25/2009)
So, I copied them to my SQL 2005 server put the MDFs on the data drive, the LDFs on the Logs drive, fired up SQL Server Management Studio (who else hates this interface?) and was able to attach the DBs with no issues.
Yup, and if you checked in the SQL error log aferwards, you'd have seen several entried describing an update to the database version.
When a 2000 data file is attached/restored on SQL 2005, an upgrade 'script' runs, changing the system tables to the 2005 format, making several modifications to the way the file is laid out and incrementing the database's version number.
So, I guess what I am saying here is, if I can simple attached SQL 2000 DBs, why is their no way to 'export' an existing SQL 2005 DB running in 'SQL 2000' mode in a format that SQL 2000 could then attach? Is this function just not built in?
Because, while SQL 2005 understands the SQL 2000 file format, SQL 2000 doesn't understand the file format and system tables used in SQL 2005/2008. Short of a very large patch from MS, earlier versions can't be expected to understand later versions structures.
As for export, there is a way. SSIS's copy database objects does a very nice jb of copying a database's contents to lower versions.
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
March 26, 2009 at 9:18 am
You'd think there was a way, but there isn't. Gail's suggestion of SSIS is the best way to move to a lower version.
March 26, 2009 at 9:44 am
I will give up on this then and tell my boss we need another SQL 2005 server if we are going to have any type of failover server, cold, warm, hot or otherwise.
Thanks,
David
March 26, 2009 at 1:30 pm
david.griswold (3/26/2009)
I will give up on this then and tell my boss we need another SQL 2005 server if we are going to have any type of failover server, cold, warm, hot or otherwise.
Or another 2000 server. There's no practical way to have 2005 as a failover server for 2000 or vis versa.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply