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
Clay! This is great!!! I´m waiting more updates!
Great tool, although a bit depreciated now, as native backup compression is possible in almost all editions of SQL 2008 R2.
Still, I find it very helpful when migrating and transferring all these huge databases from SQL 2005 to more recent hosts and when dealing with some legacy systems.
Thanks for you work on this!
Hi Clay,
Im trying to get the basic exe to just do a simple backup and I’m getting an error. I’m just attempting a simple backup: msbp.exe backup “db(database=model)” “local(path=E:\SQLBackup\model.bak)” msbp.exe backup “db(database=model)” “local(path=E:\SQLBackup\model.bak)”
Could not load file or assembly ‘VdiNet.VirtualBackupDevice32, Version=1.0.3970.37477, Culture=neutral, PublicKeyToken=b2100563907f98e7′ or one of its dependencies. This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem. (Exception from HRESULT: 0x800736B1)
System.IO.FileLoadException
at VdiNet.VirtualBackupDevice.Exe32Sql32.VirtualDeviceSetExe32Sql32..ctor() at VdiNet.VirtualBackupDevice.VirtualBackupDeviceFactory.NewVirtualDeviceSet(
SqlPlatform sqlP) in C:\Users\Clay\Documents\vdi.net\VdiNet.VirtualBackupDevice\VirtualBackupDeviceFactory.cs:line 18
at MSBackupPipe.Common.BackupPipeSystem.BackupOrRestore(Boolean isBackup, ConfigPair storageConfig, ConfigPair databaseConfig, List`1 pipelineConfig, IUpdateNotification updateNotifier) in C:\Users\Clay\Documents\MSSQL Compressed\mssql sourceforge2\MSBackupPipe.Common\BackupPipeSystem.cs:line 183
at MSBackupPipe.Common.BackupPipeSystem.Backup(ConfigPair databaseConfig, List`1 pipelineConfig, ConfigPair storageConfig, IUpdateNotification updateNotifier
) in C:\Users\Clay\Documents\MSSQL Compressed\mssql sourceforge2\MSBackupPipe.Common\BackupPipeSystem.cs:line 51
at MSBackupPipe.Cmd.Program.Main(String[] args) in C:\Users\Clay\Documents\MSSQL Compressed\mssql sourceforge2\MSBackupPipe.Cmd\Program.cs:line 110
This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem. (Exception from HRESULT: 0x800736B1)
System.Runtime.InteropServices.COMException
This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem. (Exception from HRESULT: 0x800736B1)
The backup failed.
It seems the message above has been truncated in some way. I’ll just add that the SQLvdi.dll is in the place where SQL Server 2005 x86 dropped it at C:\Program Files\Microsoft SQL Server\80\COM, and that the OS im running is Win2K3 x86. I took the latest 1.2 zip file and unpacked it to the D:\drive. The database is the default MSSQLSERVER instance.
Steve R,
I suspect you’re trying to run this on a remote machine. It only works when it runs on the server with the databases.
-Clay
Hi Clay,
I’ve read the comments, and seen that people get this message when they try to run this on a remote machine but i’m running it locally, against one local database. Just the one default SQL Server 2005 Standard x86 installation. The model/msdb/master are on C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data and my other databases are at D:\SQLDATA\Data but they are all on the same machine.
It might be due to running a recent (or even a not so recent) version of Windows. You have to run it in Administrator mode, so right click on Command Prompt and select “Run as administrator” and then run the command above.
I’m running the cmd in administrator mode and connecting to a local database. still get the same error. ive redownloaded the latest exe also. I still get the following: D:\Tools\MSSQLCB> msbp.exe backup “db(database=model;backuptype=log;filegroup=primary;checksum)” “local(path=e:\model.bak)” with the result: Could not load file or assembly ‘VdiNet.VirtualBackupDevice32, Version=1.0.3970.37477, Culture=neutral, PublicKeyToken=b2100563907f98e7′ or one of its dependencies. This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem. (Exception from HRESULT: 0x800736B1)
Another option is that you’re running on a 64-bit version of Windows (even if SQL is 32-bit). There’s a 64-bit version of the msbp app that you should run in this case.
Hi Clay, good news:
The problem was with the Visual C++ 2008 redistributable. It wasnt able to load the DLL’s that you provide in the package. By removing the DLLs from the folder, and reinstalling the package from MS, it worked. Registering DLLs manually did not work, neither did putting the VC DLLs in the msbp folder. On Windows Server 2003, of course the 2008 redistributable isnt pre-installed. So my recommendation is to choose the vcredist_x86.exe (this page has the latest links: http://support.microsoft.com/kb/2019667).
After that I didnt even have to open the cmd as administrator, and all the options worked. Thanks for your help!
Steve
I’m having the same problem such as Clay. However I tried to install Visual C++ 2008 redistributable (both x64 and x86) and it’s still not working. I’m using Win7 x64 with MSSQL 2008 Express. Have you some ideas?
msbp.exe backup “db(database=Helios)” “gzip()” “local(path=C:\Backup\helios.bak.gz)”
local:
path=C:\Backup\helios.bak.gz
gzip: level = 9
————————
Exception #1
VDS::Create failed: x80770007
System.InvalidProgramException
v VdiNet.VirtualBackupDevice.Native.VirtualDeviceSet.CreateEx(String instance
Name, String deviceSetName, VirtualDeviceSetConfig config) v c:\users\clay\docum
ents\vdi.net\vdinet.virtualbackupdevice.native\virtualdeviceset.cpp:řádek 101
v VdiNet.VirtualBackupDevice.Exe64Sql64.VirtualDeviceSetExe64Sql64.CreateEx(S
tring instanceName, String deviceSetName, VirtualDeviceSetConfig config) v C:\Us
ers\Clay\Documents\vdi.net\VdiNet.VirtualBackupDevice\Exe64Sql64\VirtualDeviceSe
tExe64Sql64.cs:řádek 22
v MSBackupPipe.Common.BackupPipeSystem.BackupOrRestore(Boolean isBackup, Conf
igPair storageConfig, ConfigPair databaseConfig, List`1 pipelineConfig, IUpdateN
otification updateNotifier) v C:\Users\Clay\Documents\MSSQL Compressed\mssql sou
rceforge2\MSBackupPipe.Common\BackupPipeSystem.cs:řádek 109
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
C:\rdiff-backup-1.2.8>msbp.exe backup “db(database=Helios)” “gzip()” “local(path
=C:\Backup\helios.bak.gz)”
local:
path=C:\Backup\helios.bak.gz
gzip: level = 9
————————
Exception #1
VDS::Create failed: x80770007
System.InvalidProgramException
v VdiNet.VirtualBackupDevice.Native.VirtualDeviceSet.CreateEx(String instance
Name, String deviceSetName, VirtualDeviceSetConfig config) v c:\users\clay\docum
ents\vdi.net\vdinet.virtualbackupdevice.native\virtualdeviceset.cpp:řádek 101
v VdiNet.VirtualBackupDevice.Exe64Sql64.VirtualDeviceSetExe64Sql64.CreateEx(S
tring instanceName, String deviceSetName, VirtualDeviceSetConfig config) v C:\Us
ers\Clay\Documents\vdi.net\VdiNet.VirtualBackupDevice\Exe64Sql64\VirtualDeviceSe
tExe64Sql64.cs:řádek 22
v MSBackupPipe.Common.BackupPipeSystem.BackupOrRestore(Boolean isBackup, Conf
igPair storageConfig, ConfigPair databaseConfig, List`1 pipelineConfig, IUpdateN
otification updateNotifier) v C:\Users\Clay\Documents\MSSQL Compressed\mssql sou
rceforge2\MSBackupPipe.Common\BackupPipeSystem.cs:řádek 109
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
Clay,
First off. Thank you for sharing your awesome program! Works well with all our 400+ databases, except for one which is over 250GB++ on SQL 2000. Is it possible to back this up with sql server compressed backup? I was using the command below to back it up:
backup “db(database=’ dbname’)” “gzip()” “local(path=’driveLetter’)” ‘
Thank you sir!
Clay,
Also, read about the time out being adjusted on version 1.2 but am already using 1.2 and it still persists.
Many many thanks in advance!
Hi! I’ve been using ur program to backup my DB and its been working without a glitch for so long, but now I get this error:
The process cannot access the file ‘E:\Backup\Alice\Alice3.bak.zip’ because it is being used by another process.
System.IO.IOException
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileInfo.Delete()
at MSBackupPipe.StdPlugins.Storage.LocalStorage.CleanupOnAbort() in C:\Users\Clay\Documents\MSSQL Compressed\mssql sourceforge2\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:\Users\Clay\Documents\MSSQL Compressed\mssql sourceforge2\MSBackupPipe.Common\BackupPipeSystem.cs:line 183
at MSBackupPipe.Common.BackupPipeSystem.Backup(ConfigPair databaseConfig, List`1 pipelineConfig, ConfigPair storageConfig, IUpdateNotification updateNotifier) in C:\Users\Clay\Documents\MSSQL Compressed\mssql sourceforge2\MSBackupPipe.Common\BackupPipeSystem.cs:line 51
at MSBackupPipe.Cmd.Program.Main(String[] args) in C:\Users\Clay\Documents\MSSQL Compressed\mssql sourceforge2\MSBackupPipe.Cmd\Program.cs:line 111
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’ve been using this command for all this time to backup and it never failed before:
C:\MSSQLCompressedBackup-1.2-20110729\MSSQLCompressedBackup-1.2-20110729_x64\msbp.exe backup “db(database=Alice)” “zip64(level=5;filename=Alice.bak)” “local(path=E:\Backup\Alice\Alice1.bak.zip;path=E:\Backup\Alice\Alice2.bak.zip;path=E:\Backup\Alice\Alice3.bak.zip;path=E:\Backup\Alice\Alice4.bak.zip;path=E:\Backup\Alice\Alice5.bak.zip;path=E:\Backup\Alice\Alice6.bak.zip;path=E:\Backup\Alice\Alice7.bak.zip;path=E:\Backup\Alice\Alice8.bak.zip)” > E:\Backup\Alice\Log_bak.txt.
Plz If u can give me some clues on how to solve this, cause its been few days that I cant get it back on the track and i cant afford to have my DB without a backup for so long.
Tnx in advance, Mijat.
Hi David,
I’m using first time and trying to test the Compress backup on our SQL Server 2005 cluster but is not able to connect to the server
Nodes: db01/db02
SQL Port: 62426 (not the 1433)
the command I’m running is
msbp.exe backup “db(database=DBName;instancename=SQLDB;clusternetworkname=db01;backuptype=full)” “gzip()” “local(path=Z:\MSSQL\BACKUP\FULL\DBName\DBName.bak)”
I’m getting this error
An error has occurred while establishing a connection to the server. When conne
cting to SQL Server 2005, this failure may be caused by the fact that under the
default settings SQL Server does not allow remote connections. (provider: SQL Ne
twork Interfaces, error: 26 – Error Locating Server/Instance Specified)
System.Data.SqlClient.SqlException
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception
, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj
ect stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boole
an& failoverDemandDone, String host, String failoverPartner, String protocol, Sq
lInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean
trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean
aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnecti
on owningObject, SqlConnectionString connectionOptions, String newPassword, Bool
ean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdent
ity identity, SqlConnectionString connectionOptions, Object providerInfo, String
newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOp
tions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConn
ection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owning
Object)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection o
wningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection ownin
gObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection ow
ningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection ou
terConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at VdiNet.VirtualBackupDevice.VirtualBackupDeviceFactory.DiscoverSqlPlatformF
romCnn(String cnn) in C:\Users\Clay\Documents\vdi.net\VdiNet.VirtualBackupDevice
\VirtualBackupDeviceFactory.cs:line 87
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 183
at MSBackupPipe.Common.BackupPipeSystem.Backup(ConfigPair databaseConfig, Lis
t`1 pipelineConfig, ConfigPair storageConfig, IUpdateNotification updateNotifier
) in C:\Users\Clay\Documents\MSSQL Compressed\mssql sourceforge2\MSBackupPipe.Co
mmon\BackupPipeSystem.cs:line 51
at MSBackupPipe.Cmd.Program.Main(String[] args) in C:\Users\Clay\Documents\MS
SQL Compressed\mssql sourceforge2\MSBackupPipe.Cmd\Program.cs:line 111
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
Please advise
I’m a dba myself and rather partial to the ola.hallengren.com scripts for my database maintenance. I’ve been aware of your solution for some time and just tried it today and it works wonderfully. I’m just wondering though how I would restore a database to a new name with move for the filenames etc as I often do when I need to restore. I will often do a restore for a customer to databasename_restore etc so that the needed data can be extracted from the restored db while the original is still online. I was considering doing come compression myself using a CLR SP to integrate with the existing SQL Backup restore commands, but I haven’t done enough research yet to see if this route is viable. One thing is for sure, Redgate SQL Backup is too expensive for most of my clients, I would like to develop my own free and open source watered-down version of this product.
Thanks for the excellent product and hard work. I hope you continue with your product.
Scott Emick
I realize this is a very late reply but for BLOCKSIZE, it can make a huge difference for very large databases with network backups.
http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Technical%20Case%20Study-Backup%20VLDB%20Over%20Network_Final.docx
Hi Clay,
I am getting the below errro when I try to backup the database
Could not create an instance: CLSID_MSSQL_ClientVirtualDeviceSet,
x8007007E System.InvalidProgramException
at MSBackupPipe.VirtualBackupDevice.VirtualDeviceSet..ctor()
in c:\documents and settings\clay.lenhart\my documents\personal\hg\mssqlcompressed
\msbackuppipe.virtualbackupdevice\virtualdeviceset.cpp:line 50
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\
MSBackupPi pe.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 111
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 having below environment set up
Microsoft SQL Server 2005 – 9.00.3282.00 (Intel X86)
Aug 5 2008 01:01:05 Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
I have tried with both 32 bit and 64 bit flavor of mspb .. still getting the same error.
We are not able to take compressed backup and this is stalling other activities,any help is greatly appreciated .
Thanks ….
For the record: Syntax for restoring a database (dbname) to a named instance (sql2005dev) from folder F:\sqlbackup is:
msbp.exe restore “local(path=F:\sqlbackup\dbname.bak.zip)” “zip64()” “db(database=dbname ;instancename=sql2005dev)”
Any idea how you can parse in the “with norecovery” parameter?
Hi Clay, I am trying to take a compressed backup on the clustered environment and getting the below error:
A network-related or instance related occured while establishing a connection to SQL Server.
This issue is only related to clustered servers the command I am using is:
C:\MSSQLCompressedBackup\MSSQLCompressedBackup-1.2-20110729\MSSQLCompressedBackup-1.2-20110729_x64>msbp.exe backup “db(database=DATABASE1;clusternetworkname=NETWORK1;backuptype=full)” ” gzip()” “local(path=H:\CompressedBackupFolder\DATABASE1.BAK.GZ)”
It’s not a named instance. Thanks in advance.
Hi Clay,
I want to know if I can backup and restore multiple databases that are dependent on each other. I read about Multithreaded Compressed Backup but I’m not clear about the syntax sample in the documentation.
msbp.exe backup “db(database=model)” “gzip()” “local(path=c:\model1.bak.gz;path=c:\model2.bak.gz;)”
To me you are only declaring a single database but specifying multiple compressed files. I want to do the opposite. Multiple databases in a compressed file.
Hi Clay,
first of all thank you for this great tool, it has given a great help with backin up my databases.
I have a script that creates databases backups on my network storage, and it works great when I run it manually from the command line, but it does nit when I schedule it in the windows task scheduler. I do not get any error, or feedback. It seems just the msbp command does not start at all . Do you maybe know where is the problem?
Thank you beforehand of any help
Ciao
Matteo
I must express appreciation to the writer just for bailing me out of this trouble. Because of browsing throughout the world-wide-web and meeting solutions that were not powerful, I thought my entire life was gone. Living without the answers to the difficulties you have solved by means of this guide is a serious case, as well as the ones that might have adversely affected my entire career if I hadn’t noticed your blog post. Your own personal training and kindness in handling all the stuff was useful. I don’t know what I would have done if I had not encountered such a point like this. I am able to at this point look ahead to my future. Thanks very much for your skilled and result oriented help. I won’t hesitate to refer the website to any person who should have support on this subject.
It’s the best time to make a few plans for the future and it is time to be happy.
Very efficiently written information. It will be supportive to anyone who utilizes it, including me. Keep doing what you are doing can’r wait to read more posts.
I am continually searching online for tips that can facilitate me. Thanks!
one of our visitors recently proposed the following website
That is my first-time my partner and i visit here. I ran across countless interesting material inside your blog, specifically its discussion. On the a lot of comments in your content articles, I suppose I will be definitely not on your own obtaining every one of the fun in this article! Continue the great function.
If office interiors you choose or alter for your office?This CAD drawing is available in a medical practitioner also make a decision from them. To plan and needs above all has to pay.
As a site owner I believe the material here is reallywonderful. I thank you for your hard work. You should keep it up forever! Good Luck..