Teguh Triharto Learning Center: .::: Create Script Backup All Database Full, Differential & Log Using T-SQL & Run using CMD SQL Server

correlation https://teguhth.blogspot.com/2023/08/create-script-backup-database-full.html

1. enable xp_cmdshell

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

2. create t-sql sp_backup_full_basic

IF DB_ID('dbatools') IS NULL
BEGIN
CREATE DATABASE [dbatools];
END
GO

USE [dbatools];
GO

CREATE PROCEDURE [dbo].[sp_backup_full_basic]

-- =============================================
-- Author: Teguh Triharto
-- Create date: 22 March 2000
-- Description: Script Backup Database
-- Website : https://www.linkedin.com/in/teguhth"
-- =============================================

AS
BEGIN

DECLARE @DatabaseName NVARCHAR(255)
DECLARE @BackupBasePath NVARCHAR(500)
DECLARE @BackupDateFolder NVARCHAR(500)
DECLARE @BackupFolder NVARCHAR(500)
DECLARE @BackupFile NVARCHAR(500)
DECLARE @Command NVARCHAR(1000)
DECLARE @DateFolder NVARCHAR(20)
DECLARE @BackupStatus NVARCHAR(50)
DECLARE @BackupSize BIGINT
DECLARE @BackupSizeMB DECIMAL(18,2) -- Ukuran dalam MB

-- Set lokasi penyimpanan backup utama (ubah sesuai kebutuhan)
SET @BackupBasePath = 'C:\BackupDaily\'

---SET @BackupBasePath = '\\10.10.10.90\home\mounting'

-- Tentukan folder berdasarkan tanggal (YYYYMMDD)
SET @DateFolder = FORMAT(GETDATE(), 'yyyyMMdd')
SET @BackupDateFolder = @BackupBasePath + @DateFolder + '\'

-- Buat folder berdasarkan tanggal jika belum ada
SET @Command = 'IF NOT EXIST "' + @BackupDateFolder + '" mkdir "' + @BackupDateFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT

-- Cursor untuk mendapatkan semua database kecuali sistem database
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') -- Hindari backup database sistem

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
-- Tentukan folder khusus untuk database di dalam folder tanggal
SET @BackupFolder = @BackupDateFolder + @DatabaseName + '\'

-- Buat folder database dalam folder tanggal jika belum ada
SET @Command = 'IF NOT EXIST "' + @BackupFolder + '" mkdir "' + @BackupFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT

-- Set nama file backup
SET @BackupFile = @BackupFolder +'Full_'+ @DatabaseName + '_'+ FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.bak'

-- Perintah Backup Full
PRINT 'Backing Up Full : ' + @DatabaseName + ' to ' + @BackupFile

BEGIN TRY
-- Backup Database
BACKUP DATABASE @DatabaseName
TO DISK = @BackupFile
WITH INIT, COMPRESSION, FORMAT, STATS = 10

-- Set status berhasil jika backup sukses
SET @BackupStatus = 'Success'
END TRY
BEGIN CATCH
-- Set status gagal jika ada error
SET @BackupStatus = 'Failed'
SET @BackupSizeMB = NULL
END CATCH

FETCH NEXT FROM db_cursor INTO @DatabaseName
END

-- Tutup cursor
CLOSE db_cursor
DEALLOCATE db_cursor
END
GO

3. create t-sql sp_backup_diff_basic

IF DB_ID('dbatools') IS NULL
BEGIN
CREATE DATABASE [dbatools];
END
GO

USE [dbatools]
GO

CREATE PROCEDURE [dbo].[sp_backup_diff_basic]

-- =============================================
-- Author: Teguh Triharto
-- Create date: 22 March 2000
-- Description: Script Backup Database
-- Website : https://www.linkedin.com/in/teguhth"

AS
BEGIN

DECLARE @DatabaseName NVARCHAR(255)
DECLARE @BackupBasePath NVARCHAR(500)
DECLARE @BackupDateFolder NVARCHAR(500)
DECLARE @BackupFolder NVARCHAR(500)
DECLARE @BackupFile NVARCHAR(500)
DECLARE @Command NVARCHAR(1000)
DECLARE @DateFolder NVARCHAR(20)
DECLARE @BackupStatus NVARCHAR(50)
DECLARE @BackupSize BIGINT
DECLARE @BackupSizeMB DECIMAL(18,2) -- Ukuran dalam MB

-- Set lokasi penyimpanan backup utama (ubah sesuai kebutuhan)
SET @BackupBasePath = 'C:\BackupDaily\'

---SET @BackupBasePath = '\\10.10.10.90\home\mounting'

-- Tentukan folder berdasarkan tanggal (YYYYMMDD)
SET @DateFolder = FORMAT(GETDATE(), 'yyyyMMdd')
SET @BackupDateFolder = @BackupBasePath + @DateFolder + '\'

-- Buat folder berdasarkan tanggal jika belum ada
SET @Command = 'IF NOT EXIST "' + @BackupDateFolder + '" mkdir "' + @BackupDateFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT

-- Cursor untuk mendapatkan semua database kecuali sistem database
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') -- Hindari backup database sistem

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
-- Tentukan folder khusus untuk database di dalam folder tanggal
SET @BackupFolder = @BackupDateFolder + @DatabaseName + '\'

-- Buat folder database dalam folder tanggal jika belum ada
SET @Command = 'IF NOT EXIST "' + @BackupFolder + '" mkdir "' + @BackupFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT

-- Set nama file backup
SET @BackupFile = @BackupFolder + 'Diff_' + @DatabaseName + '_'+ FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.bak'

-- Perintah Backup Diff
PRINT 'Backing Up Diff : ' + @DatabaseName + ' to ' + @BackupFile

BEGIN TRY
-- Backup Database
BACKUP DATABASE @DatabaseName
TO DISK = @BackupFile
WITH DIFFERENTIAL,INIT, COMPRESSION, FORMAT, STATS = 10

-- Set status berhasil jika backup sukses
SET @BackupStatus = 'Success'
END TRY
BEGIN CATCH
-- Set status gagal jika ada error
SET @BackupStatus = 'Failed'
SET @BackupSizeMB = NULL
END CATCH

FETCH NEXT FROM db_cursor INTO @DatabaseName
END

-- Tutup cursor
CLOSE db_cursor
DEALLOCATE db_cursor
END
GO

4. create t-sql sp_backup_log_basic

IF DB_ID('dbatools') IS NULL
BEGIN
CREATE DATABASE [dbatools];
END
GO

USE [dbatools];
GO

CREATE PROCEDURE [dbo].[sp_backup_log_basic]
-- =============================================
-- Author: Teguh Triharto
-- Create date: 22 March 2000
-- Description: Script Backup Database
-- Website : https://www.linkedin.com/in/teguhth"

AS
BEGIN

DECLARE @DatabaseName NVARCHAR(255)
DECLARE @BackupBasePath NVARCHAR(500)
DECLARE @BackupDateFolder NVARCHAR(500)
DECLARE @BackupFolder NVARCHAR(500)
DECLARE @BackupFile NVARCHAR(500)
DECLARE @Command NVARCHAR(1000)
DECLARE @DateFolder NVARCHAR(20)
DECLARE @BackupStatus NVARCHAR(50)
DECLARE @BackupSize BIGINT
DECLARE @BackupSizeMB DECIMAL(18,2) -- Ukuran dalam MB

-- Set lokasi penyimpanan backup utama (ubah sesuai kebutuhan)
SET @BackupBasePath = 'C:\BackupDaily\'

---SET @BackupBasePath = '\\10.10.10.90\home\mounting'

-- Tentukan folder berdasarkan tanggal (YYYYMMDD)
SET @DateFolder = FORMAT(GETDATE(), 'yyyyMMdd')
SET @BackupDateFolder = @BackupBasePath + @DateFolder + '\'

-- Buat folder berdasarkan tanggal jika belum ada
SET @Command = 'IF NOT EXIST "' + @BackupDateFolder + '" mkdir "' + @BackupDateFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT

-- Cursor untuk mendapatkan semua database kecuali sistem database
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') -- Hindari backup database sistem

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
-- Tentukan folder khusus untuk database di dalam folder tanggal
SET @BackupFolder = @BackupDateFolder + @DatabaseName + '\'

-- Buat folder database dalam folder tanggal jika belum ada
SET @Command = 'IF NOT EXIST "' + @BackupFolder + '" mkdir "' + @BackupFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT

-- Set nama file backup
SET @BackupFile = @BackupFolder +'Log_'+ @DatabaseName + '_'+ FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.trn'

-- Perintah Backup Log
PRINT 'Backing Up Log : ' + @DatabaseName + ' to ' + @BackupFile

BEGIN TRY
-- Backup Database
BACKUP LOG @DatabaseName
TO DISK = @BackupFile
WITH INIT, COMPRESSION, FORMAT, STATS = 10

-- Set status berhasil jika backup sukses
SET @BackupStatus = 'Success'
END TRY
BEGIN CATCH
-- Set status gagal jika ada error
SET @BackupStatus = 'Failed'
SET @BackupSizeMB = NULL
END CATCH

FETCH NEXT FROM db_cursor INTO @DatabaseName
END

-- Tutup cursor
CLOSE db_cursor
DEALLOCATE db_cursor
END
GO

5. execute sp_backup

exec [dbatools].[dbo].[sp_backup_full_basic]
exec [dbatools].[dbo].[sp_backup_diff_basic]
exec [dbatools].[dbo].[sp_backup_log_basic]

6. check result in disk