You may notice the below error while configuring your SQL server to support backup compression by executing sp_configure ‘backup compression default’ ’1′ SQL command.
The configuration option ‘backup compression default’ does not exist, or it may be an advanced option.
The reason for this failure is, simple. You SQL server version is not supporting compression. Per Technet (http://technet.microsoft.com/en-us/library/bb964719.aspx), only SQL 2008 enterprise and later versions supports backup compression. That means, if you have any flavors of SQL server 2008 except enterprise version, you can not use backup compression feature. However, you can still use any version of SQL server 2008 to restore compressed backups. When it comes to SQL server 2008 R2(not that I earlier talked about only SQL 2008 — not R2), we can use any version to backup in compressed format and similarly the restorations.
To find the version of your SQL server, you can either check it from GUI or just use the below powershell code.
$SQLServer = "Myserver1"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQLServer
$server | Select Name, Version
This code returns the version number(like 10.0.2531). You can refer http://sqlserverbuilds.blogspot.in/ to know product name of this version.
Please feel free to post if you have any questions.
While looking for a quick way to check when a SQL database was last backed up, the immediate thing that came into my mind is this post. I trolled around the parameters and functions of ‘Microsoft.SqlServer.Management.smo.Server’ object and developed below tiny script to list the last backup date time of databases in a SQL server.
function Get-SQLLastBackupTime {
param(
[Parameter(mandatory=$true)]
[string]$DBServer
)
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | Out-Null
$server = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “$DBServer”
$server.databases | select Name, LastBackupDate
}
Hope this help …
Below small function helps you to list databases in a given database server using powershell. This will come handy for you when you quickly want to check the databases list without logging on to the server.
function Get-SQLDatabases {
param(
[Parameter(mandatory=$true)]
[string]$DBServer
)
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | Out-Null
$server = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “$DBServer”
$server.databases | select Name
}
Usage :
C:>Get-SQLDatabases -DBServer MSSQLSRV1
Hope this helps…
As subject says, MS has released a new FREE e-book on MS SQL 2008 R2 a few days back. You can grab a copy of it from MS Press Blog
While installing cluster instance, it is failing at the initial stage of setup with unexpected error message pop-up. I reviewed the “C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGsummary.txt” log file and it has the below error.
Microsoft SQL Server 2005 9.00.4035.00
==============================
OS Version : Microsoft Windows Server 2003 family, Enterprise Edition Service Pack 2 (Build 3790)
Time : Fri Oct 16 12:53:22 2009
NODE1 : To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
NODE1 : There was an unexpected failure during the setup wizard. You may review the setup logs and/or click the help button for more information.
There can be two reasons here…
1) Improper directory structure in SQL installation source
2) You are trying to install different version of SQL than what you have on cluster.
If you find both the above are not applicable in your case, then go to “C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0018__Core(Patched).log” log file and see what it says. In my case, I have below error message in the logs which is indicating problems with reading one of the shared drive Q:. Sometime back I have moved system databases from one drive to another for a cluster instance and deleted the old drive. But the regkey(HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.9SetupSetupDataRoot) is still containing the old drive letter. Cluster setup is trying to read this key and failing because it cannot find the cluster drive Q: which I deleted some time back.
———————————————————————
Failed to find property “ComputerList” {“SqlComputers”, “”, “”} in cache
Source File Name: datastoreclusterinfocollector.cpp
Compiler Timestamp: Mon Nov 17 17:05:55 2008
Function Name: ClusterInfoCollector::collectClusterVSInfo
Source Line Number: 888
———————————————————-
Failed to detect VS info due to datastore exception.
Source File Name: datastoreclustergroupsproperties.cpp
Compiler Timestamp: Mon Nov 17 17:05:55 2008
Function Name: ClusterGroupScope.SharedDisks
Source Line Number: 56
———————————————————-
Failed to find a cluster group that owned shared disk:Q:
WinException : 2
Error Code: 0×80070002 (2)
Windows Error Text: The system cannot find the file specified.
———————————————————————
Now I went ahead and changed the registry key value to new drive name in all cluster nodes and restarted the setup. It worked like a champ and I continued with my work. I am posting here it to help others who stuck with same problem. This may not be the cause always but I am just outlining the procedure for troubleshooting.
Happy learning…,
Sitaram Pamarthi