# Physical Backup-------------------------------
# first stop the server
net stop MSSQLSERVER
# Create a backup directory to store the backup files
mkdir C:\backups\sqlserver
# Create Full Backup
sqlcmd -S localhost -U sa -P password -Q "BACKUP DATABASE MyDatabase TO DISK='C:\backups\sqlserver\MyDatabase.bak' WITH INIT"
# Start the server
net start MSSQLSERVER
# Logical Backup--------------------------------
# create a backup of the "mydatabase" database
sqlcmd -S localhost -U sa -P yourpassword -Q "BACKUP DATABASE mydatabase TO DISK='C:\mydatabase_backup.bak'"
# Point in time Backup-------------------------
# create a backup of the "mydatabase" database up to January 1st, 2022
sqlcmd -S localhost -U sa -P password -Q "BACKUP DATABASE mydatabase TO DISK='C:\mydatabase_backup.bak' WITH NOFORMAT, NOINIT, NAME='mydatabase-full', SKIP, NOREWIND, NOUNLOAD, STATS=10, BEFORE DATETIME='2022-01-01T12:00:00'"
# Differential Backup-----------------------------
# take a full backup
BACKUP DATABASE dbname TO DISK='C:\backup\full_backup.bak' WITH INIT,FORMAT
# take a differential backup
BACKUP DATABASE dbname TO DISK='C:\backup\diff_backup.bak' WITH DIFFERENTIAL,FORMAT
# Incremental Backup-----------------------------
# take a full backup
BACKUP DATABASE dbname TO DISK='C:\full_backup.bak' WITH INIT
# take an incremental backup
BACKUP LOG dbname TO DISK='C:\incremental_backup.trn' WITH INIT