{"id":1513,"date":"2014-02-07T09:00:00","date_gmt":"2014-02-07T17:00:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/dataplatforminsider\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/"},"modified":"2024-01-22T22:49:02","modified_gmt":"2024-01-23T06:49:02","slug":"differential-database-backup-with-memory-optimized-tables","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/","title":{"rendered":"Differential Database Backup with Memory-Optimized Tables"},"content":{"rendered":"<p>This blog describes how differential database backup is taken on database with one or more memory-optimized tables. For full database backups, please refer to <a href=\"http:\/\/blogs.technet.com\/b\/dataplatforminsider\/archive\/2014\/02\/05\/database-backup-with-memory-optimized-tables.aspx\">Database Backup with Memory-Optimized Tables<\/a>.<\/p>\n<p>For disk-based tables, the differential database backup includes only the pages that have changed since the last full database backup. \u00a0SQL Server 2014 supports differential backup on databases with memory-optimized tables. The differential backup of a database with memory-optimized tables contains the following data<\/p>\n<ul>\n<li>The differential backup component for the disk-based tables is exactly the same as it is in databases without memory-optimized tables<\/li>\n<li>Active transaction log. This is exactly the same as with full database backup.<\/li>\n<li>For a memory-optimized filegroup, the differential backup uses the same algorithm as a full database backup to identify data\/delta files for backup but it then filters out the subset of files as follows:\n<ul>\n<li><b>Data File \u2013<\/b> A data file contains newly inserted rows and once it is full, it is closed. Once the data file is closed, it is only accessed in read-only mode. A data file is backed up only if it was closed after the last full database backup. In the other words, the differential backup only backs up data files containing the inserted rows since the last full database backup.<\/li>\n<li><b>Delta File<\/b> \u2013 A delta file stores references to the deleted data rows. A delta file is always backed up. Since any future transaction can delete a row, a delta file can be modified anytime in its life time, it is never closed. Note, the delta files typically take &lt; 10% of the storage.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>Note, this optimization is only available in RTM. In CTP2, the differential database backup included all data\/delta files just like in full database backup.<\/p>\n<p>Let us walk through an example showing the difference in size of full database backup and a differential database backup. The example is based on the following database and the table schema. Please refer to blog <a href=\"http:\/\/blogs.technet.com\/b\/dataplatforminsider\/archive\/2014\/01\/23\/state-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables.aspx\">State-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables<\/a>\u00a0 for details on the state of CFPs in the following examples.<\/p>\n<p><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">CREATE DATABASE<\/span> imoltp<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #0000ff\">GO<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">ALTER DATABASE<\/span> imoltp <span style=\"color: #0000ff\">ADD FILEGROUP<\/span> imoltp_mod <span style=\"color: #ff00ff\">CONTAINS<\/span> MEMORY_OPTIMIZED_DATA<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">ALTER DATABASE<\/span> imoltp <span style=\"color: #0000ff\">ADD FILE<\/span> (name=<span style=\"color: #ff0000\">&#8216;imoltp_mod&#8217;<\/span>, <span style=\"color: #0000ff\">filename<\/span>=<span style=\"color: #ff0000\">&#8216;c:dataimoltp_mod&#8217;<\/span>) <span style=\"color: #0000ff\">TO FILEGROUP<\/span> imoltp_mod<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #0000ff\">GO<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">use<\/span> imoltp<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #0000ff\">go<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #339966\">&#8212; create the table with each row around 8K<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">CREATE TABLE<\/span> dbo.t_memopt (<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c1 <span style=\"color: #0000ff\">int<\/span> NOT NULL,<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c2 <span style=\"color: #0000ff\">char<\/span>(40) NOT NULL,<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c3 <span style=\"color: #0000ff\">char<\/span>(8000) NOT NULL,<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: #0000ff\">CONSTRAINT<\/span> [pk_t_memopt_c1] <span style=\"color: #0000ff\">PRIMARY KEY NONCLUSTERED HASH<\/span> (c1)<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">WITH<\/span> (<span style=\"color: #0000ff\">BUCKET_COUNT<\/span> = 100000)<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\">) <span style=\"color: #0000ff\">WITH<\/span> (<span style=\"color: #0000ff\">MEMORY_OPTIMIZED<\/span> = <span style=\"color: #0000ff\">ON<\/span>, <span style=\"color: #0000ff\">DURABILITY<\/span> = SCHEMA_AND_DATA)<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #0000ff\">go<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #339966\">&#8212; load 8000 rows. This should load 5 16MB files<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">declare<\/span> @i <span style=\"color: #0000ff\">int<\/span> = 0<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">while<\/span> (@i &lt; 8000)<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #0000ff\">begin<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: #0000ff\">insert<\/span> t_memopt <span style=\"color: #0000ff\">values<\/span> (@i, <span style=\"color: #ff0000\">&#8216;a&#8217;<\/span>, <span style=\"color: #ff00ff\">replicate<\/span> (<span style=\"color: #ff0000\">&#8216;b&#8217;<\/span>, 8000))<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: #0000ff\">set<\/span> @i += 1;<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #0000ff\">end<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #339966\">&#8212; Do Manual checkpoint<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #0000ff\">Checkpoint<\/span><\/p>\n<h2>Example-1 Full and Differential database backup a database after loading 8000 rows and completing the manual checkpoint<\/h2>\n<ul>\n<li>Checkpoint File Pairs (CFPs) \u2013 Since I am running on a machine with &lt; 8 cores with &lt; 16GB of physical memory, I have 8 CFPs in \u2018PRECREATED\u2019 state with 16MB sized data file and 1MB sized delta file. Also, there are 5 CFPs in ACTIVE state as explained in the blog <a href=\"http:\/\/blogs.technet.com\/b\/dataplatforminsider\/archive\/2014\/01\/23\/state-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables.aspx\">State-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables<\/a>.<\/li>\n<\/ul>\n<ul>\n<li>Full Database Backup:<i> <\/i>Wait 5 minutes and then do the full database backup. The 5-minute wait is more of a safety factor to make sure relevant data files are not missed due to time-drift. This is not a concern in production environment there as typically there is significant time span between checkpoints, full database backup and subsequent differential database backup.<\/li>\n<\/ul>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">BACKUP DATABASE<\/span> [imoltp] <span style=\"color: #0000ff\">TO<\/span> DISK = <span style=\"color: #ff0000\">N&#8217;C:dataimoltp-full-data.bak&#8217;<\/span><\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">WITH NOFORMAT<\/span>, <span style=\"color: #0000ff\">INIT<\/span>, NAME = <span style=\"color: #ff0000\">N&#8217;imoltp-Full Database Backup&#8217;<\/span>, <span style=\"color: #0000ff\">SKIP<\/span>, <span style=\"color: #0000ff\">NOREWIND<\/span>, <span style=\"color: #0000ff\">NOUNLOAD<\/span>, <span style=\"color: #339966\">STATS<\/span> = 10<\/span><\/p>\n<ul>\n<li>Output:<\/li>\n<\/ul>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">Processed 304 pages for database &#8216;imoltp&#8217;, file &#8216;imoltp&#8217; on file 1.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">10 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">20 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">Processed 8064 pages for database &#8216;imoltp&#8217;, file &#8216;imoltp_mod&#8217; on file 1.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">30 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">40 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">Processed 8612 pages for database &#8216;imoltp&#8217;, file &#8216;imoltp_log&#8217; on file 1.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">100 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">BACKUP DATABASE successfully processed 16980 pages in 1.592 seconds (83.323 MB\/sec).<\/span><\/p>\n<ul>\n<li>Result<i>: <\/i>The size of the full database backup is around 138 MB. This includes size around 70MB of transaction log and around 70MB of CFPs.<\/li>\n<\/ul>\n<ul>\n<li>Differential Database Backup: Since we have completed the full database backup, the differential backup will skip data files as no new using the following command<\/li>\n<\/ul>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">BACKUP DATABASE<\/span> [imoltp] <span style=\"color: #0000ff\">TO\u00a0<\/span><\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">DISK<\/span> = <span style=\"color: #ff0000\">N&#8217;C:dataimoltp-diff-data.bak&#8217;<\/span> <span style=\"color: #0000ff\">WITH<\/span> DIFFERENTIAL, <span style=\"color: #0000ff\">NOFORMAT<\/span>, <span style=\"color: #0000ff\">INIT<\/span>,\u00a0<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">NAME = <span style=\"color: #ff0000\">N&#8217;imoltp-diff Database Backup&#8217;<\/span>, <span style=\"color: #0000ff\">SKIP<\/span>, <span style=\"color: #0000ff\">NOREWIND<\/span>, <span style=\"color: #0000ff\">NOUNLOAD<\/span>, <span style=\"color: #339966\">STATS<\/span> = 10<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">GO<\/span><\/p>\n<ul>\n<li><i>Output:<\/i><\/li>\n<\/ul>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">Processed 48 pages for database &#8216;imoltp&#8217;, file &#8216;imoltp&#8217; on file 1.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">Processed 0 pages for database &#8216;imoltp&#8217;, file &#8216;imoltp_mod&#8217; on file 1.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">10 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">20 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">30 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">40 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">51 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">60 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">71 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">81 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">91 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">Processed 8613 pages for database &#8216;imoltp&#8217;, file &#8216;imoltp_log&#8217; on file 1.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">100 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">BACKUP DATABASE WITH DIFFERENTIAL successfully processed 8661 pages in 0.760 seconds (89.031 MB\/sec).<\/span><\/p>\n<ul>\n<li>Result<i>: <\/i>The size of differential database backup is around 72 MB predominantly consisting of transaction log. The output shows that the existing data files in \u2018ACTIVE\u2019 state are not backed up as they were not changed since the last full database backup.<\/li>\n<\/ul>\n<h2>Example-2 Differential database backup after deleting 4000<\/h2>\n<p><span style=\"font-family: 'courier new', courier;color: #339966\">\u00a0&#8212; now delete 50% rows<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">declare<\/span> @i <span style=\"color: #0000ff\">int<\/span> = 0<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">while<\/span> (@i &lt;= 8000)<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #0000ff\">begin<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: #0000ff\">delete<\/span> t_memopt <span style=\"color: #0000ff\">where<\/span> c1 = @i<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: #0000ff\">set<\/span> @i += 2;<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #0000ff\">end<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #0000ff\">go<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #0000ff\">checkpoint<\/span><\/p>\n<p><span style=\"font-family: 'courier new', courier;color: #0000ff\">go<\/span><\/p>\n<ul>\n<li>Checkpoint File Pairs (CFPs) \u2013 No changes as only deleted existing rows<\/li>\n<\/ul>\n<ul>\n<li>Differential Database Backup: Execute the following command<\/li>\n<\/ul>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">&#8212; do the differential database backup<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">BACKUP DATABASE<\/span> [imoltp] <span style=\"color: #0000ff\">TO<\/span>\u00a0<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\"><span style=\"color: #0000ff\">DISK<\/span> = <span style=\"color: #ff0000\">N&#8217;C:dataimoltp-diff-data-del50-chkpt.bak&#8217;<\/span> <span style=\"color: #0000ff\">WITH\u00a0 DIFFERENTIAL<\/span> , <span style=\"color: #0000ff\">NOFORMAT<\/span>, <span style=\"color: #0000ff\">INIT<\/span>,\u00a0<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">NAME = <span style=\"color: #ff0000\">N&#8217;imoltp-diff Database Backup&#8217;<\/span>, <span style=\"color: #0000ff\">SKIP<\/span>, <span style=\"color: #0000ff\">NOREWIND<\/span>, <span style=\"color: #0000ff\">NOUNLOAD<\/span>,\u00a0 STATS = 10<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier;color: #0000ff\">GO<\/span><\/p>\n<ul>\n<li>Output: the pages reported are computed at 8k size.<\/li>\n<\/ul>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">Processed 88 pages for database &#8216;imoltp&#8217;, file &#8216;imoltp&#8217; on file 1.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">Processed 47 pages for database &#8216;imoltp&#8217;, file &#8216;imoltp_mod&#8217; on file 1.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">10 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">20 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">30 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">40 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">50 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">60 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">Processed 8845 pages for database &#8216;imoltp&#8217;, file &#8216;imoltp_log&#8217; on file 1.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">100 percent processed.<\/span><\/p>\n<p style=\"margin-left: 60px\"><span style=\"font-family: 'courier new', courier\">BACKUP DATABASE WITH DIFFERENTIAL successfully processed 8980 pages in 0.941 seconds (74.552 MB\/sec).<\/span><\/p>\n<ul>\n<li>Result<i>: <\/i>Note, that transaction log has few more pages to account for deleted rows and the checkpoint. Also, 47 pages from files in memory-optimized filegroup were included in the backup. Out of this 47, the 32 pages are from the empty data file that was created with manual checkpoint. The internal page size in data files is 256K or 32 8k pages. The first page in the data file contains header information therefore it is part of the backup. Other 15 pages are from delta files. The total size of the backup is around 74.4MB<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>This blog describes how differential database backup is taken on database with one or more memory-optimized tables. For full database backups, please refer to Database Backup with Memory-Optimized Tables. For disk-based tables, the differential database backup includes only the pages that have changed since the last full database backup.<\/p>\n","protected":false},"author":1457,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ep_exclude_from_search":false,"_classifai_error":"","_classifai_text_to_speech_error":"","footnotes":""},"post_tag":[],"product":[],"content-type":[2424],"topic":[],"coauthors":[2487],"class_list":["post-1513","post","type-post","status-publish","format-standard","hentry","content-type-best-practices","review-flag-1593580427-503","review-flag-1593580410-819","review-flag-1593580419-556","review-flag-1-1593580431-15","review-flag-2-1593580436-981","review-flag-5-1593580452-31","review-flag-8-1593580467-480","review-flag-alway-1593580309-407","review-flag-never-1593580314-843","review-flag-new-1593580247-437"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Differential Database Backup with Memory-Optimized Tables - Microsoft SQL Server Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Differential Database Backup with Memory-Optimized Tables - Microsoft SQL Server Blog\" \/>\n<meta property=\"og:description\" content=\"This blog describes how differential database backup is taken on database with one or more memory-optimized tables. For full database backups, please refer to Database Backup with Memory-Optimized Tables. For disk-based tables, the differential database backup includes only the pages that have changed since the last full database backup.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/\" \/>\n<meta property=\"og:site_name\" content=\"Microsoft SQL Server Blog\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/sqlserver\" \/>\n<meta property=\"article:published_time\" content=\"2014-02-07T17:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-01-23T06:49:02+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/08\/cropped-microsoft_logo_element.png\" \/>\n\t<meta property=\"og:image:width\" content=\"512\" \/>\n\t<meta property=\"og:image:height\" content=\"512\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"SQL Server Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SQLServer\" \/>\n<meta name=\"twitter:site\" content=\"@SQLServer\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"SQL Server Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 min read\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/\"},\"author\":[{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/author\/sql-server-team\/\",\"@type\":\"Person\",\"@name\":\"SQL Server Team\"}],\"headline\":\"Differential Database Backup with Memory-Optimized Tables\",\"datePublished\":\"2014-02-07T17:00:00+00:00\",\"dateModified\":\"2024-01-23T06:49:02+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/\"},\"wordCount\":1051,\"commentCount\":3,\"publisher\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/\",\"name\":\"Differential Database Backup with Memory-Optimized Tables - Microsoft SQL Server Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website\"},\"datePublished\":\"2014-02-07T17:00:00+00:00\",\"dateModified\":\"2024-01-23T06:49:02+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Differential Database Backup with Memory-Optimized Tables\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\",\"name\":\"Microsoft SQL Server Blog\",\"description\":\"Official News from Microsoft\u2019s Information Platform\",\"publisher\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\",\"name\":\"Microsoft SQL Server Blog\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png\",\"contentUrl\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png\",\"width\":259,\"height\":194,\"caption\":\"Microsoft SQL Server Blog\"},\"image\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"http:\/\/www.facebook.com\/sqlserver\",\"https:\/\/x.com\/SQLServer\",\"https:\/\/www.youtube.com\/user\/MSCloudOS\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Differential Database Backup with Memory-Optimized Tables - Microsoft SQL Server Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/","og_locale":"en_US","og_type":"article","og_title":"Differential Database Backup with Memory-Optimized Tables - Microsoft SQL Server Blog","og_description":"This blog describes how differential database backup is taken on database with one or more memory-optimized tables. For full database backups, please refer to Database Backup with Memory-Optimized Tables. For disk-based tables, the differential database backup includes only the pages that have changed since the last full database backup.","og_url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/","og_site_name":"Microsoft SQL Server Blog","article_publisher":"http:\/\/www.facebook.com\/sqlserver","article_published_time":"2014-02-07T17:00:00+00:00","article_modified_time":"2024-01-23T06:49:02+00:00","og_image":[{"width":512,"height":512,"url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/08\/cropped-microsoft_logo_element.png","type":"image\/png"}],"author":"SQL Server Team","twitter_card":"summary_large_image","twitter_creator":"@SQLServer","twitter_site":"@SQLServer","twitter_misc":{"Written by":"SQL Server Team","Est. reading time":"4 min read"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/#article","isPartOf":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/"},"author":[{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/author\/sql-server-team\/","@type":"Person","@name":"SQL Server Team"}],"headline":"Differential Database Backup with Memory-Optimized Tables","datePublished":"2014-02-07T17:00:00+00:00","dateModified":"2024-01-23T06:49:02+00:00","mainEntityOfPage":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/"},"wordCount":1051,"commentCount":3,"publisher":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization"},"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/","name":"Differential Database Backup with Memory-Optimized Tables - Microsoft SQL Server Blog","isPartOf":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website"},"datePublished":"2014-02-07T17:00:00+00:00","dateModified":"2024-01-23T06:49:02+00:00","breadcrumb":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/07\/differential-database-backup-with-memory-optimized-tables\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/"},{"@type":"ListItem","position":2,"name":"Differential Database Backup with Memory-Optimized Tables"}]},{"@type":"WebSite","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/","name":"Microsoft SQL Server Blog","description":"Official News from Microsoft\u2019s Information Platform","publisher":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization","name":"Microsoft SQL Server Blog","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png","contentUrl":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png","width":259,"height":194,"caption":"Microsoft SQL Server Blog"},"image":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/"},"sameAs":["http:\/\/www.facebook.com\/sqlserver","https:\/\/x.com\/SQLServer","https:\/\/www.youtube.com\/user\/MSCloudOS"]}]}},"msxcm_display_generated_audio":false,"msxcm_animated_featured_image":null,"_links":{"self":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts\/1513","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/users\/1457"}],"replies":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/comments?post=1513"}],"version-history":[{"count":0,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts\/1513\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/media?parent=1513"}],"wp:term":[{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/post_tag?post=1513"},{"taxonomy":"product","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/product?post=1513"},{"taxonomy":"content-type","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/content-type?post=1513"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/topic?post=1513"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/coauthors?post=1513"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}