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).
Hi Clay,
This tool is a fantastic tool and its really helpful to us in so many ways.
I am working on scheduling the backup policy using msbp on powershell.
I will post you an update, once I will be able to achieve the desired results.
Regards,
Mehul
I’m glad you like it. Powershell integration would be great. I think it would be possible to control MSBackupPipe.Common.dll which is UI agnostic.
Hi,
Can we take remote machine backup ?
Ex. DB Source is one machine and Backup destination is separate machine.
If possible can you please post syntax as well !!
Thanks
Mehul, The code needs to use a DLL which is on the server. Perhaps one day this applicaiton would be split so that some code runs on the server and some code runs on a remote client).
You can, however, run msbp.exe on the server and write to a network share. I believe the command would be something like:
msbp.exe backup “db(database=model)” “local(path=\\\\server\share\model.bak)”
That is, “\\” needs to be “\\\\”.
Thanks Clay.
I want to run msbp.exe on a central server to get the database backed up from all other SQL Server machines. Like client server architecture.
any clue to achieve that target ?
Thanks.
Only with duck tape.
Not pretty, but you could write a program to copy msbp.exe and dependencies to the database server, then run it remotely using schtask.exe (http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/schtasks.mspx?mfr=true). That is, you can use schtask.exe to run the program on another machine.
A small query. We dont have any provision to provide user & password to connect to SQL Server while running the msbp utility.
Whenever we run this utility, it is running with local system credentials.
any alternate to this ?
Thanks.
There are actually two connections to SQL Server. The second one is via a DLL, and it has to be the currently running user.
Hi Clay,
While running the utility, it always shows GMT timings. I am running this in IST timings. But it always shows 5:30 Hrs back from IST timings.
Any clue to fix this ?
Thanks.
Hi Clay,
could you help me – I have a problem using the utility. I should place two bases in a zip-archive as a result. Вut only one base appeares in the zip-file though 7-zip allows to attach the second base. Does the utility allow to do this? I could not find any mention of this in the manual.
Thanks.
Kamazi,
Unfortunately this scenario was not considered. The utility creates new files each time.
-Clay
Hi Clay,
KEEP_REPLICATION option is not working using msbp. , if I create a dummy database ,and restore with KEEP_REPLICATION using msbp , “The backup set holds a backup of a database other than the existing ‘Adventureworks’ database.” Error . I have rowguid columns populated in the database.
If I create a dummy database , and use native back up with keep_replication I am getting rowGUID values after restore
Thanks
declare @res varchar(2000)
set @res=’D:\dump\msbp\MSSQLCompressedBackup-1.2-20100117_x64\msbp.exe restore “local(path=D:\dump\_1_testdb.bak.zip)” “zip64″ “db(database=testdb;instancename=INST01;replace=yes;KEEP_REPLICATION;filegroup=primary;MOVE=”testdb”TO”D:\MSSQL\data\testdb.mdf”;MOVE=”testdb_log”TO”E:\MSSQL\Tranlogs\testdb_log.ldf”;)”‘
print @res
exec master..xp_cmdshell @res