-2

I got a task for backing up my clients data through internet. Client has about 50 GB of data there. So I need to back up the data and pass through internet, but it takes about 8 to 9 hours for this process because our internet connection is slow. So I can't resolve client issues in time.

So I decide to change my back up strategy. I tried differential backup. It solves somewhat my problem but not. Because it takes back up from previous full back up. So I tried to implement incremental backup. I know this will solve my problem. But SQL Server doesn't support incremental backup.

So please help me if there any way to resolve my issue. Also tell me why there is no incremental backup in SQL Server. But DB2 has.

I am using SQL Server 2008 R2.

10
  • 2
    Sql server does support kind of incremental backup and that is called transaction log backup which is possible only in full recovery model – Shanky Mar 2 '15 at 4:57
  • @Shanky I think transaction log back up will help only for accidental deletion of less amount of data. Not for bulk data isn't it ? – Anuj K.C. Mar 2 '15 at 5:04
  • 2
    SQL Server Support 4 type of backups 1. Full database Backup 2. Incremental\Transaction Backup 3. Differential Backup 4. File\File Group backup. To understand those types this link might help you sqlcache.blogspot.com/2014/06/… – AA.SC Mar 2 '15 at 6:14
  • 1
    @MD Haider Ali khan, Shanky already mentioned that backup types somehow depend on recovery models. so while dealing with backups you need to worry about them. – AA.SC Mar 2 '15 at 6:42
  • 1
    @ABCD trn backup is not dependant on diff backup if for a database full backup is taken you can take as much trn backup as you like * I still cannot understand the core question* – Shanky Mar 2 '15 at 7:46
8

I started writing this in comments, but quickly ran out of space so I'm putting in this answer.

The way backups work with FULL recovery in SQL Server you can do what you want with transaction and differential backups (as said in the comments).

A transaction log has all the statements since the last linked backup (be it a full, differential or trn backup).
In other words, if you take a full backup weekly (at weekends), a differential backup nightly, and transaction log backups hourly, to recover a database at 2AM on wednesday you would:

  1. Recover the full backup from sunday (With NORECOVERY)
  2. Recover the differential backup from tuesday night (again with NORECOVERY)
  3. Recover the trn backup from 1AM wednesday (again...)
  4. Recover the trn backup from 2AM wednesday (now you can recover)

So if you were to get the backup weekly through a scheduled data transfer, all you need to get up to date data is the differential backup of the night before, and all the log backups from after the differential backup up to when you want to recover.

Keep in mind though that recovery models matter.
If you want to utilise transaction log backups, you will need to switch to full recovery model. If you change from simple to full recovery model, to be able to start taking transaction log backups you will need to first create a full backup.

0

My understanding of your situation is that you are backing up the database, and then transferring the file via the internet. I'm assuming that this is being done for troubleshooting or finding issues. (If these assumptions are wrong, that may change things depending...)

You could try backing up the database across multiple files. To do that, you simply specify each file path and name to back up to. (here, here). Once you have multiple files, you can then move the files using whatever you use now (ftp or whatever). Hopefully, you can establish multiple calls and transfer the files in parallel.

If you're backing up directly across the internet, then the above could still work, although I'm not sure how much, if any, performance could be gained.

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