SQL Server Compressed Backup Bugfix Release

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.

About Clay Lenhart

I am a DBA/Technical Architect for Latitude Group and love technology.
This entry was posted in Uncategorized. Bookmark the permalink.

16 Responses to SQL Server Compressed Backup Bugfix Release

  1. Saud says:

    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.

  2. Clay Lenhart says:

    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)

  3. Saud says:

    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 !

  4. AC says:

    Why is the the batch file fails to exit once the job is completed 100%?

  5. Clay Lenhart says:

    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.

  6. paul says:

    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.

  7. Nicholas Elshanski says:

    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

  8. Clay Lenhart says:

    Thanks Nicholas

  9. Armindo Silva says:

    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?

  10. Bogdan Dumitru says:

    Hi Clay,
    i have a problem when i try to use your application for creating a compressed backup.
    The message is:

    local:
    path=e:\MSSQL10.INST01.Backup\master.bak.zip
    zip64: level = 7, filename=database.bak
    ————————
    Exception #1
    VDS::Create failed: x80770007
    NULL
    System.InvalidProgramException
    at VdiNet.VirtualBackupDevice.Native.VirtualDeviceSet.CreateEx(String instanceName, String deviceSetName, VirtualDeviceSetConfig config) in c:\users\clay\documents\vdi.net\vdinet.virtualbackupdevice.native\virtualdeviceset.cpp:line 101
    at VdiNet.VirtualBackupDevice.Exe64Sql64.VirtualDeviceSetExe64Sql64.CreateEx(String instanceName, String deviceSetName, VirtualDeviceSetConfig config) in C:\Users\Clay\Documents\vdi.net\VdiNet.VirtualBackupDevice\Exe64Sql64\VirtualDeviceSetExe64Sql64.c
    s:line 22
    at MSBackupPipe.Common.BackupPipeSystem.BackupOrRestore(Boolean isBackup, ConfigPair storageConfig, ConfigPair databaseConfig, List`1 pipelineConfig, IUpdateNotification updateNotifier) in C:\Users\Clay\Documents\MSSQL Compressed\mssql sourceforge2\MSB
    ackupPipe.Common\BackupPipeSystem.cs:line 109
    NULL
    NULL
    The backup failed.
    Below are the commands for msbp.exe:
    msbp.exe help
    msbp.exe backup
    msbp.exe restore
    msbp.exe listplugins
    msbp.exe helpplugin
    msbp.exe version
    NULL
    For more information, type msbp.exe help
    NULL

    Can you help me?
    ps: it is not a default instance , it is a named instance .

    Thank you!

  11. SRINI says:

    Hi Clay,

    When we are tring to take the compressed backup we are getting the below error message.

    My environment details
    Microsoft SQL Server 2005 – 9.00.3282.00 (Intel X86) Aug 5 2008 01:01:05
    Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT
    5.2 (Build 3790: Service Pack 2)

    I am using the compatible version of mspb.exe.
    Any help would be greatly appreciated as we are unaable to take compressed backup.

    “Could not create an instance: CLSID_MSSQL_ClientVirtualDeviceSet, x8007007E System.InvalidProgramException at

    MSBackupPipe.VirtualBackupDevice.VirtualDeviceSet..ctor() in c:\documents and settings\clay lenhart\my documents\mssqlcompressed

    \msbackuppipe.virtualbackupdevice\virtualdeviceset.cpp:line 50 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

    \mssqlcompre ssed\MSBackupPipe.Common\BackupPipeSystem.cs:line 179 at MSBackupPipe.Common.BackupPipeSystem.Backup(ConfigPair databaseConfig, List`1

    pipelineConfig, ConfigPair storageConfig, IUpdateNotification updateNotifier) in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed

    \MSBackupPipe.Common\Ba ckupPipeSystem.cs:line 51 at MSBackupPipe.Cmd.Program.Main(String[] args) in C:\Documents and Settings\Clay Lenhart\My Documents

    \mssqlcompressed\MSBackupPipe.Cmd\Program.cs:line 111 The backup failed. Below are the commands for msbp.exe: msbp.exe help msbp.exe backup msbp.exe

    restore msbp.exe listplugins msbp.exe helpplugin msbp.exe version For more information, type msbp.exe help “

  12. Mark says:

    Hi Clay,
    We’re using your excellent solution to backup a large(for us) 280 GB SQL Server 2005 Database.
    Intermittantly we get the error ‘Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.’
    We’re on version 1.2.0.0 29/07/2011 – are you aware of this issue and is there anything we can do?
    Many Thanks for your help
    Mark

  13. 古今東西、アーティストの花の深い降参だ。も秀でているも咲き乱れる花の錦の群れ、バラ色の花びらに配ってしなやかな雰囲気。時計師たちはひとペアの美しさを発見する目と無限のアイデア、花卉の元素に解けて時計設計の中では、細工が巧みでの好プレーかぐわしい花を表現するのが生き生きとして、花が満開のときの美しい瞬間を再現。 http://www.brandiwc.com/brand-super-12-copy-0.html

  14. 有効な対応を今回の世界的な金融と経済危機、スイス各界の去年は行動して。政府は前後して2度経済振興計画を発表し、税収の手段を通じて呼びかけと励まし社会の各分野の減少リストラ;経済界を積極的に対応し、勤務時間短くコスト削減の方式で、努力して就業市場の安定を維持する。昨年12月、スイス300社以上の企業のスタッフの労働時間短縮、今年1月にこのような企業が増えた1200家。スーパーコピーバッグ労働時間の短縮と同時に、大多数の企業が従業員を育成して、未来への就職市場は不測の風雲。最近の一項の経済調査によると、これまでの半分以上の従業員を保留スイスたい仕事で減少の給与、しかもしっかり職場の変化の準備。 http://www.gginza.com/%E6%99%82%E8%A8%88/%E3%83%AD%E3%83%AC%E3%83%83%E3%82%AF%E3%82%B9/daytona/04e74ce5ed154d2e.html

  15. 2010年のカルティエのブランドの新しいコレクションメンズ腕時計の程度というをリリースしました。 ガガ・ミラノスーパーコピー それはむしろ革命的なブランドのためのいくつかの理由のために、カルティエのメンズコレクションは、21世紀にもたらしている。それは大量生産された腕時計は、社内のカルティエの運動以前に彼らの最もハイエンドの作品の中に存在するだけが含まれるものがありました。 http://www.brandiwc.com/brand-super-13-copy-0.html

  16. フランスも誕生したたくさんの有名な時計ブランドを含む有名のトップブランドブレゲ、そして彼の姉妹ブランドL.LEROY、ハイエンドのPequignet赫柏林など、優秀なブランドは、新古典設計スタイル抜群。面白いのは、”と呼ばれる時計の父」ブレゲ時計師さんがそろそろ人生の3分の2のはフランス過ごした、彼はパリ学習タブ技術、そしてまたパリを発明した多くの時計技術及び技術、陀フライホイール、万年暦、ブレゲさんがパリ発明のさえ、ブレゲ時計ブランドはすべてフランス創立の。 パネライ時計コピー今日は私たちは瑞表で見た多くの技術やデザインはフランスから伝わってきたもので、パリ飾釘、玑粘紋、ブレゲ指針など、フランス手厚いタブ歴史ヨーロッパ時計業界全体に影響を与えている今。 http://www.ooobrand.com/bags/hermes/2249.html

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>