May 6, 2011 at 8:30 am
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.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 6, 2011 at 8:32 am
WadeN 75975 (5/6/2011)
So...none of the 4 answers are completely correct, altho the 4th one is closest. Would this then be the correct answer: "The restore will copy all the data from the backup file and then MAY fail" ?
The 4th answer is 100% correct given the wording in the question.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 6, 2011 at 8:34 am
Data Compression is a feature, as noted in the question explanation that is only available in Enterprise and Data Center editions. If you restore a backup that contains this, or any of the four Enterprise only features (data compression, partitioning, TDE, CDC), the restore will appear to work. It will run, all data will be copied, and then before the database is brought online, the restore will fail and the database will be unusable.
My Connect item is based on the fact that if you mistakenly restored on a lower edition, and you had a large database, you would have wasted a lot of time. There are databases that take hours to restore and doing that twice can be an issue.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 6, 2011 at 8:35 am
Koen Verbeeck (5/6/2011)
tommyh (5/6/2011)
honza.mf (5/5/2011)
20 optimists now.Hrmmm... in this case whats an optimist. One that thinks it will work or one that thinks it will fail at once... or both? :unsure:
/T
A pessimist is an optimist with experience 😀
Anyway, nice question. Good to know.
Nice!
May 6, 2011 at 8:42 am
I assumed that the question was referring to backup compression, not page or row compression. I think the question would be more clear if the terminology of "page compression" or "row compression" was used instead of the more generic and misleading "data compression".
MWise
May 6, 2011 at 9:26 am
Chris Houghton (5/6/2011)
Good question. I rushed it and misread "data" compression as "backup compression" so I selected that it would succeed. Haste makes waste.
+1
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 6, 2011 at 9:38 am
Mhlewis (5/6/2011)
I assumed that the question was referring to backup compression, not page or row compression. I think the question would be more clear if the terminology of "page compression" or "row compression" was used instead of the more generic and misleading "data compression".MWise
I can appreciate this, but "data compression" is listed as a feature. There's a white paper (http://msdn.microsoft.com/en-us/library/dd894051%28v=SQL.100%29.aspx), stored procs: sp_estimate_data_compression_savings (http://msdn.microsoft.com/en-us/library/cc280574.aspx) and a wizard (data compression wizard in SSMS).
Backup compression is also listed by that name in a few links people have posted in this discussion.
There wasn't an attempt to be tricky. Data Compression was just the first feature I thought of from EE.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 6, 2011 at 9:43 am
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.
What I'm reading into this is that Standard still understands data compression since it would have to for the part of the log that disables compression to run through. Is that correct? Or is it something like dropping the table or index that you're referring to which wouldn't "disable" compression but instead just remove the table that uses the feature?
May 6, 2011 at 10:08 am
Good question.
I got that one wrong by assuming that backup would be up-front about whether row and page compression had been included so that failure would be immediate. Now I know better (so a good question); and I've upvoted the connect item.
Tom
May 6, 2011 at 10:30 am
Nice question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 6, 2011 at 1:55 pm
jadsmith (5/6/2011)
It would depend on if the database backed up contains Enterprise features or not. If not the backup should restore with no problems. The question doesn't say that there are such features used in the database.The books online page http://msdn.microsoft.com/en-us/library/bb964719.aspx indicates that every edition of sql server 2008 can restore a compressed backup, though only 2008 Enterprise or 2008R2 Standard and higher can create the compressed backup
It does not matter if the DB backuup is compressed or not, this restore will fail becuase the DB data has compression, and a standard server can not support.
I definately understand the behavior if there is no uncomressed header information in the backup file that Standard Edition can read and know data compression is on and fail the backup.
I just think it a little dumb that this is still happens since It was reported as being a problem during the first Partner BETA release of 2008. :w00t:
May 6, 2011 at 2:14 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.
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?
May 6, 2011 at 2:43 pm
So this says
Note
Creating compressed backups is supported only in SQL Server 2008 Enterprise and later, but beginning in SQL Server 2008, every edition can restore a compressed backup.
http://msdn.microsoft.com/en-us/library/bb964719%28v=SQL.100%29.aspx
I dont have an environment to test on but I got it wrong based on this article.
May 6, 2011 at 2:52 pm
Obs (5/6/2011)
Creating compressed backups is supported only in SQL Server 2008 Enterprise and later, but beginning in SQL Server 2008, every edition can restore a compressed backup.http://msdn.microsoft.com/en-us/library/bb964719%28v=SQL.100%29.aspx
I dont have an environment to test on but I got it wrong based on this article.
The question is about table compression, not backup compression. To answer your question, it's saying any edition of SQL 2008 can restore a compressed backup. In SQL 2008 Enterprise Edition can create a compressed backup. If you look at the SQL 2008 R2 version of the same page you'll see that it states that SQL 2008 R2 Standard Edition (and higher) can create a compressed backup. It also retains the note about any edition of SQL 2008 being able to restore a backup.
http://msdn.microsoft.com/en-us/library/bb964719%28v=SQL.105%29.aspx
May 6, 2011 at 2:52 pm
Thanks for the question! I am surprised at the percentage getting it wrong, but I see that a lot of people thought backup and not data compression.
Viewing 15 posts - 31 through 45 (of 56 total)
You must be logged in to reply to this topic. Login to reply