Native Backup Database From Script

Full Database Backup

BACKUP DATABASE [MyDB] 
TO  DISK = N'D:\DBBackups\MyDB_Full_20200101.bak' WITH NOFORMAT, NOINIT, NAME = N'Full Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Output

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 92256 pages for database ‘MyDB’, file ‘MyDB’ on file 1.
100 percent processed.
Processed 3 pages for database ‘MyDB’, file ‘MyDB_log’ on file 1.
BACKUP DATABASE successfully processed 92259 pages in 54.588 seconds (13.203 MB/sec).

Transaction Log Backup

BACKUP LOG [MyDB] TO  DISK = N'D:\DBBackups\MyDB_Log_20200101.trn' WITH NOFORMAT, NOINIT, NAME = N'Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Note: Transaction Log backups are for database in “Full” recovery model. You will get the following error message if you try to do a transaction log backup on a “Simple” recovery model database.

Msg 4208, Level 16, State 1, Line 6
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Msg 3013, Level 16, State 1, Line 6
BACKUP LOG is terminating abnormally.

If you don’t care about the transaction log backup file, just set the file to null.

BACKUP LOG [MyDB] TO DISK='NUL:' WITH NO_CHECKSUM, CONTINUE_AFTER_ERROR

Use [MyDB]

DBCC SHRINKFILE (MyDB_log, EMPTYFILE);

Differential Backup

BACKUP DATABASE [MyDB] 
TO  DISK = N'D:\DBBackups\MyDB_DIFF_20200729_01.DIF' WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'Differential Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Backup Parameters

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15

SKIP

If the volume contains a valid media header, appends the backup set, preserving all existing backup sets.

NOREWIND

Specifies that SQL Server will keep the tape open after the backup operation. You can use this option to help improve performance when performing multiple backup operations to a tape.

NOREWIND implies NOUNLOAD, and these options are incompatible within a single BACKUP statement.

STATS

The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server?view=sql-server-ver15