Sql Server Express Incremental Backup and Log Shipping

by
Reading Time: 2 minutes

Many of our customers (with installed version of OfficeClip Suite) were asking us how to take backup of the database and how to avoid data loss if the server died. Most of our users run OfficeClip on Sql Express database and Microsoft does not provide Log Shipping for the Sql Server Express Edition.

So we decided to write a command line interface which would manage incremental backup and restore for our customer. Anticipating that this would be a useful solution for many other sql express users, we decided to make the source available free. Here are some of the features:

  1.  Full backup is taken every day (regardless of how often the program is run)
  2. Incremental backup is taken periodically (say 10 minutes) when the program is run
  3. It can keep the past backup history for a certain number of days
  4. Log file is created in HTML format so it is easy to read
  5. Backup can be restored using a single command that first restores the most recent full backup and then restores all the incremental backups in the correct order
  6. It is possible to use the Windows Scheduler and run the program periodically and save the files to a destination machine and restore periodically thereby simulating log shipping
  7. It can be run from a command prompt

 

      Notes:

  1. Before running this program on a database, you must change the database to “Full Recovery Mode” from the Sql Server Express Management Studio.
  2. For restoring the backups make sure that the sql server user name and permission should match in both database.
  3. The program is released under BSD License, so that you can also use it commercially without paying any royalities whatsoever.
  4. It is written in C# using .net framework 2.0 in visual studio 2005 but should run on any vs.net 2008 editions.
  5. If you are an OfficeClip customer, the same program is available in the distribution and it is called ocbackup.exe.

Download: Source Code | Executables Only

Usage:

To see all the options, run the program: OfficeClip.OpenSource.LogShipping.exe (from the LogShipping\bin\debug folder)

Examples:

The following command takes transactional backup every time it is run, takes full backup every day, keeps old backup for 7 days for the database called officeclip. The backup files are stored in the folder E:SqlBackups. It can be run every 10 minutes using the windows scheduler.

 OfficeClip.OpenSource.LogShipping -bd 7 -c backup -d officeclip -f “E:\SqlBackups” -s “Server=(local);Database=Master;Trusted_Connection=True”

The following command restores the backup to the database called officeclipR. The files are read from the folder E:SqlBackups.

 OfficeClip.OpenSource.LogShipping -bd 7 -c backup -d officeclipR -f “E:\SqlBackups” -s “Server=(local);Database=Master;Trusted_Connection=True”

License: BSD License

If you download and end up using this code, please leave a note. We would like to know who is using this code.