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).
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?
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)”
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.
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.
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
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;)”
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
I remember a similar bug in version 20090310. Can you try the latest version (20090408)?
Hi Clay, now it work perfectly. I must have been downloaded beta version by mistake.
Thank you very much!
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).
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)”
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.
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.
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
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.
One thing I see is that the last parenthesis is switch with the double quote. Ie it should end with )”
Backup integration with 3rd party tools would be good, but I’d like to work on a GUI first.
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].
please ignore my last comment.
How can I move files when restoring a database on a different server?
sorry, my english is limmited.
Is it possible to specify the BLOCKSIZE or BUFFERCOUNT parameters to the backup command?
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.
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
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?
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.
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!
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.
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
Also I would like to know how to restore a database with norecovery option.
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';)”
Thank you very much once again… You are a genius…
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.
This occurs when calculating the backup size. I’m looking into this.
Sherbaz, Is your system case-sensitive by any chance (What’s the collation for the master or msdb database?)
Yes Lenhart. Its case-sensitive. All databases are of collation SQL_Latin1_General_CP850_BIN.
One more problem. Your application fails to initialise when it is run on windows server 2003 64 bit Standard edition
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
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
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
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).
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
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.
— =============================================
— 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
Thanks Larry!
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
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.
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,
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.
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
Looks like I posted some unsafe xml/html , in other words:
I deleted the “fxcop tools” related tags on the xml project file.