Skip to main content

Incremental or Differential Backups of SQL Server Database

Beginner
Posts: 7
Comments: 6

Does the SQL add on support Incremental or Differential Backups of SQL Server Database?

Frequent Poster
Posts: 2
Comments: 517

Top

Hello,

Our SQL backup is done using single-pass backup of the entire volume. The SQL meta-data is extracted during this volume backup, allowing you to restore seperate databases, mount them on a running instance and do all the other SQL-specific actions.

The volume backup supports both incremental and differential backups and the SQL single-pass functionality does not affect this in anyway.

Thank you.

Beginner
Posts: 7
Comments: 6

Top

If for a example a 500GB database gets new records of 100mb.

Will the backup what will be the approximate size of an incremental backup (near 500gb or 100mb)?

Forum Star
Posts: 58
Comments: 2186

Top

Hello eugene,

Thank you for your posting! Incremental backups will contain only changes to the files in comparison to the last backup, so the size should be smaller than the size of the full backups. Here you will find some additional information on Single-Pass backup:

Permissions for SQL Server backup and recovery
What else you need to know about single-pass backup

Thank you,

Beginner
Posts: 7
Comments: 6

Top

When I check the Sql Server ErrorLog File it seems that the database are backed up in full. But afterwards there is a message that BACKUP DATABASE successfully processed 0 pages. Please note that the recovery model of my databases is simple. Here is the part of ErrorLog during Acronis Backup:

 

2016-05-17 17:00:39.69 spid58      I/O is frozen on database model. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2016-05-17 17:00:39.69 spid60      I/O is frozen on database msdb. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2016-05-17 17:00:39.69 spid59      I/O is frozen on database MyDB1. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2016-05-17 17:00:39.69 spid69      I/O is frozen on database MyDB2. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2016-05-17 17:00:39.69 spid67      I/O is frozen on database MyDB3. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2016-05-17 17:00:39.69 spid65      I/O is frozen on database MyDB4. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2016-05-17 17:00:39.69 spid66      I/O is frozen on database MyDB5. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2016-05-17 17:00:39.69 spid68      I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2016-05-17 17:00:40.35 spid65      I/O was resumed on database MyDB4. No user action is required.
2016-05-17 17:00:40.35 spid67      I/O was resumed on database MyDB3. No user action is required.
2016-05-17 17:00:40.35 spid68      I/O was resumed on database master. No user action is required.
2016-05-17 17:00:40.35 spid58      I/O was resumed on database model. No user action is required.
2016-05-17 17:00:40.37 spid66      I/O was resumed on database MyDB5. No user action is required.
2016-05-17 17:00:40.37 spid59      I/O was resumed on database MyDB1. No user action is required.
2016-05-17 17:00:40.37 spid69      I/O was resumed on database MyDB2. No user action is required.
2016-05-17 17:00:40.37 spid60      I/O was resumed on database msdb. No user action is required.
2016-05-17 17:00:41.18 Backup      Database backed up. Database: master, creation date(time): 2016/05/13(14:03:50), pages dumped: 491, first LSN: 295:416:57, last LSN: 295:456:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{D92F6064-4377-445C-BDF8-798E8C1FBC0B}8'}). This is an informational message only. No user action is required.
2016-05-17 17:00:41.19 Backup      Database backed up. Database: model, creation date(time): 2003/04/08(09:13:36), pages dumped: 346, first LSN: 35:296:1, last LSN: 35:320:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{D92F6064-4377-445C-BDF8-798E8C1FBC0B}1'}). This is an informational message only. No user action is required.
2016-05-17 17:00:41.19 Backup      Database backed up. Database: MyDB1, creation date(time): 2016/05/11(11:05:53), pages dumped: 130490, first LSN: 636:560:34, last LSN: 636:592:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{D92F6064-4377-445C-BDF8-798E8C1FBC0B}3'}). This is an informational message only. No user action is required.
2016-05-17 17:00:41.20 Backup      Database backed up. Database: msdb, creation date(time): 2014/02/20(20:49:38), pages dumped: 2316, first LSN: 210:712:96, last LSN: 210:768:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{D92F6064-4377-445C-BDF8-798E8C1FBC0B}2'}). This is an informational message only. No user action is required.
2016-05-17 17:00:41.21 Backup      Database backed up. Database: MyDB2, creation date(time): 2016/05/11(12:31:02), pages dumped: 258250, first LSN: 264:760:34, last LSN: 264:792:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{D92F6064-4377-445C-BDF8-798E8C1FBC0B}4'}). This is an informational message only. No user action is required.
2016-05-17 17:00:41.22 Backup      BACKUP DATABASE successfully processed 0 pages in 1.577 seconds (0.000 MB/sec).
2016-05-17 17:00:41.22 Backup      BACKUP DATABASE successfully processed 0 pages in 1.694 seconds (0.000 MB/sec).
2016-05-17 17:00:41.22 Backup      BACKUP DATABASE successfully processed 0 pages in 1.652 seconds (0.000 MB/sec).
2016-05-17 17:00:41.22 Backup      Database backed up. Database: MyDB3, creation date(time): 2016/05/11(12:49:43), pages dumped: 1842, first LSN: 105:456:34, last LSN: 105:488:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{D92F6064-4377-445C-BDF8-798E8C1FBC0B}5'}). This is an informational message only. No user action is required.
2016-05-17 17:00:41.24 Backup      BACKUP DATABASE successfully processed 0 pages in 1.674 seconds (0.000 MB/sec).
2016-05-17 17:00:41.24 Backup      Database backed up. Database: MyDB4, creation date(time): 2016/05/11(14:17:46), pages dumped: 47730, first LSN: 613:120:34, last LSN: 613:152:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{D92F6064-4377-445C-BDF8-798E8C1FBC0B}6'}). This is an informational message only. No user action is required.
2016-05-17 17:00:41.24 Backup      Database backed up. Database: MyDB5, creation date(time): 2016/05/11(14:40:32), pages dumped: 4386, first LSN: 413:224:34, last LSN: 413:256:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{D92F6064-4377-445C-BDF8-798E8C1FBC0B}7'}). This is an informational message only. No user action is required.
2016-05-17 17:00:41.25 Backup      BACKUP DATABASE successfully processed 0 pages in 1.657 seconds (0.000 MB/sec).
2016-05-17 17:00:41.25 Backup      BACKUP DATABASE successfully processed 0 pages in 1.657 seconds (0.000 MB/sec).
2016-05-17 17:00:41.25 Backup      BACKUP DATABASE successfully processed 0 pages in 1.645 seconds (0.000 MB/sec).
2016-05-17 17:00:41.26 Backup      BACKUP DATABASE successfully processed 0 pages in 1.655 seconds (0.000 MB/sec).
 

 

 

Attachment Size
358526-129100.txt 5.88 KB