MSSQL Backup
The recommended way to run a backup is by scheduling Ola Halengren's maintenance scripts.
Check
SELECT TOP (1000)
database_creation_date,
backup_start_date,
backup_finish_date,
type,
backup_size,
database_name,
server_name,
machine_name,
recovery_model,
is_snapshot,
is_copy_only
FROM [msdb].[dbo].[backupset]
ORDER BY backup_start_date DESC
Backup History for a Specific Database
SELECT b.database_name,
b.backup_start_date,
b.backup_finish_date,
a.physical_device_name
FROM [msdb].[dbo].[backupmediafamily] a,
[msdb].[dbo].[backupset] b
WHERE a.media_set_id = b.media_set_id
AND b.database_name = 'myDatabase'
ORDER BY b.database_name, b.backup_finish_date;
SELECT CONVERT(CHAR(100),
SERVERPROPERTY('Servername')) AS [Server],
bs.database_name AS [Database Name],
bs.backup_start_date AS [Backup Start Date],
bs.backup_finish_date AS [Backup Finish Date],
bs.expiration_date AS [Backup Expiration Date],
CASE bs.type
WHEN 'D' THEN 'Database (Full)'
WHEN 'I' THEN 'Incremental (Differential)'
WHEN 'L' THEN 'Log'
END AS [Backup Type],
bs.backup_size AS [Backup Size],
bmf.logical_device_name AS [Logical Device],
bmf.physical_device_name AS [Physical Device],
bs.name AS [Backupset Name],
bs.description,
bs.is_snapshot,
bs.is_copy_only
FROM [msdb].[dbo].[backupmediafamily] bmf
INNER JOIN [msdb].[dbo].[backupset] bs ON bmf.media_set_id = bs.media_set_id
WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY bs.database_name,
bs.backup_finish_date
Backup
To run backups by hand...
If you are also running scheduled backups remember to use COPY_ONLY to avoid breaking your differential backup chainFull...
BACKUP DATABASE myDatabase
TO DISK=N'G:\SQL Backups\myDatabase\myDatabase_Full.bak' COPY_ONLY;
Differential...
BACKUP DATABASE myDatabase
TO DISK=N'G:\SQL Backups\myDatabase\myDatabase_Diff.bak'
WITH DIFFERENTIAL;
To multiple files...
BACKUP DATABASE myDatabase
TO DISK=N'G:\SQL Backups\myDatabase\myDatabase_Full_1.bak',
DISK=N'H:\SQL Backups\myDatabase\myDatabase_Full_2.bak',
DISK=N'I:\SQL Backups\myDatabase\myDatabase_Full_3.bak',
DISK=N'J:\SQL Backups\myDatabase\myDatabase_Full_4.bak'
WITH Name = 'MyBackup';
Recovery Model
SIMPLE - Does not allow point in time recovery. Transaction Log usage is cyclical.
FULL - Allows point in time recovery. Transaction Log backups are required in order to avoid filing the transaction log location.
Backup Performance
To measure how fast SQL Server can read the database from disk...
BACKUP DATABASE MyDb TO DISK='NUL:' COPY_ONLY
Without using COPY_ONLY you will break your differential backup chainOptions for speeding this up...
Faster storage
Data Compression (Enterprise Edition)
Avoid contention by reading from a lightly used AlwaysOn Availability Group Replica
Do weekly Full backups and daily Differentials (instead of Daily Full backups)
To measure how fast SQL Server can write to the backup target disk...
Use a disk benchmarking tool
Compare backups to local storage against backups to tools like TSM (i.e. know whether it is TSM slowing the backup down)
Options for speeding this up...
Backup Compression (but can impact CPU)
Faster storage
Parallelism
Third Party Backup Tool Integration
Bibliography
https://www.brentozar.com/archive/2020/08/back-up-sql-server-43-67-faster-by-writing-to-multiple-files/https://www.brentozar.com/archive/2015/12/how-to-make-sql-server-backups-go-faster/https://www.brentozar.com/archive/2014/01/improving-the-performance-of-backups/https://www.brentozar.com/blitz/full-recovery-mode-without-log-backups/
https://sirsql.net/2012/12/13/20121212automated-backup-tuning/
https://dba.stackexchange.com/questions/44889/simple-or-full-recovery-model-for-databaseshttps://dba.stackexchange.com/questions/185834/use-of-third-party-vss-backup-plus-native-sql-backup?rq=1https://dba.stackexchange.com/questions/184977/sql-server-backup-versus-third-party-snapshot-based-backup-using-vss?rq=1
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql?view=sql-server-ver15
https://www.sqlskills.com/blogs/paul/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup/https://www.mssqltips.com/sqlservertip/5655/script-to-delete-old-sql-server-backup-files-using-msdb-backup-history-data/
https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/backupmediafamily-transact-sqlhttps://learn.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql
Backup Compressionhttp://henkvandervalk.com/how-to-increase-sql-database-full-backup-speed-using-compression-and-solid-state-diskshttps://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server
Arcservehttps://community.spiceworks.com/topic/1795082-question-about-arcserve-unified-data-protection-db-log-truncation