MSSQL Backup

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;

The recommended way to run a backup is by scheduling Ola Halengren's maintenance scripts.

Backup

To run backups by hand...

If you are also running scheduled backups remember to use COPY_ONLY to avoid breaking your differential backup chain

Full...

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

https://www.brentozar.com/blitz/full-recovery-mode-without-log-backups/

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 chain

Options for speeding this up...

To measure how fast SQL Server can write to the backup target disk...

Options for speeding this up...

Third Party Backup Tool Integration