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.
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?
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
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>
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)
Yep, just rests you to improve a little bit the error message hanlder
Thx Clay
++
Hi Clay
Is your command incompatible with sql2008
It works great with our 2005
/kim
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)
Thx. Excactly I had 64bit msbp and os but only 32 bit sqlserver.
Everything works fine now.
/kim
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.
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!!
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/
Big thanks, i hadn’t realized you had released another version since 1.2, it works perfectly now!! Keep up the good work!
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.
… Sorry ignore the above. School boy error
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.
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?
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)
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’)”
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!
Thanks! I’m glad it is useful.
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.
Hello
Well I try the restore and I get this Error,
VDS::GetConfiguration failed: x80770003
Can anyone Help me.
Regards
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.
Gomes,
0×80770003 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?
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
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.
Would it be possible to include a computed throughput once the backup has completed?
e.g.
Completed Successfully. 00:00:01.342342 12MB/sec