We have a machine tool application that runs on a SQL server backend. These tools run all day every day and I am looking for a way to incrementally backup the database without any machine downtime.

I've seen mirroring solutions with two servers, but these solutions would not work for us do to cost and size constraints. Is it possible to programmatically or through some service to mirror this database to a backup hard drive on the main PC?

Some data loss is acceptable.

  • Which database? I mean, which DBMS?
    – Rachcha
    Commented Mar 21, 2013 at 13:20
  • We're using SQL Server 2000 via .NET
    – Scott P
    Commented Mar 21, 2013 at 18:14

4 Answers 4


As @Remus Rusanu said, SQL backups do not require downtime. For incremental backups: First do a full backup followed by log backups< which will be your incremental backups. Note that your DB should be in full recovery mode for the log backups.

To restore your DB, you will need the full backup and all the (incremental) log backup files.

SQL Mirroring is a high availability solution, not as much a backup solution. You mentioned cost as a factor, so you do not need mirroring if all you need are incremental backups.

  • If I end up with something like 180 (daily for 6 months) log backup files in addition to the full backup, will I be able to automatically restore this database? Is this a rational approach, or will this take many hours to recover and be error prone?
    – Scott P
    Commented Mar 21, 2013 at 18:28
  • You can throw in differential backups to reduce the number of log file restores required. This depends on your RPO. This is what @Remus meant with (eg. full weekly, diferential daily, log hourly etc). Commented Mar 21, 2013 at 20:00

None of the SQL Server backups require downtime. Full, differential, log backup are all online.

So just do a normal run-of-the-mill backup scheme (eg. full weekly, diferential daily, log hourly etc) and go with it. Is incremental and is online.


As many others have said, in Microsoft SQL Server backups do not require a downtime. They automatically record changes that happen to the data while the backup is running. During the restore that change log is used to bring the database into a consistent state that lies somewhere between the start time and the end time of the backup.

If you need more control over the point in time to restore to, you need to also take log bakups. A database that is in FULL recovery mode allows you to take log backups. with a full backup and all log backups that where taken afterwards you can restore the database to any point in time after that full backup.

there are also differential backups that allow you to reduce the storage requirement for backups. To restore a differential backup you first need to restore the most recent full backup that was taken before the differential. Intermediate differentials are not required.

What type of backups to take and how frequent is dependent on you recovery point and recovery time objectives. (How long can the service be down? How much data loss can I afford?)

A starting point for planning you backup strategy is http://msdn.microsoft.com/en-us/library/ms191239(v=sql.105).aspx


You should look up for sql server replication service. It is designed for this only.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.