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.
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.
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
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.
*2440 is the only database that fails to backup on those days.
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.
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.
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.
Saud, no problem! Let me know how it goes.
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
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.
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
Mirco, I haven’t tried it this way, but makes sense! Thanks for the tip.
Saud, Great to hear! Thanks for the update.
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.
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.
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.
Royner,
That probably applies to your situtation too (running the tool with admin privledges).
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 ?
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