.::: Create Script Backup Database Full, Differential & Log Using T-SQL & Run using CMD SQL Server :::.

1. Create Script Backup Full > sp_backup_full


CREATE procedure [dbo].[sp_backup_full]
as
begin
DECLARE @BackupPath NVARCHAR(255);
DECLARE @DateSuffix NVARCHAR(20);
DECLARE @BackupFileName NVARCHAR(255);
declare @DBName NVARCHAR(255);
-- Replace 'YourDatabaseName' with the name of your database
SET @DBName ='teguhth'
SET @BackupPath = 'C:\backupdb_full\' -- Replace 'C:\YourBackupFolder\' with the desired backup folder path
 
-- Generate the current date suffix in the format YYYYMMDD_HHMMSS
SET @DateSuffix = REPLACE(REPLACE(CONVERT(NVARCHAR(20), GETDATE(), 120), '-', ''), ' ', '_');
SET @DateSuffix = REPLACE(REPLACE(@DateSuffix, ':', ''), '.', '');

-- Create a folder based on the date if it doesn't exist
DECLARE @FolderPath NVARCHAR(255);
--SET @FolderPath = @BackupPath + @DateSuffix + '\';
SET @FolderPath = @BackupPath + '\';

EXEC master.dbo.xp_create_subdir @FolderPath;

-- Generate the backup file name with the current date suffix
SET @BackupFileName = @FolderPath + 'teguhth_full_backup_' + @DateSuffix + '.bak';
-- SET @BackupFileName = 'teguhth_full_backup_' + @DateSuffix + '.bak';

-- Perform the full database backup
BACKUP DATABASE @DBName
TO DISK = @BackupFileName
WITH INIT, FORMAT, COMPRESSION; -- 'INIT' overwrites the backup media, 'FORMAT' reinitializes the backup set, 'COMPRESSION' enables backup compression

end

GO

2. Create Script Backup log > sp_backup_log

CREATE procedure [dbo].[sp_backup_log]
as
begin
DECLARE @BackupPath NVARCHAR(255);
DECLARE @DateSuffix NVARCHAR(20);
DECLARE @BackupFileName NVARCHAR(255);
declare @DBName NVARCHAR(255);
-- Replace 'YourDatabaseName' with the name of your database
SET @DBName ='teguhth'
SET @BackupPath = 'C:\backupdb_log\' -- Replace 'C:\YourBackupFolder\' with the desired backup folder path

-- Generate the current date suffix in the format YYYYMMDD_HHMMSS
SET @DateSuffix = REPLACE(REPLACE(CONVERT(NVARCHAR(20), GETDATE(), 120), '-', ''), ' ', '_');
SET @DateSuffix = REPLACE(REPLACE(@DateSuffix, ':', ''), '.', '');

-- Create a folder based on the date if it doesn't exist
DECLARE @FolderPath NVARCHAR(255);
--SET @FolderPath = @BackupPath + @DateSuffix + '\';
SET @FolderPath = @BackupPath + '\';

EXEC master.dbo.xp_create_subdir @FolderPath;

-- Generate the backup file name with the current date suffix
SET @BackupFileName = @FolderPath + 'teguhth_full_backup_' + @DateSuffix + '.trn';
-- SET @BackupFileName = 'teguhth_full_backup_' + @DateSuffix + '.bak';

-- Perform the log database backup
BACKUP LOG @DBName
TO DISK = @BackupFileName
WITH INIT, FORMAT, COMPRESSION; -- 'INIT' overwrites the backup media, 'FORMAT' reinitializes the backup set, 'COMPRESSION' enables backup compression

end
GO

3. Create Script Backup differential > sp_backup_diff

CREATE procedure [dbo].[sp_backup_diff]
as
begin
DECLARE @BackupPath NVARCHAR(255);
DECLARE @DateSuffix NVARCHAR(20);
DECLARE @BackupFileName NVARCHAR(255);
declare @DBName NVARCHAR(255);
-- Replace 'YourDatabaseName' with the name of your database
SET @DBName ='teguhth'
SET @BackupPath = 'C:\backupdb_diff\' -- Replace 'C:\YourBackupFolder\' with the desired backup folder path

-- Generate the current date suffix in the format YYYYMMDD_HHMMSS
SET @DateSuffix = REPLACE(REPLACE(CONVERT(NVARCHAR(20), GETDATE(), 120), '-', ''), ' ', '_');
SET @DateSuffix = REPLACE(REPLACE(@DateSuffix, ':', ''), '.', '');

-- Create a folder based on the date if it doesn't exist
DECLARE @FolderPath NVARCHAR(255);
--SET @FolderPath = @BackupPath + @DateSuffix + '\';
SET @FolderPath = @BackupPath + '\';

EXEC master.dbo.xp_create_subdir @FolderPath;

-- Generate the backup file name with the current date suffix
SET @BackupFileName = @FolderPath + 'teguhth_diff_backup_' + @DateSuffix + '.bak';
-- SET @BackupFileName = 'teguhth_full_backup_' + @DateSuffix + '.bak';
-- Perform the log database backup

BACKUP DATABASE @DBName
TO DISK = @BackupFileName
WITH DIFFERENTIAL, INIT, FORMAT, COMPRESSION; -- 'INIT' overwrites the backup media, 'FORMAT' reinitializes the backup set, 'COMPRESSION' enables backup compression

end


GO

4. example run via cmd

sqlcmd -E -S mssql -d teguhth -Q "select getdate()as start;exec sp_backup_full;select getdate() as Finish" > C:\backupdb_full\log_sp_backup_full.txt
sqlcmd -E -S mssql -d teguhth -Q "select getdate()as start;exec sp_backup_log;select getdate() as Finish"  > C:\backupdb_log\log_sp_backup_log.txt
sqlcmd -E -S mssql -d teguhth -Q "select getdate()as start;exec sp_backup_diff;select getdate() as Finish" > C:\backupdb_diff\log_sp_backup_diff.txt

5. check result backup
 

6. check result log txt