SQL Server 2005 Compressed Backup

I’ve been working on a way to backup SQL Server 2005 databases to a compressed file recently, but without using temporary files. I recently found that it is actually pretty easy. SQL Server has an interface for creating a virtual backup device so that the data is pumped to your application, not a real device. Once the application has the data, it can compress it, encrypt it, or do whatever it likes. Maybe DBAs would like to save the data to an FTP server. There are so many options.

The application I wrote is called MSQL Compressed Backup which you can find on Sourceforge. It is currently in beta, and I don’t plan to release a 1.0 version for awhile until it has had some testing by other people. If you are interested in it, please download a copy and give it a try.

Documentation is currently lacking, but here are a couple of examples. I think they are self explanatory.

  • msbp.exe backup [model] gzip file:///c:\model.bak.gz
  • msbp.exe restore file:///c:\model.bak.gz gzip [model]
    Here, gzip knows to uncompress since it is in “restore” mode.
  • msbp.exe backup [model] bzip2(level=5) file:///c:\model.bak.bz2
    Each plugin in the pipeline can have parameters.
  • msbp.exe backup [model] rate(ratemb=5.0) bzip2(level=5) file:///c:\model.bak.bz2
    You can have any number of plugins in the pipeline. The rate plugin limits the impact of a backup on your server by restricting the speed of the backup (or restore).

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.

89 Responses to SQL Server 2005 Compressed Backup

  1. Patrik says:

    This is great! I have looked for a way of fixing this for ages.

    Just one question. By default you connect to the default instance at the local server. Is there a way to connect to another instance?

  2. Clay Lenhart says:

    There is. You’ll have to use the long way (the “db” source) which has more options:

    This page documents all the options:
    http://mssqlcompressed.sourceforge.net/doc_plugins_db.shtml

    An example that connects to the sqlexpress instance is:

    msbp.exe backup “db(database=model;instancename=sqlexpress)” “local(path=c:\model.bak)”

  3. alexey says:

    Idea is very good but low speed due to using pipes (even sometimes less than in batch with WinZip) makes it unpractical. Good example of SQL VDI usage.

  4. Clay Lenhart says:

    Alexey,

    Thanks for your feedback.

    In tests that I have done with a commercial product found that msbp has comparable speed and compression level using the gzip plugin when the commercial product was set to single threaded (to compare apples to apples*). The zip64 and bzip2 plugins result in smaller file sizes at the expense of slower execution times. If speed is your main concern, then the gzip plugin is the one you want to use.

    I’m surprised that you found batch mode Winzip to be faster, since this will have much more disk IO than msbp due to first writing the uncompressed backup to disk and then compressing it. I’d be interested in knowing more about this.

    My gut feeling is that if msbp is slower, it is not due to the pipe architecture, but something else. I’d first suspect that the compression component msbp uses which may not be as optimized as Winzip, but I would want to know about the version of Winzip used, the msbp options used, and the compression levels you were able to achieve with both.

    Thanks,
    Clay

    * The next version of msbp (v1.1) will have a multithreaded option.

  5. stephane says:

    Hi,
    Great tool ! But, how to restore a multithreaded backup ?

    I have tried that :

    msbp restore “local(path=F:\toto1.bak.gz;path=F:\toto2.bak.gz)” “gzip()” “db(database=toto)”

    And it doesn’t run. It returned me :

    Security=SSPI;Asynchronous Processing=true;local: path=F:\toto1.bak.gz path=F:\toto2.bak.gz gzip gzip Restore startedThe tail of the log for the database “toto” has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log. RESTORE DATABASE is terminating abnormally. System.Data.SqlClient.SqlException à System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) à System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) à System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) à System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,…

    Thanks

  6. Clay Lenhart says:

    I’m glad you like it.

    This error is from SQL Server, and not really specific to msbp. SQL Server doesn’t want to overwrite an existing database accidentally.

    This link has more information: http://msdn.microsoft.com/en-us/library/ms179314.aspx

    Assuming it is OK to overwrite the existing database, I would add the “replace” option:

    msbp restore “local(path=F:\toto1.bak.gz;path=F:\toto2.bak.gz)” “gzip()” “db(database=toto;REPLACE;)”

  7. Miha says:

    Hi, great tool! But… When I try to make a restore with:
    msbp.exe restore file:///d:\MEDIS_0.zip zip64 [MEDIS]

    i get an error:

    Connecting: Data Source=.;Initial Catalog=master;Integrated Security=SSPI;Asynch ronous Processing=true;
    Object reference not set to an instance of an object.
    System.NullReferenceException
    at MSSQLBackupPipe.StdPlugins.Storage.LocalStorage.GetRestoreReader(String config) in C:\Documents and Settings\clay.lenhart\My Documents\personal\svn\MSSQLComp\mssqlcompressed\trunk\MSSQLBackupPipe.StdPlugins\Storage\LocalStorage.cs:line 103
    at MSSQLBackupPipe.Program.BackupOrRestore(Boolean isBackup, ConfigPair storageConfig, ConfigPair databaseConfig, List`1 pipelineConfig) in C:\Documents andSettings\clay.lenhart\My Documents\personal\svn\MSSQLComp\mssqlcompressed\trunk\MSSQLBackupPipe\Program.cs:line 213

    The restore failed.

    Can you please suggest what the problem could be?

    Thanks, Miha

  8. Clay Lenhart says:

    I remember a similar bug in version 20090310. Can you try the latest version (20090408)?

  9. Miha says:

    Hi Clay, now it work perfectly. I must have been downloaded beta version by mistake.

    Thank you very much!

  10. Colin says:

    Yes, very interesting tool but I agree with Alexey in that the pipe option is too slow for large databases.

    The big advantage I see with your tool is portability. i.e. I dont have to purchase another license to restore to a different location.

    I compared a commercial product we have in house with your tool. The commercial product is about 4 times faster but produced compressed files that are about 20% larger. I used gzip level 1 compression for the test and used both single and six threads for comparison.

    The number of threads had nearly no impact on the final run duration but that is probably due to gzip(level=1).

  11. Clay Lenhart says:

    A user gather statistics on the performance of the different options. They found zip64(level=1) to be the fastest: Compression Statistics

    If you didn’t see any performance improvement when trying multiple threads, then it could be due to either having a singe core machine or incorrectly specifying threading (which honestly isn’t obvious). To enable multiple threads, you specify multiple destinations files, for instance
    msbp.exe backup “db(database=model)” “zip64(Level=1)” “local(path=c:\file1.bak.zip;path=c:\file2.bak.zip)”

  12. Colin says:

    I ran the tests on 8 core servers with 16GB of RAM. I did specify the multiple threads similar to your example, except using six output files.

    I will try zip64, since from the stats you linked to indicate a ~50% duration reduction.

  13. Clay Lenhart says:

    I looked into it, and the updated Sourceforge site has picked the very first release as the default file to download! I bet you are using the 0.9 beta version. The 1.1 version is the first version to support multithreaded compression. In particular version 1.1-20090408 is the best version to use.

  14. Colin says:

    The zip64 plugin gave much better results compared to gzip in terms of run duration. I was using the 1.1 version.

    If I may make a suggestion, please work with Ola Hallengren to integrate your msbp tool into the DatabaseBackup procedure as a third backup software option. http://ola.hallengren.com/Documentation.html

  15. Terry says:

    When running any command, for example: msbp.exe backup “db(database=mydb)” “gzip(level=9)” “local(path=c:\mydb.bak.gz”)

    I’m gettin this:

    Connecting: Data Source=.;Initial Catalog=master;Integrated Security=SSPI;Asynchronous Processing=true;
    Illegal characters in path.
    System.ArgumentException
    at System.IO.Path.CheckInvalidPathChars(String path)
    at System.IO.Path.NormalizePathFast(String path, Boolean fullCheck)
    at System.IO.Path.NormalizePath(String path, Boolean fullCheck)
    at System.IO.Path.GetFullPathInternal(String path)
    at System.IO.FileInfo..ctor(String fileName)
    at MSSQLBackupPipe.StdPlugins.Storage.LocalStorage.b__0(String path) in C:\Documents and Settings\clay.lenhart\My Documents\personal\Hg\mssqlcompressed-stable\MSSQLBackupPipe.StdPlugins\Storage\LocalStorage.cs:line 70
    at System.Collections.Generic.List`1.ConvertAll[TOutput](Converter`2 converter)
    at MSSQLBackupPipe.StdPlugins.Storage.LocalStorage.GetBackupWriter(String config) in C:\Documents and Settings\clay.lenhart\My Documents\personal\Hg\mssqlcompressed-stable\MSSQLBackupPipe.StdPlugins\Storage\LocalStorage.cs:line 68
    at MSSQLBackupPipe.Program.BackupOrRestore(Boolean isBackup, ConfigPair storageConfig, ConfigPair databaseConfig, List`1 pipelineConfig) in C:\Documents and Settings\clay.lenhart\My Documents\personal\Hg\mssqlcompressed-stable\MSSQLBackupPipe\Program.cs:line 213

    The backup failed.

  16. Clay Lenhart says:

    One thing I see is that the last parenthesis is switch with the double quote. Ie it should end with )”

  17. Clay Lenhart says:

    Backup integration with 3rd party tools would be good, but I’d like to work on a GUI first.

  18. Colin says:

    Do you have any plans for a 64bit version?

    On a Windows server 2003, standard x64 edition I get the following error:

    C:\util\MSSQLCompressedBackup>msbp.exe help

    Unhandled Exception: System.BadImageFormatException: Could not load file or assembly ‘VirtualBackupDevice, Version=1.0.3363.35718, Culture=neutral, PublicKeyToken=d210a28332c6e3f1′ or one of its dependencies. An attempt was made to load a p
    rogram with an incorrect format.
    File name: ‘VirtualBackupDevice, Version=1.0.3363.35718, Culture=neutral, Public
    KeyToken=d210a28332c6e3f1′
    at MSSQLBackupPipe.Program.Main(String[] args)

    WRN: Assembly binding logging is turned OFF.
    To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
    Note: There is some performance penalty associated with assembly bind failure logging.
    To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

  19. Colin says:

    please ignore my last comment.

  20. Adrian says:

    How can I move files when restoring a database on a different server?

    sorry, my english is limmited.

  21. Colin says:

    Is it possible to specify the BLOCKSIZE or BUFFERCOUNT parameters to the backup command?

  22. Clay Lenhart says:

    Adrian, There is a move option to restore the mdf and ldf files to a different location. The documentation is here, http://mssqlcompressed.sourceforge.net/doc_plugins_db.shtml, and includes an example with the move option.

  23. Clay Lenhart says:

    Colin, I thought I included all the backup options, but it looks like I missed these. I’ve added a bug issue to track this: http://sourceforge.net/tracker/?func=detail&aid=2866861&group_id=229242&atid=1076258

  24. Richard Svatos says:

    When the backup path contains “\s” (only lowercase), it is replaced by a semicolon (“;”). Uppercase “\S” works fine
    Is this behavior intentional?
    Are there other special characters like this?

  25. Clay Lenhart says:

    All the encoded characters are:
    \s for ‘;’
    \p for ‘|’ (left over from a early command line design)
    \\ for ‘\’
    In the code, you can find them in the ConfigUtil class in the MSSQLBackupPipe.StdPlugins namespace.

    And they are case-sensitive.

    I want to change these since the encoded characters (eg \s) are commonly used in paths. Should I use the SQL LIKE encoding — for instance [;]? Or change the encoding character, for example #s for semicolon?

    I’m currently thinking that parameters that are paths shouldn’t be encoded — it’s pretty rare to have semicolons and pipes in paths — but keep the current scheme for all other parameters.

  26. Richard Svatos says:

    Thanks! Tough call about how to best handle these. Off hand, not encoding paths sounds least problematic [and easiest to document] to me.
    I worked around this with a DOS hack to upper-case “\s” in the path (`set P=%P:\s=\S%`) in a .bat wrapper around msbp. And now I’ve added`set P=%P:\p=\P%`, too.
    Very nice utility! Thanks again!

  27. Clay Lenhart says:

    I’m getting ready to release version 1.2. The changes are (or will be):

    I’ve 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.

    I’ve 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.

    I’m working on the BlockSize and BufferCount request now. 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 I’m doing those.

  28. Sherbaz Mohamed says:

    Hi Lenhart,
    This is an amazing tool you have built. I really appreciate you for this innovation.

    Your portable software helped me compress huge backups from a server which does not have litespeed software installed. Thank you very much.

    But I dont know how to do differential and log backups using your tool.

    Best regards
    Sherbaz

  29. Sherbaz Mohamed says:

    Also I would like to know how to restore a database with norecovery option.

  30. Clay Lenhart says:

    Hi Sherbaz, Thanks! I’m glad it helped.
    Log backups can be done like:
    msbp.exe backup “db(database=model;backuptype=log)” “zip64″ “local(path=c:\model.bak.zip)”
    Differential backups can be done by changing backuptype=log to backuptype=differential.
    Restoring with norecovery can be done like:
    msbp.exe restore “local(path=c:\model.bak)” “db(database=model;NORECOVERY;MOVE=’modeldev’TO’c:\model.mdf';MOVE=’modellog’TO’c:\model.ldf';)”

  31. Sherbaz Mohamed says:

    Thank you very much once again… You are a genius…

  32. Sherbaz Mohamed says:

    I got below error when I tried to do differential backup

    E:\Compress>msbp.exe backup “db(database=pubs;backuptype=differential;instancena
    me=TEST)” “zip64(level=5)” “local(path=D:\multi\pubs_diff.bak.zip)”
    ————————
    Exception #1
    Invalid column name ‘field’.
    Invalid column name ‘field’.

    System.Data.SqlClient.SqlException
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea
    n breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj
    ect stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm
    dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds
    ParserStateObject stateObj)
    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, Run
    Behavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBe
    havior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav
    ior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult
    result)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav
    ior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, S
    tring 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 Docume
    nts\personal\Hg\mssqlcompressed\MSBackupPipe.Common\SqlThread.cs:line 314
    at MSBackupPipe.Common.SqlThread.PreConnect(String clusterNetworkName, String
    instanceName, String deviceSetName, Int32 numDevices, IBackupDatabase dbCompone
    nt, Dictionary`2 dbConfig, Boolean isBackup, IUpdateNotification notifier, Int64
    & estimatedTotalBytes) in C:\Documents and Settings\clay.lenhart\My Documents\pe
    rsonal\Hg\mssqlcompressed\MSBackupPipe.Common\SqlThread.cs:line 67
    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\personal\Hg\mssqlcompressed\MSBackupPipe.Common\BackupPipeSystem.cs:line 95

    The backup failed.

  33. Clay Lenhart says:

    This occurs when calculating the backup size. I’m looking into this.

  34. Clay Lenhart says:

    Sherbaz, Is your system case-sensitive by any chance (What’s the collation for the master or msdb database?)

  35. Sherbaz Mohamed says:

    Yes Lenhart. Its case-sensitive. All databases are of collation SQL_Latin1_General_CP850_BIN.

  36. Sherbaz Mohamed says:

    One more problem. Your application fails to initialise when it is run on windows server 2003 64 bit Standard edition

  37. Clay Lenhart says:

    Sherbaz,
    Thanks for reporting these. I tried it on my setup (Windows 2003 64-bit standard), and it works. Can you confirm that you are using the x64 msbp.exe rather than the x86 one? The latest zip file contains both.

    I should have a new version to address the case-sensitive collation today, however I don’t have a case-sensitive server to test it on, so it may fail due to another case-sensitivity issue.

    Cheers,
    Clay

  38. Sherbaz Mohamed says:

    Hi Lenhart,

    I found the reason why I got the error when executing that in 64 bit servers. The reason was that the server didnt have .net framework installed. Sorry for complaining your tool. The only bug in your product is the case- insensitivity.

    If you dont have a case-sensitive server to test your new version, you can forward that to me. I shall test it in my servers and will update you about what happened.

    Thanks
    Sherbaz

  39. Joseph says:

    Hi Clay,

    First of all, it’s a very good job you do. Thanks a lot.

    I need to list the files contained in a compressed backup (to be able to use the move option on my 53 ndf files), but I can’t find out how.

    If it wasn’t compressed I’d type the query “RESTORE FILELISTONLY FROM MyPath” (cf: http://msdn.microsoft.com/en-us/library/ms173778.aspx) but the uncompress process is very long (almost 600Gb at the end) and I don’t have enough disk space.

    Is your tool able do that (I didn’t found out the option in the documentation)?

    Thanks in advance,
    Joseph

  40. Clay Lenhart says:

    Joseph,
    To answer your question, it doesn’t do FILELISTONLY currently.

    As a potential work around, if you attempt to restore to a drive that doesn’t exist or to a file used by a database, it will include the name in the error message. Maybe you could attempt to restore on a development machine to get the list of files (assuming the dev box doesn’t have the same drive letters).

    In general, it is extra effort to restore using this tool especially with 53 ndf files. I’d like to make it easier with a GUI. Also, from the command prompt there could be a command to suggest restore options (listing all files to give people a chance to change the destination paths).

  41. Joseph says:

    Hi Clay,

    I finally found a USB drive big enough to uncompress the backup file.
    This option would be a great improvement (even if your tool is already great a tool!).

    About the GUI : it would be great to have a GUI, as you say, but I’d rather have a GUI that generate the command line, not a GUI that launches the tool (for restoring or even backuping big backup files, I usually use the windows scheduled tasks, so a GUI wouldn’t fit with it! :( ).

    Happy new year in advance and keep going, you do a great job! :o)

    Cheers,
    Joseph

  42. Clay Lenhart says:

    I’m glad you got it working in the end.

    Scheduling restores is a scenario I didn’t think of. I’ll definitely keep that in mind.

  43. Larry Hawkes says:

    — =============================================
    — Author…..: Hope this saves you time. Thanks!
    — Create date: 2010-02-25
    — Description: Backup A Database To Target
    — Notes……: Unzip MSBP files to a directory
    — and then set the directory name
    — below. I call this from my backup
    — job to automate the compressed backups
    — =============================================
    Create PROCEDURE [dbo].[usp_BackupDBCompressed]
    @DBName varchar(2000),
    @DestPathFileName varchar(2000)
    AS
    BEGIN
    SET NOCOUNT ON;

    DECLARE @MSBPDir AS varchar(200)
    SET @MSBPDir = ‘D:\MSSQLCB_x86\msbp.exe ‘

    DECLARE @CMD AS varchar(2000)

    SET @CMD = @MSBPDir + ‘backup “db(database=’ +
    @DBName + ‘)” “zip64(level=3)” “local(path=’ +
    @DestPathFileName + ‘)”‘

    EXEC master..xp_cmdshell @CMD
    END

  44. Clay Lenhart says:

    Thanks Larry!

  45. Kimmo Akkanen says:

    Hi,

    Looks like a good tool for us as well, but is it possible to specify to backup all databases instead of specifying each one manually?

    Thanks,
    Kimmo

  46. Clay Lenhart says:

    Kimmo, this is probably the highest requested feature. Unfortunately I do not have time to work on this at the moment, so it’s going to be awhile. Also, I’m thinking that the best way to do this is to integrate with Powershell, but I don’t yet know how to code against Powershell.

  47. Pablo says:

    Hi, I have downloaded the source code, but the compilation fails because I don´t have FXCOP installed and I can´t find how to “disable” it

    Thanks,

  48. Clay Lenhart says:

    Pablo, I’m glad you want to look at the code. The csproj files will include the following line “<RunCodeAnalysis>true</RunCodeAnalysis>”. Just delete the line (or set it to false) to remove this feature.
    I’d like to get the project to the point where people can use regular Visual Studio or even Visual Studio Express to contribute (or even to just look around in the code). The C++ code is probably the biggest issue preventing this at the moment. I’d also like to upgrade to 2008 or even 2010, but again there are some C++ issues with this.

  49. Pablo says:

    I manually deleted all the

    sections on the “MSBackupPipe.VirtualBackupDevice.vcproj” file and I was able to compile.

    There may not be a workaround about the c++ code, anyway I need more time to play with the project.

    thanks for the answer,
    Pablo

  50. Pablo says:

    Looks like I posted some unsafe xml/html , in other words:
    I deleted the “fxcop tools” related tags on the xml project file.

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>