Bugfix 1.2-20110729 increases a timeout when estimating the size of a differential backup. Previous releases could fail with a timeout error if the database was large.
SQL Server Compressed Backup Bugfix Release
This entry was posted in Uncategorized. Bookmark the permalink.
Hi Clay,
I hope all is well with you. We spoke some time ago when you implemented a bug fix for me (i.e the timeout error). I’m running into an issue and I thought you would be the best person to contact. I’m using your program to backup databases and truncate the log files. However, I’m unable to back up the databases or truncate the log files in SQL 2008. I’m not a programmer so this isn’t my best strength so I would appreciate it if you can guide me in the right way. I do understand the Truncate_Only command is no longer in SQL 2008. I was wondering what would be the best way to make changes to the script so it would backup SQL 2008 databases as well as truncate the transacational log files. Here is the the script as of now:
function backupAndCompress {
Param($databasesToBackup=@(), $backupType=”full”)
foreach ($db in $databasesToBackup) {
clear-content G:\SQLCompress\Temp\temp.txt
$rootPath = “G:\Backups\$db”
$dayOfWeek = $(Get-Date).DayOfWeek
$instanceName = “”
$filePartPaths = 1..4 | foreach { “path=$rootPath\$db.$dayOfWeek.$backupType.$_.bak.zip;” }
$command = “G:\SQLCompress\MSSQLCompressedBackup-1.2-20110729_x64\msbp.exe backup ”
$dbParam = “`”db(database=$db;backuptype=$backupType;)`”"
$zipParam = “`”zip64(level=1)`”"
$destParam = “`”local(” + [string]::join(”, $filePartPaths) + “)`”"
$totalCommand = “$command $dbParam $zipParam $destParam”
Write-Host “$totalcommand”
Write-Host “Creating $backupType backup for $db”
$msg = “Creating $backupType backup for $db using $totalCommand”
$msg >> temp.txt
try
{
#run backup and output results to log file
$dt = $(Get-Date).ToString(“yyyyMMdd”)
Invoke-Expression $totalCommand >> “G:\SQLCompress\Temp\temp.txt”
get-content G:\SQLCompress\Temp\temp.txt >> “G:\SQLCompress\Logs\$dt-log.txt”
#check if the log file output an error
if ( get-childitem G:\SQLCompress\Temp\temp.txt | select-string -pattern “The backup failed.” -casesensitive ){
#raise a new exception
throw get-content G:\SQLCompress\Temp\temp.txt
}
#since the backup has now completed successfully, we need to truncate the log file
$SqlCmd = “BACKUP LOG [$db] WITH TRUNCATE_ONLY;”
$cmd = “sqlcmd -S $instanceName -d $db -Q `”$SqlCmd`”"
Invoke-Expression $cmd >> “G:\SQLCompress\Logs\$dt-log.txt”
}
catch
{
$emailFrom = “monitor@wealthserv.com”
$emailTo = “saud.ahmed@wealthserv.com”
$subject = $db + “WS-DB3 Backup Report”
$body = $_.Exception
$smtpServer = “10.20.0.10″
# $smtp = new-object Net.Mail.SmtpClient($smtpServer)
# $smtp.Send($emailFrom, $emailTo, $subject, $body)
}
clear-content G:\SQLCompress\Temp\temp.txt
}
}
function backupForDay {
Param($fullBackupDay, $databasesToBackup=@())
$backupType = “differential”
if ($(Get-Date).DayOfWeek -eq $fullBackupDay) {
$backupType = “full”
}
backupAndCompress $databasesToBackup $backupType
}
$fridayFull = @(“ws2005″, “ws3005″, “2005Report”, “3005Report”)
backupForDay “Friday” $fridayFull
——————————————-
The problem here is 2005Report and 3005Report are SQL 2008 Databases. Ca1001 and Ca1002 get backed up successfully (and log files also get truncated) whereas 5000Report and 6000Report (none of it happens). How do I go about backing these databases as well as truncating their logs? The error I am getting is:
local:
path=G:\Backups\2005Report\2005Report.Thursday.differential.1.bak.zip
path=G:\Backups\2005Report\2005Report.Thursday.differential.2.bak.zip
path=G:\Backups\2005Report\2005Report.Thursday.differential.3.bak.zip
path=G:\Backups\2005Report\2005Report.Thursday.differential.4.bak.zip
zip64: level = 1, filename=database.bak
zip64: level = 1, filename=database.bak
zip64: level = 1, filename=database.bak
zip64: level = 1, filename=database.bak
————————
Exception #1
VDS::GetConfiguration failed: x80770003
System.InvalidProgramException
at MSBackupPipe.VirtualBackupDevice.VirtualDeviceSet.GetConfiguration(Nullable`1 timeout) in c:\documents and settings\clay lenhart\my documents\mssqlcompressed\msbackuppipe.virtualbackupdevice\virtualdeviceset.cpp:line 128
at MSBackupPipe.Common.BackupPipeSystem.BackupOrRestore(Boolean isBackup, ConfigPair storageConfig, ConfigPair databaseConfig, List`1 pipelineConfig, IUpdateNotification updateNotifier) in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed\MSBackupPipe.Common\BackupPipeSystem.cs:line 108
————————
Exception #2
Could not locate entry in sysdatabases for database ’2005Report’. No entry found with that name. Make sure that the name is entered correctly.
BACKUP DATABASE is terminating abnormally.
System.Data.SqlClient.SqlException
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader()
at System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(IAsyncResult asyncResult)
at MSBackupPipe.Common.SqlThread.EndExecute() in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed\MSBackupPipe.Common\SqlThread.cs:line 491
The backup failed.
—————————————
If you could look into this and let me know what I can do to backup the databases as well as truncate the log, I would really appreciate it.
Saud,
The error is “Could not locate entry in sysdatabases for database ’2005Report’”. I’d double check the name.
You talked about truncating the log. I’d really discourage this. Truncating the log suggests that you do not want to have log backups, in which case you should change the recovery mode to “Simple” and not take log backups. If you do want log backups, don’t truncate the log. Taking the log backup will truncate the ldf file (assuming other SQL Server features do not require the log entries, like open transactions, mirroring, replication, etc)
Hi Clay,
Thanks for replying.
I just wanted to let you know that I got it resolved by following your advice i.e. changing it to the simple recovery model.
Merry Christmas & Happy New Years in advance !
Why is the the batch file fails to exit once the job is completed 100%?
There is usually a delay to allow the two internal threads to finish gracefully. If it doesn’t exit within 3 seconds, I’d say there is a problem with the threads finishing normally. I can’t think of what might be causing that though.
Clay,
I wanted to thank you for the SQL Server Compressed Backup program. We replaced our crusty Redgate backup compression in a Sql Server 2005 environment (old license will not work for Sql Server 2008) . We only needed the command line compression for use our backup service infrastructure. We backup our database , transmit transaction logs and differential updates to our remote standby and report servers.
Hi Clay,
I published a wrapper for the backup of all databases on a pair of their production servers using msbp.exe – http://msbplaunch.codeplex.com/
wbr, Nicholas
Thanks Nicholas
I am getting the error Unknown plataform on Windows 2008 R2 x64 and MSSQL 2005 Enterprise, do you have any pointers on howto solve it?