SQL Server Backup

Full Backup

backup database AdventureWorks2019 to disk = 'C:\Windows\AdventureWorks2019_20220602.bak';

If get an error like this:

image-20220606100125253

It means that the SQL server has no permission to the location. To fix this, in the SQL Server Configuration Manager, we can find that the SQL service is logged on as NT Service\MSSQLSERVER.

image-20220606100346465

Then right-click on the folder you want to access, properties --> security, and add permission to the NT Service\MSSQLSERVER.

image-20220606100858530

Backup with differential

This will back up the changed pages from the previous full backup.

-- backup changes start from last full backup, save changed page only
backup database AdventureWorks2019 to disk = 'C:\Users\t-jxie\Downloads\AdventureWorks2019_20220602_2.bak' with differential;

Backup with copy_only

This will leave the DCM (diff change map) unchanged, meaning that if we are back up with differential, it will also backup changes from the last full backup.

-- backup, leave (DCM) diff change map unchanged, compressed
backup database AdventureWorks2019 to disk = 'C:\Users\t-jxie\Downloads\AdventureWorks2019_20220602_2.bak' with copy_only, compression;

The DCM records the changes made to the page of the database, we can view the DCM of the specific database by

dbcc page(5, 1, 6, 2)

image-20220606101710464

Backup log files

backup log AdventureWorks2019 to disk = 'C:\Users\t-jxie\Downloads\AdventureWorks2019_20220602.trn';

We can view the information of log files of databases by

dbcc sqlperf(logspace)

image-20220606101859208