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

7. Special backup for SQL2008 sp_backup_full_basic

USE [dbatools]
GO

/****** Object: StoredProcedure [dbo].[sp_backup_full_basic] Script Date: 2/23/2026 2:33:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_backup_full_basic]
AS
BEGIN
SET NOCOUNT ON;

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 @SQL NVARCHAR(MAX)

-- Base Path
SET @BackupBasePath = 'C:\BackupDaily\'

-- Date Folder
SET @DateFolder = CONVERT(VARCHAR(8), GETDATE(), 112)
SET @BackupDateFolder = @BackupBasePath + @DateFolder + '\'

-- Create Date Folder
SET @Command = 'IF NOT EXIST "' + @BackupDateFolder + '" mkdir "' + @BackupDateFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT

-- Cursor (ONLINE database only)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND state_desc = 'ONLINE'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @BackupFolder = @BackupDateFolder + @DatabaseName + '\'

SET @Command = 'IF NOT EXIST "' + @BackupFolder + '" mkdir "' + @BackupFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT

SET @BackupFile = @BackupFolder +
'Full_' + @DatabaseName + '_' +
CONVERT(VARCHAR(8), GETDATE(), 112) + '_' +
REPLACE(CONVERT(VARCHAR(8), GETDATE(),108),':','') +
'.bak'

PRINT 'Backing Up : ' + @DatabaseName

BEGIN TRY

SET @SQL = 'BACKUP DATABASE [' + @DatabaseName + ']
TO DISK = ''' + @BackupFile + '''
WITH INIT, FORMAT,STATS = 10'

EXEC(@SQL)

PRINT 'SUCCESS : ' + @DatabaseName

END TRY
BEGIN CATCH

PRINT 'FAILED : ' + @DatabaseName
PRINT ERROR_MESSAGE()

END CATCH

FETCH NEXT FROM db_cursor INTO @DatabaseName
END

CLOSE db_cursor
DEALLOCATE db_cursor
END
GO

8. Special backup for SQL2008 sp_backup_diff_basic

USE [dbatools]
GO

/****** Object: StoredProcedure [dbo].[sp_backup_diff_basic] Script Date: 2/23/2026 2:39:51 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_backup_diff_basic]
AS
BEGIN
SET NOCOUNT ON;

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 @SQL NVARCHAR(MAX)

-- Base Path
SET @BackupBasePath = 'C:\BackupDaily\'

-- Date Folder
SET @DateFolder = CONVERT(VARCHAR(8), GETDATE(), 112)
SET @BackupDateFolder = @BackupBasePath + @DateFolder + '\'

-- Create Date Folder
SET @Command = 'IF NOT EXIST "' + @BackupDateFolder + '" mkdir "' + @BackupDateFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT

-- Cursor (ONLINE database only)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND state_desc = 'ONLINE'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @BackupFolder = @BackupDateFolder + @DatabaseName + '\'

SET @Command = 'IF NOT EXIST "' + @BackupFolder + '" mkdir "' + @BackupFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT

SET @BackupFile = @BackupFolder +
'Diff_' + @DatabaseName + '_' +
CONVERT(VARCHAR(8), GETDATE(), 112) + '_' +
REPLACE(CONVERT(VARCHAR(8), GETDATE(),108),':','') +
'.bak'

PRINT 'Backing Up : ' + @DatabaseName

BEGIN TRY

SET @SQL = 'BACKUP DATABASE [' + @DatabaseName + ']
TO DISK = ''' + @BackupFile + '''
WITH DIFFERENTIAL,INIT, FORMAT,STATS = 10'

EXEC(@SQL)

PRINT 'SUCCESS : ' + @DatabaseName

END TRY
BEGIN CATCH

PRINT 'FAILED : ' + @DatabaseName
PRINT ERROR_MESSAGE()

END CATCH

FETCH NEXT FROM db_cursor INTO @DatabaseName
END

CLOSE db_cursor
DEALLOCATE db_cursor
END
GO

9. Special backup for SQL2008 sp_backup_log_basic

USE [dbatools]
GO

/****** Object: StoredProcedure [dbo].[sp_backup_log_basic] Script Date: 2/23/2026 3:29:04 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_backup_log_basic]
AS
BEGIN
SET NOCOUNT ON;

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 @SQL NVARCHAR(MAX)

-- Base Path
SET @BackupBasePath = 'C:\BackupDaily\'

-- Date Folder
SET @DateFolder = CONVERT(VARCHAR(8), GETDATE(), 112)
SET @BackupDateFolder = @BackupBasePath + @DateFolder + '\'

-- Create Date Folder
SET @Command = 'IF NOT EXIST "' + @BackupDateFolder + '" mkdir "' + @BackupDateFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT

-- Cursor (ONLINE database only)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND state_desc = 'ONLINE'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @BackupFolder = @BackupDateFolder + @DatabaseName + '\'

SET @Command = 'IF NOT EXIST "' + @BackupFolder + '" mkdir "' + @BackupFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT

SET @BackupFile = @BackupFolder +
'Log_' + @DatabaseName + '_' +
CONVERT(VARCHAR(8), GETDATE(), 112) + '_' +
REPLACE(CONVERT(VARCHAR(8), GETDATE(),108),':','') +
'.bak'

PRINT 'Backing Up : ' + @DatabaseName

BEGIN TRY

SET @SQL = 'BACKUP LOG [' + @DatabaseName + ']
TO DISK = ''' + @BackupFile + '''
WITH INIT,FORMAT, STATS = 10'

EXEC(@SQL)

PRINT 'SUCCESS : ' + @DatabaseName

END TRY
BEGIN CATCH

PRINT 'FAILED : ' + @DatabaseName
PRINT ERROR_MESSAGE()

END CATCH

FETCH NEXT FROM db_cursor INTO @DatabaseName
END

CLOSE db_cursor
DEALLOCATE db_cursor
END
GO

Teguh Triharto Learning Center

Blog List

Friday, December 19, 2025

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

No comments:

Post a Comment

Popular Posts