{"id":1533,"date":"2014-02-05T09:00:00","date_gmt":"2014-02-05T17:00:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/dataplatforminsider\/2014\/02\/05\/database-backup-with-memory-optimized-tables\/"},"modified":"2024-01-22T22:49:02","modified_gmt":"2024-01-23T06:49:02","slug":"database-backup-with-memory-optimized-tables","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/02\/05\/database-backup-with-memory-optimized-tables\/","title":{"rendered":"Database Backup with Memory-Optimized Tables"},"content":{"rendered":"

The memory-optimized tables are backed up as part of regular database backups so you don\u2019t need to do anything special to manage backups on databases with memory-optimized tables. Like for disk-based tables, the CHECKSUM of data\/delta files is validated as part of database backup to proactively detect any storage corruption. However, as described in the blog storage allocation and management<\/a>, the storage used by memory-optimized tables can be much larger than its footprint in the memory. \u00a0A full backup of a database with one or more memory-optimized tables consist of the allocated storage for disk-based tables, active transaction log and the data\/delta file pairs (i.e. checkpoint file pairs) for memory-optimized tables. This blog focuses on the size of database backups that you can expect in database with memory-optimized tables. \u00a0<\/p>\n

Full Database Backup<\/h2>\n

For the discussion here, we will focus on the database backups for databases with just durable memory-optimized tables because the backup part for the disk-based tables is the same irrespective of the existence of memory-optimized tables. The data\/delta file pairs, also referred to as Checkpoint File Pairs or CFPs residing in the filegroup could be in various states at a given time. Please refer to merge-operation-in-memory-optimized-tables<\/a> for details. The table below describes what part of the files is backed up both in CTP2 and in RTM. \u00a0<\/p>\n\n\n\n\n\n\n\n\n\n\n\n
CFP State<\/b> <\/td>\n\n

Backup in CTP2<\/b><\/p>\n<\/td>\n

\n

Backup in RTM<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

PRECREATED<\/p>\n<\/td>\n

\n

File metadata only<\/p>\n<\/td>\n

\n

File metadata only<\/p>\n<\/td>\n<\/tr>\n

\n

UNDER CONSTRUCTION<\/p>\n<\/td>\n

\n

File metadata + allocated bytes<\/p>\n<\/td>\n

\n

File metadata only<\/p>\n<\/td>\n<\/tr>\n

\n

ACTIVE<\/p>\n<\/td>\n

\n

File metadata + allocated bytes<\/p>\n<\/td>\n

\n

File metadata + used bytes<\/p>\n<\/td>\n<\/tr>\n

\n

MERGE SOURCE<\/p>\n<\/td>\n

\n

File metadata + allocated bytes<\/p>\n<\/td>\n

\n

File metadata + used bytes<\/p>\n<\/td>\n<\/tr>\n

\n

MERGE TARGET<\/p>\n<\/td>\n

\n

File metadata + allocated bytes<\/p>\n<\/td>\n

\n

File metadata only<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

REQUIRED FOR BACKUP\/HA<\/p>\n<\/td>\n

\n

File metadata + allocated bytes<\/p>\n<\/td>\n

\n

File metadata + used bytes<\/p>\n<\/td>\n<\/tr>\n

\n

IN TRANSITION TO TOMBSTONE<\/p>\n<\/td>\n

\n

File metadata + allocated bytes<\/p>\n<\/td>\n

\n

File metadata only<\/p>\n<\/td>\n<\/tr>\n

\n

TOMBSTONE<\/p>\n<\/td>\n

\n

File metadata + allocated bytes<\/p>\n<\/td>\n

\n

File metadata only<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Table – 1: CFP and Database Backup<\/em><\/p>\n

You will find that the size of database backup in SQL Server 2014 RTMis relatively smaller than what you had in CTP2.<\/p>\n

Let us walk through a few examples to show the size of the backups. All these examples are based on the following database and the table schema using pre-RTM bits. The state of checkpoint file pairs (i.e. CFPs) in the example here please refer to the blog state-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables<\/a>.<\/p>\n

CREATE DATABASE<\/span> imoltp<\/span><\/p>\n

GO<\/span><\/p>\n

ALTER DATABAS<\/span>E imoltp ADD FILEGROUP<\/span> imoltp_mod CONTAINS<\/span> MEMORY_OPTIMIZED_DATA<\/span><\/p>\n

ALTER DATABAS<\/span>E imoltp ADD FILE<\/span> (name=‘imoltp_mod’<\/span>, filename<\/span>=‘c:dataimoltp_mod’<\/span>) TO FILEGROUP<\/span> imoltp_mod<\/span><\/p>\n

GO<\/span><\/p>\n

use<\/span> imoltp<\/span><\/p>\n

go<\/span><\/p>\n

— create the table with each row around 8K<\/span><\/p>\n

CREATE TABLE<\/span> dbo.t_memopt (<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c1 int<\/span> NOT NULL,<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c2 char<\/span>(40) NOT NULL,<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c3 char<\/span>(8000) NOT NULL,<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT<\/span> [pk_t_memopt_c1] PRIMARY KEY NONCLUSTERED HASH<\/span> (c1)<\/span><\/p>\n

WITH<\/span> (BUCKET_COUNT<\/span> = 100000)<\/span><\/p>\n

) WITH<\/span> (MEMORY_OPTIMIZED<\/span> = ON<\/span>, DURABILITY<\/span> = SCHEMA_AND_DATA)<\/span><\/p>\n

Go<\/span><\/p>\n

We will use the following query to look at the states of CFPs<\/p>\n

select<\/span> file_type_desc, state<\/span>, state_desc, internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes, inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn<\/span><\/p>\n

from<\/span> sys<\/span>.dm_db_xtp_checkpoint_files<\/span><\/p>\n

order by<\/span> file_type_desc, upper_bound_tsn<\/span><\/p>\n

Example-1: Backup a database with no rows in memory-optimized tables<\/h3>\n