Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sys.configurations missing 'backup compression default' Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 4:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 15, 2013 1:39 PM
Points: 28, Visits: 58
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 count 0 65536 0 0
access check cache quota 0 2147483647 0 0
Ad Hoc Distributed Queries 0 1 0 0
affinity I/O mask -2147483648 2147483647 0 0
affinity mask -2147483648 2147483647 0 0
affinity64 I/O mask -2147483648 2147483647 0 0
affinity64 mask -2147483648 2147483647 0 0
Agent XPs 0 1 1 1
allow updates 0 1 0 0
awe enabled 0 1 0 0
blocked process threshold (s) 0 86400 0 0
c2 audit mode 0 1 0 0
clr enabled 0 1 0 0
cost threshold for parallelism 0 32767 5 5
cross db ownership chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
Database Mail XPs 0 1 1 1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
default trace enabled 0 1 1 1
disallow results from triggers 0 1 0 0
filestream access level 0 2 0 0
fill factor (%) 0 100 0 0
ft crawl bandwidth (max) 0 32767 100 100
ft crawl bandwidth (min) 0 32767 0 0
ft notify bandwidth (max) 0 32767 100 100
ft notify bandwidth (min) 0 32767 0 0
index create memory (KB) 704 2147483647 0 0
in-doubt xact resolution 0 2 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 64 1 1
max full-text crawl range 0 256 4 4
max server memory (MB) 16 2147483647 9216 9216
max text repl size (B) -1 2147483647 65536 65536
max worker threads 128 32767 0 0
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
Ole Automation Procedures 0 1 1 1
open objects 0 2147483647 0 0
optimize for ad hoc workloads 0 1 0 0
PH timeout (s) 1 3600 60 60
precompute rank 0 1 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
Replication XPs 0 1 0 0
scan for startup procs 0 1 1 1
server trigger recursion 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
SMO and DMO XPs 0 1 1 1
SQL Mail XPs 0 1 0 0
transform noise words 0 1 0 0
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
xp_cmdshell 0 1 1 1

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
Post #1388893
Posted Monday, November 26, 2012 10:46 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
Are you using Expresse Edition? I believe it wont be there.
Post #1388944
Posted Tuesday, November 27, 2012 12:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 AM
Points: 42,412, Visits: 35,479
itis4junk (11/26/2012)

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


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 2008, MVP
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

Post #1388985
Posted Tuesday, November 27, 2012 7:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 15, 2013 1:39 PM
Points: 28, Visits: 58
I'll request the database to be upgraded.


Thank you,


Khanh Pham
Post #1389483
Posted Wednesday, November 28, 2012 4:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:27 PM
Points: 6,162, Visits: 13,303
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"
Post #1389629
Posted Wednesday, November 28, 2012 6:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 AM
Points: 42,412, Visits: 35,479
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 2008, MVP
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

Post #1389726
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse