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
Hi Clay, Am new to SQL Server 2005 and was wondering if you could help me with your code. I have all the SQL BACKUPS in a folder.eg C:\$user\SSO\Backups~Database\SSO_Sep 20, 10.BAK
How would i use your code to compress this backup.?
Roger,
The tool compresses while you backup. It doesn’t compress files. To backup a database, run the following command from the DOS prompt:
msbp.exe backup “db(database=model)” “gzip()” “local(path=c:\model.bak.gz)”
More examples are here:
http://mssqlcompressed.sourceforge.net/doc_started.shtml
Dear Clay,
Its really a fantastic tool, Thanks so much for it. We have one concern, the application runs on the local user credentials, however the local user does not have privileges on the computer and the application accesses the DB through named user / pass. Mixed mode is disabled. We centrally have all the details and are trying to schedule a backup of the DB for the user everytime he quits the application. Any possibility ? If we are able to figure this out we would roll this out for around 3k SQL 2005 / MSDE databases.
Thanks,
Cheers,
Vikram.
Vikram, Thanks!
Unfortunately it uses a SQL Server DLL and it requires Windows authentication for its connection.
A couple of options are:
Your code could use impersonation to run under a different account. The accepted answer here has links that show how to do it in .Net code: http://stackoverflow.com/questions/125341/impersonation-in-net
You create a Windows Service that runs under a different user, and your application sends a message to it to start the backup.
Good luck! Let me know how it goes on.
Hey Clay,
Thought I’d drop you a line, the route my team is planning to take is to write a short sp to retrieve the local user account, enable windows logon, add the account, take the compressed backup and reverse the above steps.
Its currently under testing, hopefully should work and save close to 200 hours daily
Cheers,
Vikram.
Thanks for the update!
Hey Clay,
It’s been some time ! Wanted to say thanks again, we have rolled this out to 3000 distributed DBs, we had to package .net framework along with it as it was missing on most computers.
Other than 200 instances it has worked great and the time and space saving is highly appreciated.
It is currently working for the following versions.
MSDE
SQL Server 2005 Standard Edition
SQL Server 2005 Express Edition
Cheers,
Vikram.
Hi,
how can i get a result set containing a list of the database and log files contained in the backup set?
Is it possible to get something similar like
“RESTORE FILELISTONLY FROM “?
Kind regards,
Jochen
Hi Clay,
This is a fantastic tool. I was just wondering if I can use 7z instead of gzip for compression. If yes, could you please tell how?
Thanks.
-Manoj
No unfortunately it isn’t implemented. At the time there wasn’t a good .Net DLL for 7z compression. It would be something straightforward to add in the code since there are several compression algorithms already if anyone is interested in doing this.
Hi Clay,
gzip is creating compressed files with extension .7z too. I uncompressed the files using 7zip and tried to restore the DB. That worked too.
Your utility uses windows logon to read data from SQL. However, due to some security policies on our Production servers, we cannot enable windows logon for SQL. Could you please tell me if I can pass user/password as parameters and get things working?
Thanks,
-Manoj
Unfortunately the 2nd connection to the database requires a windows-based login. This is not through ADO.Net, but rather through a SQL Server DLL on the server (vdi)
Is it possible to restore the backup, to a different database server?
Darren, Yes, but you run msbp on the server you want to restore to. I think you might be asking if you can run msbp on one machine to restore the database on another machine — which isn’t possible.
Just testing on the northwind db and I get this:
C:\mssqlbkup>msbp.exe backup “northwind” file:///e:\bak\northwind.bak
Plugin not found: northwind
System.ArgumentException
at MSBackupPipe.Cmd.ConfigUtil.ParseComponentConfig(Dictionary`2 pipelineComponents, String componentString) in C:\Users\Clay\Documents\MSSQL Compressed\mssql sourceforge2\MSBackupPipe.Cmd\ConfigUtil.cs:line 68
at MSBackupPipe.Cmd.Program.ParseBackupOrRestoreArgs(List`1 args, Boolean isBackup, Dictionary`2 pipelineComponents, Dictionary`2 databaseComponents, Dictionary`2 storageComponents, ConfigPair& databaseConfig, ConfigPair& storageConfig)in C:\Users\Clay\Documents\MSSQL Compressed\mssql sourceforge2\MSBackupPipe.Cmd\Program.cs:line 292
at MSBackupPipe.Cmd.Program.Main(String[] args) in :\Users\Clay\Documents\MSSQL Compressed\mssql sourceforge2\MSBackupPipe.Cmd\Program.cs:line 94
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
I’m sure it’s something dumb that I’m doing but could you give a hint?
thanks
Doug, it should be:
C:\mssqlbkup>msbp.exe backup [northwind] file:///e:\bak\northwind.bak
(assuming that northwind is the nme of the database you want to backup).