November 26, 2012 at 4:29 pm
Hello,
We have a SQL 2008 database with compatibility level set to 80. I don't know if this is the cause of no entry 'backup compression default' in sys.configurations when ran:
sp_configure 'show advanced options',1
GO
reconfigure
Go
sp_configure
go
Result:
access check cache bucket count06553600
access check cache quota0214748364700
Ad Hoc Distributed Queries0100
affinity I/O mask-2147483648214748364700
affinity mask-2147483648214748364700
affinity64 I/O mask-2147483648214748364700
affinity64 mask-2147483648214748364700
Agent XPs0111
allow updates0100
awe enabled0100
blocked process threshold (s)08640000
c2 audit mode0100
clr enabled0100
cost threshold for parallelism03276755
cross db ownership chaining0100
cursor threshold-12147483647-1-1
Database Mail XPs0111
default full-text language0214748364710331033
default language0999900
default trace enabled0111
disallow results from triggers0100
filestream access level0200
fill factor (%)010000
ft crawl bandwidth (max)032767100100
ft crawl bandwidth (min)03276700
ft notify bandwidth (max)032767100100
ft notify bandwidth (min)03276700
index create memory (KB)704214748364700
in-doubt xact resolution0200
lightweight pooling0100
locks5000214748364700
max degree of parallelism06411
max full-text crawl range025644
max server memory (MB)16214748364792169216
max text repl size (B)-121474836476553665536
max worker threads1283276700
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364700
nested triggers0111
network packet size (B)5123276740964096
Ole Automation Procedures0111
open objects0214748364700
optimize for ad hoc workloads0100
PH timeout (s)136006060
precompute rank0100
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote admin connections0100
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
Replication XPs0100
scan for startup procs0111
server trigger recursion0111
set working set size0100
show advanced options0111
SMO and DMO XPs0111
SQL Mail XPs0100
transform noise words0100
two digit year cutoff1753999920492049
user connections03276700
user options03276700
xp_cmdshell0111
ServerProperty settings:
Instance: NULL
IsClustered: 0
ComputerNamePhysicalNetBIOS: MainProduction1
Edition: Standard Edition (64-bit)
ProductLevel: SP1
ProductVersion: 10.0.2531.0
ProcessID: 1608
Collation: SQL_Latin1_General_CP1_CI_AS
How do I configure 'backup compression default' in sys.configurations, if it is possible?
Thanks,
Khanh
November 26, 2012 at 10:46 pm
Are you using Expresse Edition? I believe it wont be there.
November 27, 2012 at 12:07 am
itis4junk (11/26/2012)
Instance: NULLIsClustered: 0
ComputerNamePhysicalNetBIOS: MainProduction1
Edition: Standard Edition (64-bit)
ProductLevel: SP1
ProductVersion: 10.0.2531.0
ProcessID: 1608
Collation: SQL_Latin1_General_CP1_CI_AS
Standard edition cannot create compressed backups. It's an enterprise-only feature in SQL 2008 (It's in Standard Edition in SQL 2008 R2)
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
November 27, 2012 at 7:08 pm
I'll request the database to be upgraded.
Thank you,
Khanh Pham
November 28, 2012 at 4:17 am
itis4junk (11/27/2012)
I'll request the database to be upgraded.Thank you,
Khanh Pham
Theres a vast difference in the licencing costs from Std to Ent, if all you want is compression then Redgate's SQLBackup or Quest's LiteSpeed both offer a good alternative and are very reasonably priced.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 28, 2012 at 6:43 am
itis4junk (11/27/2012)
I'll request the database to be upgraded.
If you mean upgraded to SQL 2008 R2, bear in mind that is a different version of SQL (not a service pack) and hence requires new licences, testing, an upgrade plan, etc.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply