SQL Server 2005 Compressed Backup

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).

About Clay Lenhart

I am a DBA/Technical Architect for Latitude Group and love technology.
This entry was posted in MSSQL Compressed Backup and tagged , . Bookmark the permalink.

88 Responses to SQL Server 2005 Compressed Backup

  1. Mehul says:

    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

  2. Clay Lenhart says:

    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.

  3. Mehul says:

    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

  4. Clay Lenhart says:

    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 “\\\\”.

  5. Mehul says:

    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.

  6. Clay Lenhart says:

    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.

  7. Mehul says:

    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.

  8. Clay Lenhart says:

    There are actually two connections to SQL Server. The second one is via a DLL, and it has to be the currently running user.

  9. Mehul says:

    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.

  10. Kamazi says:

    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.

  11. Clay Lenhart says:

    Kamazi,
    Unfortunately this scenario was not considered. The utility creates new files each time.
    -Clay

  12. Kumar says:

    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

  13. Kumar says:

    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

  14. Roger says:

    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.?

  15. Clay Lenhart says:

    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

  16. 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.

  17. Clay Lenhart says:

    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.

  18. 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.

  19. Clay Lenhart says:

    Thanks for the update!

  20. 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.

  21. Jochen says:

    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

  22. Manoj says:

    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

  23. Clay Lenhart says:

    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.

  24. Manoj says:

    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

  25. Clay Lenhart says:

    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)

  26. Darren W says:

    Is it possible to restore the backup, to a different database server?

  27. Clay Lenhart says:

    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.

  28. Doug says:

    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 :)

  29. Armindo Silva says:

    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).

  30. Jose M. Vazquez says:

    Hi clay,

    I’m not sure if I understood how it works. Imagine that I have more than one instance or a named instance on the same server ( clustered instance to be more clear ). How can backup one database from a particular instance ? What is the exact syntax.

    Best regards,
    Jose

  31. 上品なブランドの新作が期間限定セール発売上品なルイヴィトン、グッチやエルメスなどのブランドコピーの新作が大量入荷しました。種類が豊富で、勝手に選べます。激安の上に、品質には保証があって、末長くご愛用いただけます。新年とクリスマスを迎えで、期間限定セールが開催中で、早く手に入れましょう。 http://www.gginza.com/News/index.html

  32. スーパーコピーブランド格安販売店はこちらへ!品々の激安価格に持ったスーパーコピーブランド 代引きの新作はお客様に提供されます。安心、迅速、確実、お客様の手元にお届け致します。★弊社は9年の豊富な経験と実績を持っております。★一流の素材を選択し、精巧な作り方でまるで本物のようなな製品を造ります。★品質を重視、納期も厳守、お客様第一主義を貫きは当社の方針です。★驚きの低価格で商品をお客様に提供致します!★早速に購入へようこそ! http://www.eevance.com/tokei/zenith/index.html

  33. 色は、この腕時計で新しいことを唯一のものでない:リンデやり直しのダイヤルを持って、5層からなる上部層、さらに白骨化されている。この月のより多くの相は10、11時位置で着用者によって見られるのを許します。また、lwなくなると彼らは合金リンデと呼んでいますが、「航空宇宙材料」を作成しました(または「alw」)。その音は少しあるかもしれない間、リンデによってチタンの半分の重さと鋼の強度が2倍になった。材料は、以前は「無色」の形で使用されているが、リンデの白い背景の上に、光を吸収するとタコの色を変更する方法を模倣することを意図します。我々がどのようにすべての人にこれを翻訳するのを見ます、しかし、それは本当ですが、リンデの商標の角の場合このクールでむしろ衝撃的に見えます、薄い灰色の色。 http://www.ooobrand.com/bags/hermes/2248.html

  34. エマニュエル・ブーシェのもう一つの才能と非常に創造的な「近代」を考え、他のサードパーティ製の時計ブランドとは、単にエマニュエル・ブーシェの腕時計で彼自身の名祖の会社を開始するとの考えを捨てた。彼の最初の腕時計、エマニュエル・ブーシェの合併症の1つは、最も面白いと新しい機械の腕時計を私は見ました。 http://www.fujisanbrand.com/wallet/vuitton/index_12.html

  35. は素晴らしい万「FUN」の型の表をシンクロ以外にも、Swatch香港区開設の首の間の旗艦店を開店し、強力な販売網を構築。スーパーコピーブランドMr . Kevin Rollenhagen衆がナンシーだと、城の女神イケメンを鳴らす式獅子舞形にでき、一緒に巨大な「どら」にし、新しい自動車ボール砲杖、百の水素気球すぐさまゆっくりと打ち上げて、象徴「Swatch利園山道旗艦店」が開幕し、城人衆潮、VIP貴賓やメディア代表として出席して、一緒にこの腐表壇新話。 http://www.gowatchs.com/brand-233.html

  36. 超人気旺店全新登場人気ブランド品★2016年全新登場★揃っている。ロレックス腕時計、オメガ、ブルガリ、カルティエIWC腕時計、シャネルコピーバッグ、財布腕時計、バネライ、ベル&ロス、ゴルム腕時計チュードル腕時計、ルイヴィトン、グッチバッグ財布、コピーミュウミュウバッグ財布、エルメス、ブラダバッグ、財布等弊社は「信用第一」をモットーにお客様にご満足頂けるよう、送料は無料です(日本全国)! ご注文を期待しています!下記の連絡先までお問い合わせください。是非ご覧ください http://www.gowatchs.com/news_cat-12.html

  37. もう長い間のない中国人と思った、私たちの報道掲示の主な著しい傾向は、西部地域の本当に目覚めました。中国三大経済地域で、西部は最も発達しても、少なくとも贅沢なブランド「浸透される」の地域。十年前から、中央政府は近年にもっと力を入れて、高まって与える西部地域の戦略を縮小し、大陸部と沿海都市間の格差。だから、西部の経済の急速な発展は、贅沢品消費も後を追う。 http://www.eevance.com/tokei/chanel/index.html

  38. 一項ごとに作品だけ1種あるいは多種の衰えぬ人気のタブ技術、例えば垂直使用雕刻刀の斜め曲線エッチング術、明るさと同時にアップに際立って飾り物;や透かし雕り技術、必要でまずカッター彫っ彫刻して飾るモデル、更に磨き滑らかな波紋ヤスリ。さまざまな花模様を反映している別のスキルは、例えば「勿忘草」柄の内を見せたエナメルケースエナメル大師非凡な技芸風変わりな黒金ケースは、鉛、銅、銀、硫黄やアンモニア塩の混合物にモデルに象眼して、それからエナメル大師窯に焼き、余分の材料をヤスリで磨き、より滑らかな表面を、植物の図案が現れる。宝石をちりばめ部分、ターコイズとガーネットを体現した芸術家に鮮やかな色を活用する。スーパーコピー時計は1項、靑いエナメル文字盤の黄金のブローチ表には、明るい色の宝石が真に迫っていて演じたみずみずしい花一輪エデンを静かに咲く。 http://www.newkakaku.com/lxq8.htm

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>