通过

Vardecimal Storage Format and its implications on Backup/Recovery

  • 项目

Has any one tried restoring or attaching a SQL Server 2005 database on SQL Server 2000? You will find that SQL Server 2000 will fail this restore or attach. The reason is simple. SQL Server 2000 does not understand the physical structure changes in SQL Server 2005 database. SQL Server detects this incompatibility using the database version stored in the bootpage of the database. You cannot attach/restore a database with higher database version on a SQL Server that does not support it. Note that it is always possible to attach/restore a SQL Server 2000 database to SQL Server 2005. In fact, this must be allowed as otherwise customers will not be able to upgrade their database to run with newer SQL Server versions. In this case, the SQL Server 2005 understands the physical structures of SQL Server 2000 database and converts them to SQL Server 2005 specific structures during the upgrade process.

 

You may wonder why I am telling you all this in the context of Vardecimal storage format? Well, the Vardecimal storage format is a new storage format, introduced in SQL Server SP2, to store decimal/numeric data. This new storage format is not understood by SQL Server 2005 or SQL Server 2005/SP1. Just like you cannot attach a SQL Server 2005 database to SQL Server 2000, attaching/restoring a SQL Server 2005/SP2 database that has been enabled for Vardecimal storage format to earlier versions of SQL Server 2005 will fail. SQL Sever implements this by incrementing the database version number when the database is enabled for Vardecimal storage format. When you disable Vardecimal storage format on a database, its database version is decremented so that the database can now be attached to earlier versions of SQL Server 2005. There is one interesting scenario/requirement that you need to be aware of when disabling Vardecimal storage format on the database.

Scenario:

· Do a full physical backup (DB) on SQL Server 2005/SP1. This backup will have the database version that is supported by SQL Server 2005/SP1.

· Attach the database to SQL Server 2005/SP2.

· Enable Vardecimal storage format on the database.

· Create a table and enable it for Vardecimal storage format

· Insert one row. The log records generated by this will have data in Vardecimal storage format

· Disable Vardecimal storage format on the database.

· Do the logs backup (L). Again, this log backup will have the database version that is supported by SQL Server 2005/SP1.

 

Now if you restore (DB + L) on SQL Server/SP1, it will not be able to detect that there were log records with Vardecimal storage format and will potentially fail unpredictably. To prevent this situation, SQL Server requires you to set database in SIMPLE recovery mode before you can disable Vardecimal storage format on the database. When you do that, the log chain is broken and the above scenario is prevented. Note that you are not required to set database in SIMPLE recovery mode when disabling Vardecimal storage format on individual table(s). It is ONLY needed when disabling it on the database.

Comments

  • Anonymous
    January 11, 2007
    I understand why it's needed to switch to simple recovery mode when you disable vardecimal storage format. But why is it needed for enabling it ? The log backups would be marked with the new version while vardecimal  storage is enabled, so they would not able to be restored on SP1, right ? Razvan

  • Anonymous
    January 11, 2007
    RavzanL You are right. It is not needed for enabling. I read my BLOG again and I does not say that. Can you please clarify.. thanks

  • Anonymous
    January 15, 2007
    Books Online SP2 Dec CTP says: "Databases using vardecimal storage format must be set to the simple recovery model." This is in the "vardecimal storage format" topic (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b4a37f9c-3b71-43bd-b64e-2bfb5c5817ba.htm), in the "Limitations" section. If this is an error in the documentation, please make sure that it will be corrected in the next version of BOL. Razvan

  • Anonymous
    February 06, 2007
    SQL Server 2005 SP2 では、decimal, numeric データ型利用時のディスクスペースが減る !? vardecimal とは.