Denali - storage format and compression backwards compatibility

Question

Denali - storage format and compression backwards compatibility

cashmanifesto

Say Hey Kid

Points: 682
More actions
November 18, 2010 at 9:59 am

#226782

Sorry for posting a Denali topic in the 2008 forum, I didn't see where else to put it. If a mod wants to move this, it will not hurt my feelings one bit!
I was trying to restore a SQL 2008 R2 database that contains all of my tools to my new Denali instance and ran into this message. I disabled autogrowth on all files for this database and tried again, same message. I'm not familiar with vardecimal storage. Is it just a datatype, or set of datatypes?
How would one go about "scrubbing" their pre-Denali databases so that they can be successfully upgraded if these types of facets have been deprecated, marked for deletion, or completely removed?
Here's the message:
"Database [wemu] cannot be started in this edition of SQL Server because part or all of object 'DatabaseGrowth' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enter"

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

cashmanifesto Say Hey Kid Points: 682 More actions · Answer 1

I just wanted to add that I was already successful getting the database over to Denali by using the good old "Generate Scripts" method, as I've done before to take something to an earlier version of SQL (ei. SQL2008 -> SQL2005)

I would just like to be educated a little as to what needs to be done prior to the backup to allow a successful restore and eventual upgrade from version 100 to 110.

I am assuming that is still the recommended process for upgrading a database in Denali, but that could be incorrect as well. If so, I would love to know!

Thanks in advance guys.

SQLServer-Training.com SSC Enthusiast Points: 160 More actions · Answer 2

I just tested Contained Databases, anew feature of DENALI in my test virtual Setup.

Here is Video and Screenshots, which I captured during configuration.

http://sqlserver-training.com/video-sql-server-contained-database/-

Suggest me some good test scenarios to test more deeper this solution now.

Thanks
DBATAG
Website : http://sqlserver-training.com
Email : dbatag@sqlServer-Training.com

cashmanifesto Say Hey Kid Points: 682 More actions · Answer 3

SQLServer-Training.com (12/5/2010)
I just tested Contained Databases, anew feature of DENALI in my test virtual Setup.
Here is Video and Screenshots, which I captured during configuration.
<link removed by request>
Suggest me some good test scenarios to test more deeper this solution now.

Thanks for that DBATAG. While it isn't really the solution I was looking for, it will be an invaluable feature for me moving forward. Copying SQL logins and SQL Agent Jobs has been a regular PITA for me for years. I can't wait until this hits primetime!

I really wanted to restore the database natively so that I could build on it with Denali, and not bring any of the dependecies. I don't need logins, agent jobs or other sysobjects. I just want tables, procedures, functions and triggers really.

I already scripted them all over by now and it works great, but I would prefer to understand what the DatabaseGrowth dependecy is, so that I can hopefully continue to do the old backup/restore upgrade that's been available since I entered the SQL Server world.

Regards,

Chris C.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Vardecimal was introduced in 2005 SP 1(iirc) and deprecated in SQl 2008 (replaced by compression). I cannot remember the exact syntax, but it should be an ALTER DATABASE to enable or disable it. Check SQL 2005 books online

p.s. I've reported that contained DB post as spam, would you mind editing you post and removing the link?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cashmanifesto Say Hey Kid Points: 682 More actions · Answer 5

GilaMonster (12/6/2010)
Vardecimal was introduced in 2005 SP 1(iirc) and deprecated in SQl 2008 (replaced by compression). I cannot remember the exact syntax, but it should be an ALTER DATABASE to enable or disable it. Check SQL 2005 books online
p.s. I've reported that contained DB post as spam, would you mind editing you post and removing the link?

Thank you much. That's exactly what I was looking for. I believe that will get me moving in the direction I wanted.

I have removed the link from my quote. Hopefully the OP of the link will remove it as well.

Regards,

Chris C.