Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question
Which database? I mean, which DBMS? – Rachcha Mar 21 at 13:20
We're using SQL Server 2000 via .NET – Scott P Mar 21 at 18:14

migrated from stackoverflow.com Mar 21 at 15:39

4 Answers

up vote 3 down vote accepted

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.

share|improve this answer
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 Mar 21 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). – StanleyJohns Mar 21 at 20:00
up vote 5 down vote

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.

share|improve this answer
up vote 4 down vote

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

share|improve this answer
up vote -2 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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