May 6, 2011 at 5:04 pm
SanDroid (5/6/2011)
Paul Randal (5/6/2011)
There's no choice but to have it behave that way because something in the transaction log that gets recovered at the end of the restore good disable compression - making the DB ok for Standard. But yes, the behavior is a little perverse.So are you saying that if you:
1. Make two Database on 2008 Enterprise Edition w FULL Recovery option and data compression enabled in both.
2. Insert a bunch of data.
3. Disable Data Compression on both.
4. Enable Data compression on one
4. Create a FULL Backup of both DB.
Will --
A. One of these DB will restore on SQL 2008 Standard and one will not.
or
B. Both will fail, but one will fail sooner than the other.
Dose that cover it?
The change in Data Compression can only be seen in the Tran Log and not in the Full Backup Header created after the change?
One fails, one succeeds.
Data compression cannot be enabled in the backup or the restore will fail.
May 6, 2011 at 11:08 pm
Excellent question..Learned something new..
May 7, 2011 at 12:22 pm
Paul Randal (5/6/2011)
There's no choice but to have it behave that way because something in the transaction log that gets recovered at the end of the restore good disable compression - making the DB ok for Standard. But yes, the behavior is a little perverse.
Surely it would be possible to decide at the end of the backup (when the last log data has been appended) that the use of dta compression has changed and overwrite something in the first block/page/sector (whatever you want to call it) (and yes that could require tape remounts) so that the actual situation is described in that first block. Of course you would need a parameter to say this should not be done (since in the tape case there is a risk that this will effectively destroy the backup, although this risk can be made extremely small by appropriate use of tapemark blocks unless standards have changed radically since I last played with tapes). I feel that this something that ought to have been done - although of course it would be much better if it were arranged that the data storage engine in all editions could read and decompress compressed pages, and simply allow some editions to be unable to compress (so that if a page were updated either the written back version would not be compressed (and might occupy more than a page) or the update would fail because the compressed parts of teh DB were treated as read-only in lesser editions.
Tom
May 8, 2011 at 6:30 pm
Tricky question.
I knew that data compression was only available in the Developer to Enterprise and higher editions but I never tried restoring a compressed backup in a Standard Edition before.
Well, lost my point but learnt something new today.
Thank you. 🙂
[EDIT]: Now I understand that I got it wrong. I thought you were talking about compressed backups. I should've payed more attention. :w00t:
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
May 9, 2011 at 2:54 am
Paul Randal (5/6/2011)
tommyh (5/5/2011)
What the **** was Microsoft thinking here... ah thats right they wherent thinking as usual... sigh.Great question though, definatly worth knowing.
/T
There's no choice but to have it behave that way because something in the transaction log that gets recovered at the end of the restore good disable compression - making the DB ok for Standard. But yes, the behavior is a little perverse.
An interesting point. Which got me thinking. And wasting time. Since i had to install SQL2008 in order to be able to test my idea. Im "abit" behind.
My thought was: What about temptables? Or even more fun: What about temptables and SPs?
And the answer?... not good.
Creating an SP that uses a temptable with compression... no problems.
Restoring a database with this SP in it to SQL Standard (or in my case Express)... no problems.
Executing the SP in the new database... fail.
It fails with it not being able to add compression to its temptable. Which is understandable... its not supposed to be able to do that.
However you have NO idea that it will fail until you actually run the SP. Now this is most definatly not good. Since you now have a database that might not actually work. Yes the data is there but if the data is accessed with SPs then any number of these might fail. And you wont know which ones will work and which ones wont work.
So i think i have to stick with my initial comment that this is most definatly not thought through. Having features that wont work in some versions and not having a good way of checking if they are used... thats not so good.
But hay maybe there are a good way to check for this... just that i dont know about it... which isnt all that unlikely:-D
/T
May 9, 2011 at 3:25 am
Good question.
May 9, 2011 at 4:54 am
Another one here who didn't read the question properly and thought it was talking about backup compression 🙁
May 9, 2011 at 4:58 am
Duncan Pryde (5/9/2011)
Another one here who didn't read the question properly and thought it was talking about backup compression 🙁
I should add: good question though, thanks.
May 9, 2011 at 10:19 am
good question and i agree Microsoft does not have good documentation about it
December 19, 2011 at 5:54 am
You didn't specify all the facts. Should have stated that the database was using Enterprise level features.
---------------
Mel. 😎
December 19, 2011 at 7:17 am
SqlMel (12/19/2011)
You didn't specify all the facts. Should have stated that the database was using Enterprise level features.
It was stated very clearly and unambiguously:
"...a SQL Server 2008 Enterprise Edition database that uses data compression"
If you were not aware that data compression is an enterprise level feature that's your problem, not something missing in the question.
Tom
December 19, 2011 at 7:23 am
L' Eomot Inversé (12/19/2011)
SqlMel (12/19/2011)
You didn't specify all the facts. Should have stated that the database was using Enterprise level features.It was stated very clearly and unambiguously:
"...a SQL Server 2008 Enterprise Edition database that uses data compression"
If you were not aware that data compression is an enterprise level feature that's your problem, not something missing in the question.
I must apologize. I misread "data compression" with "backup compression"
I am aware that data compression is an enterprise level feature. 🙂
---------------
Mel. 😎
Viewing 12 posts - 46 through 56 (of 56 total)
You must be logged in to reply to this topic. Login to reply