TSQL -> Backup SQLserver database using TSQL

Here is TSQL script that makes datatabse backup. Current date is concatenated to the .bak file name.

-- Backup Database to .bak File
DECLARE @fileName VARCHAR(90);
DECLARE @db_name VARCHAR(20);
DECLARE @fileDate VARCHAR(20);

SET @fileName = 'D:\backups\'; -- change to the relevant path
SET @db_name = 'my_db';     -- change to the relevant database name
SET @fileDate = CONVERT(VARCHAR(20), GETDATE(),112);
SET @fileName = @fileName + @db_name + '_' + RTRIM(@fileDate) + '.bak';

BACKUP DATABASE @db_name TO DISK = @fileName;

You can save this script to file backup_database.sql and execute it from command line. Like this:

sqlcmd -S SQLEXPRESS\AMPER -i "D:\DBA_scripts\backup_database.sql"