SQL Server Compressed Backup v1.2 Released

I’ve just released version 1.2-20091123 of SQL Server Compressed Backup which can be downloaded here.

SQL Server Compressed Backup will back up SQL Server databases using either gzip, zip or bzip2 compression.

The new features are:

  • Fixed the encoding problem by removing “\s”, “\p” and “\\” since they are commonly used in file paths and replacing them with “;;” to mean “;”. Looking at the command line options, it is only necessary to encode the semicolon, so “;;” is the only encoding the command line does.
  • Added a feature I’ve personally wanted: progress updates and an estimated time until completion. It is more verbose at the beginning (every few seconds), then slows down. After an hour of backing up, updates are only once every 24 minutes.
  • Worked on the BlockSize and BufferCount request. I’m not keen on implementing BlockSize. I’d like to hear someone explain how they take advantage of it. My understanding is that BlockSize is only for writing to devices (tapes and CD-ROMs — not files on a CD-ROM though), so it doesn’t seem appropriate for backing up to files. BufferCount and MaxTransferSize seem like reasonable options to request, so these are now available.

About Clay Lenhart

I am a DBA/Technical Architect for Latitude Group and love technology.
This entry was posted in MSSQL Compressed Backup and tagged , . Bookmark the permalink.

69 Responses to SQL Server Compressed Backup v1.2 Released

  1. Saud Ahmed says:

    Hi Clay,

    Thanks a lot for developing this program as we use it to compress our databases. While everything works as we want it to, recently we ran into an error which is as follows:

    System.Management.Automation.RuntimeException: Creating differential backup for 2440 using D:\SQLCompress\MSSQLCompressedBackup-1.2-20100527_x64\msbp.exe backup “db(database=2440;instancename=wsdb1;clusternetworkname=wsdb1;backuptype=differential;)” “zip64(level=1)” “local(path=D:\Backups\2440\2440.Thursday.differential.1.bak.zip;path=D:\Backups\2440\2440.Thursday.differential.2.bak.zip;path=D:\Backups\2440\2440.Thursday.differential.3.bak.zip;path=D:\Backups\2440\2440.Thursday.differential.4.bak.zip;)” ———————— Exception #1 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. 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.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at MSBackupPipe.Common.SqlThread.CalculateEstimatedDatabaseSize(SqlConnection cnn, Dictionary`2 dbConfig) in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed\MSBackupPipe.Common\SqlThread.cs:line 314 at MSBackupPipe.Common.SqlThread.PreConnect(String clusterNetworkName, String instanceName, String deviceSetName, Int32 numDevices, IBackupDatabase dbComponent, Dictionary`2 dbConfig, Boolean isBackup, IUpdateNotification notifier, Int64& estimatedTotalBytes) in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed\MSBackupPipe.Common\SqlThread.cs:line 67 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 95 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

    —————

    The backup runs successfully on Sat/Sun/Mon (All Differentials) & Fri (Full). However, on Tues/Wed/Thurs it fails due to the above error.

    This is the largest database we have and for some reason it fails maybe due to the increasing size over the days. Anyway, I know increasing the time out will resolve the error. However, I don’t know where to increase the time out as I can’t find any documentation on this. We are using a powershell script to invoke the command.

    The connection string is as follows:

    $dbParam = “`”db(database=$db;instancename=$instanceName;clusternetworkname=wsdb1;backuptype=$backupType;)`”"

    When I add CommandTimeout=10000 to the above string, the script does not create any backups but runs successfully. This is how I am editing the connection string:

    $dbParam = “`”db(database=$db;instancename=$instanceName;clusternetworkname=wsdb1;backuptype=$backupType;Command Timeout=10000;)`”"

    _____________________

    Can you please let me know how to fix this error?

    I would really appreciate it if you can help.

    Thanks a lot.

  2. Clay Lenhart says:

    Saud,
    I’m glad to hear about people using it. Thanks for letting me know.

    As for your problem, msbp.exe has to run on the active node. It sounds like the active node moved to another one Tues/Wed/Thurs, and then was active again on the original node Fri.

    Thinking how I’d handle this if I were in your shoes, I’d setup scheduled tasks on all the nodes in the cluster, but do not schedule them. This scheduled task will run msbp.exe to backup the database(s). Then on one node I’d create another schedule task to remotely start the first scheduled task using the cluster network name so that it finds the active node. You can remotely start scheduled tasks using schtasks.exe which comes with Windows. http://support.microsoft.com/kb/814596

  3. Saud Ahmed says:

    Clay,

    I really appreciate you replying so quickly. Thanks for answering my question.

    Unfortunately, your answer doesn’t apply in my situation. Let me explain to you a little more as to what is going on.

    We have two database servers namely WS-DB1 and WS-DB2 and they are clustered. However, they’ve both been setup as active nodes. We use a powershell script to compress and backup our databases.

    All backups run successfully on both nodes on all days except for 2440 (which resides on WS-DB1) which fails on Tuesday/Wednesday and Thursday (All differentials). 2440 is the only database that fails to backup on those database. Just so you know, 2440 is the largest database we have as in 300GB compared to the other database we have which are merely 60GB each.

    Is there any other way around this because I get a time out error for this database on those days. My guess is it takes to long to connect or takes too long to backup hence the failure.

    Is there a time out setting that I can change somewhere or Do you have any other suggestions that could help me fix this issue?

    Please let me know, Thanks a lot.

  4. Saud Ahmed says:

    *2440 is the only database that fails to backup on those days.

  5. Clay Lenhart says:

    OK, I see now. It’s calculating the number of changed pages by calling DBCC PAGE on all the Differential Changed Map pages. The larger the database, the more DBCC PAGE calls there will be. I’ll cut another build with a longer timeout. Or perhaps it should sample a percentage of them.

  6. Clay Lenhart says:

    Saud,
    There is an updated version on the source forge site: https://sourceforge.net/projects/mssqlcompressed/
    Please download and try it again. The timeout is set to 10 minutes now.

  7. Saud Ahmed says:

    Clay,

    Thanks a lot for replying and increasing the timeout. It is really nice of you to not only reply but also take the time out to takecare of people that use your program.

    I will try to get it setup by tommorrow and let you know if the backup fails or not.

  8. Clay Lenhart says:

    Saud, no problem! Let me know how it goes.

  9. Mirco Bondatti says:

    Hello
    I was just looking for another way to compress our SQL backups, and I found your tool.
    First of all thanks for developing and sharing your work.
    In order to backup DBs on clusters it could be enough to schedule its execution through a SQL Agent job.
    Since Maintenance Plans are directly managed by the SQL instance, you should not need to schedule it on all nodes, as the Job would run always on the active one.
    What do you think about that? Have you ever tried it?
    Thanks and regards

  10. Saud Ahmed says:

    Clay,

    It worked !! I used the new version and the backup ran successfully on Tuesday, Wednesday and Thursday. I checked the logs and everything is golden. I’ll keep monitoring it over the next two weeks and see if it fails once again (hopefully not).

    Thanks a lot once again ! I really appreciate your help.

  11. Hello
    I want tou use this great software to compress my SQL backups, but when I try to run in our server which have an instance name, the process raise an error:

    C:\backups>msbp.exe backup “db(database=model)” “zip64(level=3)” “local(path=c:\
    model.bak.zip)”
    local:
    path=c:\model.bak.zip
    zip64: level = 3, filename=database.bak
    ————————
    Exception #1
    VDS::Create failed: x80770007

    System.InvalidProgramException
    at VdiNet.VirtualBackupDevice.Native.VirtualDeviceSet.CreateEx(String instanc
    eName, String deviceSetName, VirtualDeviceSetConfig config) in c:\users\clay\doc
    uments\vdi.net\vdinet.virtualbackupdevice.native\virtualdeviceset.cpp:line 98
    at VdiNet.VirtualBackupDevice.Exe32Sql32.VirtualDeviceSetExe32Sql32.CreateEx(
    String instanceName, String deviceSetName, VirtualDeviceSetConfig config) in C:\
    Users\Clay\Documents\vdi.net\VdiNet.VirtualBackupDevice\Exe32Sql32\VirtualDevice
    SetExe32Sql32.cs:line 23
    at MSBackupPipe.Common.BackupPipeSystem.BackupOrRestore(Boolean isBackup, Con
    figPair storageConfig, ConfigPair databaseConfig, List`1 pipelineConfig, IUpdate
    Notification updateNotifier) in C:\Users\Clay\Documents\MSSQL Compressed\mssql s
    ourceforge2\MSBackupPipe.Common\BackupPipeSystem.cs:line 108

    The backup failed.

    What can be happening ?
    Thanks

  12. Clay Lenhart says:

    Mirco, I haven’t tried it this way, but makes sense! Thanks for the tip.

  13. Clay Lenhart says:

    Saud, Great to hear! Thanks for the update.

  14. Clay Lenhart says:

    Royner,
    It can’t find the VDI dll, which on my machine is located here:
    C:\Program Files\Microsoft SQL Server\80\COM\sqlvdi.dll
    The path varies from installation to installation, but it’ll be in a similar place (….80\COM\sqlvdi.dll).

    I see this occur when people try to backup a database remotely, which isn’t possible with this tool.

  15. Kevin Batman says:

    Hello Clay,

    I am currently using MSSQLCompressedBackup via a script on a server running Windows Server 2003 (32-bit) and want to use it also on another server running Windows Server 2008R2 (64-bit). I am getting the following error which seems to be the same error that Royner Suarez was getting:
    C:\MSSQLCompressedBackup-1.2-20100527_x64>msbp backup [model] gzip file:///c:\scripts\model.bak.gz
    local:
    path=c:\scripts\model.bak.gz
    gzip: level = 9
    ————————
    Exception #1
    VDS::Create failed: x80070005

    System.InvalidProgramException
    at MSBackupPipe.VirtualBackupDevice.VirtualDeviceSet.CreateEx(String instance
    Name, String deviceSetName, VirtualDeviceSetConfig config) in c:\documents and s
    ettings\clay lenhart\my documents\mssqlcompressed\msbackuppipe.virtualbackupdevi
    ce\virtualdeviceset.cpp:line 96
    at MSBackupPipe.Common.BackupPipeSystem.BackupOrRestore(Boolean isBackup, Con
    figPair storageConfig, ConfigPair databaseConfig, List`1 pipelineConfig, IUpdate
    Notification updateNotifier) in C:\Documents and Settings\Clay Lenhart\My Docume
    nts\mssqlcompressed\MSBackupPipe.Common\BackupPipeSystem.cs:line 105

    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

    I am running the 64-bit version of the software. Any ideas? The path to the sqlvdi.dll is what you say it is on your machine, so I don’t believe that is the problem.

    Thanks.

  16. Clay Lenhart says:

    Kevin,

    A “CreateEx” error on Win2008+ is likely due to not having admin privledges. You’ll need to start a command prompt with “run as administrator” to run the tool.

    Next on the list is to change the app to automatically upgrade to administrative privledges automatically.

  17. Clay Lenhart says:

    Royner,
    That probably applies to your situtation too (running the tool with admin privledges).

  18. Thanks for your response Clay.
    The problem was related to the INSTANCENAME paramater that must have passed to the SQL server if you are using SQL instances on it.
    Now It works fine…..
    Is this software able to backup SQL server 7.0 datatabases ?

  19. Clay Lenhart says:

    Royner,
    I’d suspect that it wouldn’t work. I’m pretty sure that it’s compiled against the 8.0 (SQL 2000) version of the DLL.
    Clay

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=""> <strike> <strong>