Today's question (by Steve Jones - SSC Editor):

Accessing Always Encrypted Data

SELECT CustomerID
     , CustomerName
     , CustomerEmail
     , SecureCreditLimit
     , Active FROM customers

CustomerID  CustomerName    CustomerEmail SecureCreditLimit Active
----------- --------------- ------------- ----------------- -------
1           Steve           0x01AB3C223A6 0x018E0FB43ED     1
2           Andy            0x01B1B7A29F3 0xE060FAB9FED     1 
3           Kyle            0x01309196F71 0x01DCF7F90041E9  0

Think you know the answer? Click here, and find out if you are right.

Temporary data and Differential Backup size

I fill and empty some temporary staging tables inside my database between a FULL backup and a DIFFERENTIAL backup. Will this increase my differential backup size?

This is the type of work I am doing:

IF DB_ID('test') IS NULL CREATE DATABASE test
GO
USE test
GO
-- each row will be 4011 byte long, so only 2 records will fit onto one page
CREATE TABLE dbo.test_for_backup (id INT NOT NULL IDENTITY PRIMARY KEY, txt CHAR(4000) NOT NULL)
GO
-- create the FULL backup
BACKUP DATABASE [test] TO DISK = N'C:MSSQLBackuptest1.bak' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

-- insert 10k records, it will fill 5000 pages a 8kB = 40,000 kB = 3.9 MB
INSERT INTO dbo.test_for_backup (txt)
SELECT CAST(ROW_NUMBER() OVER (ORDER BY t1.n) AS VARCHAR(10)) AS txt
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t1(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t2(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t3(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t4(n)

-- remove the records again
TRUNCATE TABLE dbo.test_for_backup

-- what do you think, how many pages the DIFFERENTIAL backup will process?
BACKUP DATABASE [test] TO DISK = N'C:MSSQLBackuptest_diff.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Answer: No, only a few (about 120-150) system pages will be processed

Explanation: A differential backup will process only modified pages. When you insert the records 5k pages will be modified, but when you delete / truncate the rows again, the same 5k pages will be unallocated again (and removed from the dirty-pages-list). So there are no modified pages (from your staging table) that needs to be backed up and the DIFFERENTIAL backup will process only about 120 to 150 system internal pages, but not the 5k temporary storage. In reverse this means, that - when you restore a database from a FULL / DIFFERENTIAL backup - the physical file on the HDD/SDD will be different from the original one (since 5k pages were written to the drive after the insert, but not overwritten with x00 after deleting / truncating the table). So if you restore the database with another name, shut down the SQL server and compare the *.mdb files, you will find a lot of differences in the spaces, that are not allocated at the moment. Nice to know: if you create a FULL backup, insert the rows, create a DIFF backup (will process ~ 5150 pages this time), delete or truncate the table and create another DIFF backup, the second DIFF will process again only the 150 system pages (e.g. the GAM / SGAM pages, where it stores, which pages are allocated at the moment and where it had just disallocated the 5k pages) Ref: Backup - https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.