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.

129 Responses to SQL Server Compressed Backup v1.2 Released

  1. Terry says:

    I really like the progress updates. Nice job.

    One thing – it seems I can’t take a backup I made on a 64 bit server and restore it on a 32 bit server, even though that’s possible through the standard SSMS interface. I get ‘device is incorrectly formatted’. Any thoughts?

  2. Clay Lenhart says:

    Terry,
    I suspect that you used compression when backing up, but didn’t include the compression plugin in the pipeline when restoring. If you post a comment with the backup command, I’ll reply with the restore command that you should use.

    Cheers,
    Clay

  3. -phil- says:

    Hi Clay,

    Thx for your excellent work !

    A little bug:

    C:\Program Files\MSSQLCompressedBackup-1.2>msbp.exe restore
    capacity was less than the current size.
    Parameter name: capacity

    System.ArgumentOutOfRangeException
    at System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
    at System.Collections.Generic.List`1..ctor(Int32 capacity)
    at MSBackupPipe.Cmd.Program.CopySubArgs(String[] args) in C:\Documents and Settings\clay.lenhart\My Documents\personal\Hg\mssql
    compressed\MSBackupPipe.Cmd\Program.cs:line 251
    at MSBackupPipe.Cmd.Program.Main(String[] args) in C:\Documents and Settings\clay.lenhart\My Documents\personal\Hg\mssqlcompres
    sed\MSBackupPipe.Cmd\Program.cs:line 136

    The restore 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

    C:\Program Files\MSSQLCompressedBackup-1.2>

  4. Clay Lenhart says:

    Thanks, the error message should be nicer. I’ll add a issue for this.

    The problem is that the command is incomplete. It should be something like

    msbp.exe restore file:///c:\model.bak db(database=model)

    or

    msbp.exe restore file:///c:\model.bak.zip zip64 db(database=model)

  5. -phil- says:

    Yep, just rests you to improve a little bit the error message hanlder :)

    Thx Clay
    ++

  6. Kim says:

    Hi Clay
    Is your command incompatible with sql2008
    It works great with our 2005
    /kim

  7. Clay Lenhart says:

    It works with sql2008. Can you post the error message? A common error is related to using the right x86 or x64 version of msbp (based on SQL Server not Windows. So SQL Server x86 on Windows x64 should use the x86 version of msbp)

  8. Kim says:

    Thx. Excactly I had 64bit msbp and os but only 32 bit sqlserver.
    Everything works fine now.
    /kim

  9. Clay Lenhart says:

    Kim,
    I’m glad it is working for you!

    The next todo item is to hide x86 vs x64 from users and have one EXE. This will also make a GUI easier since we can then target “any cpu” rather than a particular one.

  10. Don bell says:

    I’m currently using your tool to run full backups and all works ok, i started using it for log backups on SQL 2008 64 bit edition and that works fine, but when i tried using it on SQL 2000 32 bit edition, it gave this error :

    Exception #1
    EXECUTE cannot be used as a source when inserting into a table variable.

    Any idea what may cause this, thanks!!

  11. Clay Lenhart says:

    This sounds like a bug that has been fixed. Can you check to see if you are using the latest version, 1.2-20100117. You can download the latest version from the project page here https://sourceforge.net/projects/mssqlcompressed/

  12. Don bell says:

    Big thanks, i hadn’t realized you had released another version since 1.2, it works perfectly now!! Keep up the good work!

  13. Neil davies says:

    Firstly I’d like to say this is a fantastic little tool and keep up the good work. I do have some issue getting it to work with SQL2K8 on a 64bit Windows 2008 server. I seem to be getting a permission denied error on writing the file out. Probably a school boy error, but any thoughts would be appreciated. I’m running as administrator and can create files in the directory concerned.

    C:\MSCOMP>msbp.exe backup “db(database=model)” “zip64(level=1)” “local(path=c:\mscomp)”
    local:
    path=c:\mscomp
    Access to the path ‘c:\mscomp’ is denied.

    System.UnauthorizedAccessException
    at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
    at System.IO.FileInfo.Delete()
    at MSBackupPipe.StdPlugins.Storage.LocalStorage.CleanupOnAbort() in C:\Documents and Settings\clay.lenhart\My Documents\personal\Hg\mssqlcompressed\MSBackupPipe.StdPlugins\Storage\LocalStorage.cs:line 156
    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\personal\Hg\mssqlcompressed\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\personal\Hg\mssqlcompressed\MSBackupPipe.Common\BackupPipeSystem.cs:line 51
    at MSBackupPipe.Cmd.Program.Main(String[] args) in C:\Documents and Settings\clay.lenhart\My Documents\personal\Hg\mssqlcompressed\MSBackupPipe.Cmd\Program.cs:line 112

    The backup failed.

  14. Neil davies says:

    … Sorry ignore the above. School boy error 😉

  15. Don bell says:

    I’m getting this error while trying to restore a file, any idea?

    Restore started
    The file on device ‘fbcd7976-ea5b-4448-b46e-4
    ft Tape Format backup set.
    RESTORE DATABASE is terminating abnormally.

  16. botr says:

    how would one restore the database to another db server or another database name? I keep getting errors from the SQL Server.

    Is it possible to send parameters through the command line like WITH MOVE etc?

  17. Clay Lenhart says:

    Don,
    The likely issue is the file you are trying to restore is compressed and the command you’re using doesn’t include the compression plugin. You probably should restore with something like (the key being “gzip”, though you may need zip64 or bzip2 instead):
    msbp.exe restore path(local=c:\path\mydatabase.bak.gz) gzip db(database=mydatabase)

  18. Clay Lenhart says:

    botr,
    You’ll need to run msbp.exe on that server — it doesn’t work remotely.
    An example, (with MOVE), would be:
    msbp.exe restore “local(path=c:\path\mydatabase.bak.gz)” gzip “db(database=mydatabase;MOVE=’logicalname’TO’c:\path\mydatabase.mdf';MOVE=’logicalnameldf’TO’c:\path\mydatabase.ldf’)”

  19. Juan Font says:

    Great tool Clay! I have already integrated it into a maintenance plan. Have been using it for over a week to make daily (full) backups. Works like a charm!

    I am very grateful for this!

  20. Clay Lenhart says:

    Thanks! I’m glad it is useful.

  21. Manoj Sardana says:

    I am getting following error:-

    local:
    path=D:\sqlbackups\CMBS_1.bak
    ————————
    Exception #1
    VDS::Create failed: x80770005
    NULL
    System.InvalidProgramException
    at MSBackupPipe.VirtualBackupDevice.VirtualDeviceSet.CreateEx(String instanceName, String deviceSetName, VirtualDeviceSetConfig config) in c:\documents and settings\clay lenhart\my documents\mssqlcompressed\msbackuppipe.virtualbackupdevice\virtualdevic
    eset.cpp:line 93
    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 104
    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

    Please help.

  22. Gomes says:

    Hello
    Well I try the restore and I get this Error,
    VDS::GetConfiguration failed: x80770003
    Can anyone Help me.
    Regards

  23. Clay Lenhart says:

    Manoj,
    This web page, http://support.microsoft.com/kb/912414, suggests that if SQL Server is running under .\username, it throws this error. It suggests to run SQL Server under LocalSystem or a domain account.

  24. Clay Lenhart says:

    Gomes,
    0x80770003 means that msbp.exe waited for the BACKUP (or RESTORE) command to start, but timed out. It could mean the system is busy, or there is a reason why a RESTORE couldn’t start. What other errors did you see?

  25. Don Bell says:

    I’m having an issue when i run a Full DB backup, and the someone else does a Log backup, it hangs the backups, i tried running 2 separate msbp.exe copy and it still seemed to jam up, any idea what i’m doing wrong? Thanks!!

    Other than this issue your tool has been very useful, works wonders for us!!

    Don

  26. Clay Lenhart says:

    There isn’t any really special that msbp does. Do you get the same behavior with the native backup commands?
    Also, open the Activity Monitor to see what is blocking the BACKUP commands.

  27. Colin Foss says:

    Would it be possible to include a computed throughput once the backup has completed?

    e.g.
    Completed Successfully. 00:00:01.342342 12MB/sec

  28. Alexandra Mladenovic says:

    Hi Clay,
    when I try to run msbp.exe backup “db(database=LoanDbTest;instancename=yubelcre2;clusternetworkname=yucli0;backuptype=full)” “gzip(level=5)” “local(path=K:\BackupTest\LoanDbTest.bak.gz)” … I got error:

    Exception 1#
    A network-related or instance-specific error occured while establishing a connection to SQL server.The server was not found or was not accessible.Verify that the instance name is correct and SQL Server is configured to allow remote connection

    But, option Allow other SQL Servers to connect remotely to this SQL Server using RPC is enabled for my SQL instance, and also telnet yubelcre2 1433 works.

    Here we have cluster with Windows Server 2003 EE SP2, active/active, 2 SQL Server 2000 SP3 instance.

    Best regards

  29. Clay Lenhart says:

    Alexandra,
    It sounds like you are trying to backup remotely. Unfortunately you have to run it on a node in the cluster since it connects to a SQL Server DLL to receive the backup data.

    update: You have to run it on an active node in the cluster.

  30. Alexandra Mladenovic says:

    I am a DBA in a bank, my managers are unlikely to buy a complete backup solution, which itself has the ability to compression, such as LiteSpeed, because they want to save money without realizing the importance of backups. I came across your solution sounds perfect to me, so I sincerely hope I will be able to implement it on the system, actually it’s my last hope 

    As I said, we have active / active cluster, for each physical node is one instance of SQL Server. Yubelcre2, SQL instance for which I tried to do the database backup is on the physical node yucli2.

    MSCS Cluster: yucli0
    Physical node: yucli2
    Virtual Server (SQL instance): yubelcre2

    I am not trying to backup remotely, but directly from the active node where is the SQL instance, with cmd:

    C: \ MSSQLCompressedBackup-1.2-20100527-x86> msbp.exe backup db (database = LoanDbTest; INSTANCENAME = yubelcre2; clusternetworkname = yucli0; backuptype = full) “” gzip (level = 5) “,” local (path = K: \ BackupTest \ LoanDbTest.bak.gz)

    Yubelcre2 the cluster group that is active in the physical yucli2 Noda, and K is a disc that is attached to the group.

    If you have any idea yet what would eventually be able to check …

    Advance grateful,
    Alexandra

  31. Clay Lenhart says:

    Alexandra,
    Based on the information provided, msbp connects to yucli0\yubelcre2. Can you first check that SQL Management Studio can connect to this from yucli2. If this doesn’t work,could you send me the stack trace by writing the output to a text file:
    C: \ MSSQLCompressedBackup-1.2-20100527-x86> msbp.exe backup db (database = LoanDbTest; INSTANCENAME = yubelcre2; clusternetworkname = yucli0; backuptype = full) “” gzip (level = 5) “,” local (path = K: \ BackupTest \ LoanDbTest.bak.gz) > msbp_log.txt

    Thanks,
    Clay

  32. Alexandra Mladenovic says:

    Dear Clay,

    I can connect to yubelcre2 instance (not yucli0\yubelcre2) from Management Studio or Enterprise Manager, on node yucli2.

    The output of msbp_out text file:

    Plugin not found: LoanDbTest.bak.gz)

    System.ArgumentException
    at MSBackupPipe.Cmd.ConfigUtil.ParseComponentConfig(Dictionary`2 pipelineComponents, String componentString) in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed\MSBackupPipe.Cmd\ConfigUtil.cs:line 68
    at MSBackupPipe.Cmd.Program.ParseBackupOrRestoreArgs(List`1 args, Boolean isBackup, Dictionary`2 pipelineComponents, Dictionary`2 databaseComponents, Dictionary`2 storageComponents, ConfigPair& databaseConfig, ConfigPair& storageConfig) in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed\MSBackupPipe.Cmd\Program.cs:line 309
    at MSBackupPipe.Cmd.Program.Main(String[] args) in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed\MSBackupPipe.Cmd\Program.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

    Thank you very much for help

    Alexandra

  33. Clay Lenhart says:

    Alexandra,
    This is a completely different error this time, but I think I’ve spotted the issue anyways.

    If you are connecting to “yubelcre2″ and not “yucli0\yubelcre2″ then I’d change this:
    db(database=LoanDbTest;INSTANCENAME=yubelcre2;clusternetworkname=yucli0;backuptype=full)
    to
    db(database=LoanDbTest;clusternetworkname=yubelcre2;backuptype=full)

    I think when you refer to “instance” you think “cluster instance”, but in msbp, “instance” means “SQL Instance” and has nothing to do with clustering. I suspect this is the source of the problem. I appologize that I didn’t pick up on this before.

    Try the following command and let me know how it goes.

    msbp.exe backup “db(database=LoanDbTest;clusternetworkname=yubelcre2;backuptype=full)” “gzip(level=5)” “local(path=K:\BackupTest\LoanDbTest.bak.gz)”

    Cheers,
    Clay

  34. Alexandra Mladenovic says:

    Dear Clay,

    You’re a genius! Backup works, quite correctly. From your experience and the experience of others, which is the most viable option ie. which is the best plugin to use and what level?

    I am now testing the backup database of 5GB, to a remote location (a shared folder on another server) using gzip and level = 7. Backup file size is 500 MB and the backup takes about 7 minutes.

    I then tried restore:
    msbp.exe restore “local(path=\\yucli1\U$\SQL_Backup\LoanDbTest1.bak.gz)” gzip “db(database=SaskaTest1;clusternetworkname=yubelcre2;MOVE=’bankinfodb_Data’TO’K:\LoanDBTest\bankinfodb_Data1.mdf';MOVE=’bankinfodb_Log’TO’K:\LoanDBTest\bankinfodb_Log1.ldf’)” > msbp_log_restore5.txt

    Unfortunately, the restore failed, the log states:

    local:
    path=\\yucli1\U$\SQL_Backup\LoanDbTest1.bak.gz
    gzip
    Restore has started
    0.00% Complete. Estimated End: 3/10/2011 4:27:24 PM
    31.35% Complete. Estimated End: 12/23/2010 2:52:31 PM
    74.95% Complete. Estimated End: 12/23/2010 2:48:51 PM
    ————————
    Exception #1
    RESTORE DATABASE is terminating abnormally.

    System.Data.SqlClient.SqlException
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.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 490

    ————————
    Exception #2
    Unexpected EOF

    ICSharpCode.SharpZipLib.SharpZipBaseException
    at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.Fill()
    at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputStream.Read(Byte[] buffer, Int32 offset, Int32 count)
    at ICSharpCode.SharpZipLib.GZip.GZipInputStream.Read(Byte[] buffer, Int32 offset, Int32 count)
    at MSBackupPipe.VirtualBackupDevice.CommandBuffer.ReadFromStream(Stream source) in c:\documents and settings\clay lenhart\my documents\mssqlcompressed\msbackuppipe.virtualbackupdevice\commandbuffer.cpp:line 97
    at MSBackupPipe.Common.DeviceThread.ReadWriteData(VirtualDevice device, CommandBuffer buff, Stream stream, Boolean isBackup) in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed\MSBackupPipe.Common\DeviceThread.cs:line 128
    at MSBackupPipe.Common.DeviceThread.ThreadStart() in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed\MSBackupPipe.Common\DeviceThread.cs:line 86

    The restore 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

    Tnx in advance.

    Alexandra

  35. Alexandra Mladenovic says:

    Dear Clay,

    I try to restore various versions, from different locations, but we do not succeed.

    For example, when I switch back up to the same disk in which I want to develop backup, let me know the error:

    msbp.exe restore “local(path=K:\BackupTest\LoanDbTest1.bak.gz)” gzip “db(database=SaskaTest;clusternetworkname=yubelcre2;MOVE=’bankinfodb_Data’TO’K:\LoanDBTest\bankinfodb_Data1.mdf';MOVE=’bankinfodb_Log’TO’K:\LoanDBTest\bankinfodb_Log1.ldf’)” > msbp_log_restore_241210.txt

    local:
    path=K:\BackupTest\LoanDbTest1.bak.gz
    gzip
    Restore has started
    0.00% Complete. Estimated End: 3/11/2011 10:31:06 PM
    60.86% Complete. Estimated End: 12/24/2010 8:56:02 AM
    ————————
    Exception #1
    Read on ‘e9142cf8-2da0-4ef2-9e0f-859eb6e53ede’ failed, status = 112. See the SQL Server error log for more details.
    RESTORE DATABASE is terminating abnormally.

    System.Data.SqlClient.SqlException
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.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 490

    ————————
    Exception #2
    Unexpected EOF

    ICSharpCode.SharpZipLib.SharpZipBaseException
    at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.Fill()
    at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputStream.Read(Byte[] buffer, Int32 offset, Int32 count)
    at ICSharpCode.SharpZipLib.GZip.GZipInputStream.Read(Byte[] buffer, Int32 offset, Int32 count)
    at MSBackupPipe.VirtualBackupDevice.CommandBuffer.ReadFromStream(Stream source) in c:\documents and settings\clay lenhart\my documents\mssqlcompressed\msbackuppipe.virtualbackupdevice\commandbuffer.cpp:line 97
    at MSBackupPipe.Common.DeviceThread.ReadWriteData(VirtualDevice device, CommandBuffer buff, Stream stream, Boolean isBackup) in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed\MSBackupPipe.Common\DeviceThread.cs:line 128
    at MSBackupPipe.Common.DeviceThread.ThreadStart() in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed\MSBackupPipe.Common\DeviceThread.cs:line 86

    The restore 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

    In the Windows application log I got:

    Event Type: Error
    Event Source: MSSQLSERVER
    Event Category: (2)
    Event ID: 17055
    Date: 12/24/2010
    Time: 8:54:52 AM
    User: INFIYU\administrator
    Computer: YUBELCRE2
    Description:
    18210 :
    BackupMedium::ReportIoError: read failure on backup device ‘e9142cf8-2da0-4ef2-9e0f-859eb6e53ede’. Operating system error 112(There is not enough space on the disk.).

    The disc has nearly 130 GB of empty space so it’s no problem. Investigate the cause of errors.
    If you have a smart idea …

    Best regards,
    Alexandra

  36. Clay Lenhart says:

    First, I’d look into whether the problem is reading or writing by using something like 7zip to test the archive.

    A user did a number of tests with msbp, and found that zip64 gave the best performance for the compression acheived. This may be due to the implementation of SharpZipLib rather than anything inherient in the compression algorithms.

    update: I’d uncompress the archive to a *.bak file and use the standard SQL Server tools to restore it.

  37. Alexandra Mladenovic says:

    Decompression in the standard bak file still remains as an option, but due to problems with space on storage, I would like to be able to do restore from the compressed file :)

  38. Clay Lenhart says:

    I agree. I’d like to know if the file was stored correctly, or if there is a problem saving the file. I was thinking doing this once to see what might be the problem.

    I hope you have a Merry Christmas!

  39. Fred says:

    Thanks, your tool is awesome!

    For those who are too lazy:


    DECLARE cDb CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM SYS.DATABASES WHERE name != 'tempdb'
    DECLARE @sDbName NVARCHAR(50)
    OPEN cDb
    FETCH NEXT FROM cDb INTO @sDbName
    WHILE @@FETCH_STATUS = 0 BEGIN
    EXECUTE xp_cmdshell 'D:\Path\To\msbp.exe backup "db(database='+@sDbName+')" "gzip()" "local(path=D:\Path\To\Backup\'+@sDbName+'.bak.gz)"'
    FETCH NEXT FROM cDb INTO @sDbName
    END
    CLOSE cDb
    DEALLOCATE cDb

  40. Freddy says:

    i very unfamiliar with this tool. Could you try to tell me what I am doing wrong?
    C:\Documents and Settings\Administrator\Desktop\MSSQLCompressedBackup-1.2-201005
    27\MSSQLCompressedBackup-1.2-20100527_x86>msbp.exe backup “db(database=EMS_Backu
    p_T1.bak)” “gzip” “local(path=C:\Backup\EMS_Backup_T1.bak.)”
    Could not create an instance: CLSID_MSSQL_ClientVirtualDeviceSet, x80040154

    System.InvalidProgramException
    at MSBackupPipe.VirtualBackupDevice.VirtualDeviceSet..ctor() in c:\documents
    and settings\clay lenhart\my documents\mssqlcompressed\msbackuppipe.virtualbacku
    pdevice\virtualdeviceset.cpp:line 50
    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 179
    at MSBackupPipe.Common.BackupPipeSystem.Backup(ConfigPair databaseConfig, Lis
    t`1 pipelineConfig, ConfigPair storageConfig, IUpdateNotification updateNotifier
    ) in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed\MSBacku
    pPipe.Common\BackupPipeSystem.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.

  41. IT says:

    Hi Clay,
    I found your msbp very useful but, as I dont like typing in command pormpt much, I developed a freeware Windows application as UI for your tool. Can you give me some email so I can send it to you to see it and comment? Maybe it could go along with yours.

  42. Clay Lenhart says:

    IT,
    Post the code on Source Forge or any other site and I’ll take a look.

  43. Michel Ridguay says:

    Hi Clay!

    Nice application you developed here, it´s very usefully to me at this moment.
    But I have one question, how could I do to have and FTP plugging instead of the only local for the storage purpose

    Regards!

  44. Clay Lenhart says:

    Michel,

    This is something that I’ve always thought people would want, but you’re the first to ask for it. I’m busy at the moment with work, but I think I could start on this in a couple of months. I’ll see how things are then.

    -Clay

  45. Marek says:

    Hello Clay

    I have one question. How can I connect to to SQL 2005 name instance ? I try:
    C:\MSSQLCompressedBackup>msbp.exe backup “db(database=test;instancename=MSSQLC64
    \SQL64;backuptype=full)” “gzip(level=5)” “local(path=C:\test.bak.gz)”
    and :
    Invalid pipeline. The closing parenthesis not found: “db(database=test;instance
    name=MSSQLC64\SQL64;backuptype=full)”

    System.ArgumentException
    at MSBackupPipe.Cmd.ConfigUtil.ParseComponentConfig(Dictionary`2 pipelineComp
    onents, String componentString) in C:\Documents and Settings\Clay Lenhart\My Doc
    uments\mssqlcompressed\MSBackupPipe.Cmd\ConfigUtil.cs:line 68
    at MSBackupPipe.Cmd.Program.ParseBackupOrRestoreArgs(List`1 args, Boolean isB
    ackup, Dictionary`2 pipelineComponents, Dictionary`2 databaseComponents, Diction
    ary`2 storageComponents, ConfigPair& databaseConfig, ConfigPair& storageConfig)
    in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed\MSBackupP
    ipe.Cmd\Program.cs:line 293
    at MSBackupPipe.Cmd.Program.Main(String[] args) in C:\Documents and Settings\
    Clay Lenhart\My Documents\mssqlcompressed\MSBackupPipe.Cmd\Program.cs:line 106
    I try to use version 1.2.xxx

    Help please …

    Regards
    Marek

  46. Clay Lenhart says:

    Marek,

    The instance name parameter should be: instancename=SQL64. It is not necessary to include the server name because it has to run locally on the server.

  47. cesare says:


    Hi Clay, we have been using your software to backup our sqlserver dbs since last week with no problem.
    Now on a 32bit windows 2008 web edition it seems it doesn’t want to work anymore while on another similar machine (but 64 bit) it still working.

    I’ve got this error:
    ————————————
    C:\Users\Administrator\Desktop\BackupScript\MSSQL\MSSQLCompressedBackup-1.2-2010
    0527_x86>msbp.exe backup “db(database=xxxxxx)” “zip64(level=3)” “local(path=c:\te
    stbackup.bak.zip)”
    Could not create an instance: CLSID_MSSQL_ClientVirtualDeviceSet, x80040154

    System.InvalidProgramException
    at MSBackupPipe.VirtualBackupDevice.VirtualDeviceSet..ctor() in c:\documents
    and settings\clay lenhart\my documents\mssqlcompressed\msbackuppipe.virtualbacku
    pdevice\virtualdeviceset.cpp:line 50
    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 179
    at MSBackupPipe.Common.BackupPipeSystem.Backup(ConfigPair databaseConfig, Lis
    t`1 pipelineConfig, ConfigPair storageConfig, IUpdateNotification updateNotifier
    ) in C:\Documents and Settings\Clay Lenhart\My Documents\mssqlcompressed\MSBacku
    pPipe.Common\BackupPipeSystem.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.
    ———————————

    Can you help? I’ve tried to google around to find what was the problem but with no luck.
    What I see is that it referrers to some of your local file…
    The strange thing is that it has been working for a quite long time…

    Thanks if you can help.
    Cesare

  48. Clay Lenhart says:

    Cesare, This error means that msbp.exe cannot load a DLL installed by SQL Server. I’d first make sure SQL Server is installed correctly.
    The path varies, but the way I have SQL Server installed on my machine msbp.exe loads C:\Program Files\Microsoft SQL Server\80\COM\sqlvdi.dll

  49. cesare says:

    Our sysadmin moved the db server on a new dedicated one and didn’t told me!

  50. Clay Lenhart says:

    Cesare, I’m glad you got to the bottom of it eventually!

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>