SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_FolderExist       Script Date: 2026-05-15 19:31:09.333 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_FolderExist]')) DROP FUNCTION [dbo].[fun_FolderExist];
GO


CREATE FUNCTION [dbo].[fun_FolderExist] (
  @dirname VARCHAR(265))
RETURNS BIT
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Check if folder exists.

Return value (BIT):
 0 (False) - Folder does not exist
 1 (True)  - Folder exists

EXAMPLES
SELECT dbo.fun_FolderExist('C:\Program Files')
1

SELECT dbo.fun_FolderExist('C:\xxxx')
0

-- IIF is available starting with SQL Server 2012
SELECT IIF(dbo.fun_FolderExist('C:\Program Files')=1,'Yes','No')
Yes

HISTORY
2015-11-24 - Created function
2024-02-09 - Changed history table
2026-01-23 - Changed return value type to BIT
2026-03-30 - Changed examples

TAGS
<program>
  <description>Check if folder exists</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2015-11-24</created>
  <lastmodified>2026-03-30</lastmodified>
</program>
*/
  DECLARE @fso INT
  DECLARE @hr INT
  DECLARE @ofolder INT
  DECLARE @name VARCHAR(265)
  DECLARE @result BIT

  EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT;
  EXEC @hr = sp_OAMethod @fso, 'FolderExists', @ofolder OUT, @dirname;
  EXEC @hr = sp_OADestroy @fso;

  SET @result = @ofolder;
  RETURN @result;
END


GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_uniquename       Script Date: 2026-05-15 19:31:09.333 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_uniquename]')) DROP FUNCTION [dbo].[fun_uniquename];
GO


CREATE FUNCTION [dbo].[fun_uniquename] ()
RETURNS VARCHAR(25)
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Return an unique name with format yyyy_mm_dd_hhmiss_nnnnnnn.

yyyy=year, mm=month, dd=day, hh=hour(24), mi=minutes, ss=seconds, nnnnnnn=nanoseconds

This function can be used to generate a unique name for a backupfile.
See example.

EXAMPLE
SELECT 'TEST_backup_' + dbo.fun_uniquename() + '.bak'
TEST_backup_2024_02_10_164919_7874167.bak

HISTORY
2000-01-01 - Created function
2024-02-09 - Changed history table

TAGS
<program>
  <description>Return an unique name with format yyyy_mm_dd_hhmiss_nnnnnnn</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2000-01-01</created>
  <lastmodified>2024-02-09</lastmodified>
</program>
*/
  DECLARE @time DATETIME2
  DECLARE @uniquename VARCHAR(25)

  SET @time = SYSDATETIME();
  SET @uniquename = REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR, @time, 20),'-','_'),':',''),' ','_') +
    '_' +  RIGHT('0000000'+CONVERT(NVARCHAR,DATEPART(NANOSECOND, @time)/100),7);

  RETURN @uniquename;
END


GO

/****** Object:  SQL_STORED_PROCEDURE  prc_backup_log       Script Date: 2026-05-15 19:31:09.333 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_backup_log]')) DROP PROCEDURE [dbo].[prc_backup_log];
GO

CREATE PROCEDURE [dbo].[prc_backup_log] (
  @dbname NVARCHAR(128),
  @backup_path VARCHAR(265),
  @subdir BIT = 1,
  @options VARCHAR(128) = '',
  @verify BIT = 0) AS
BEGIN
/*
SQLServer Stored Procedure

PURPOSE
Make a transaction log backup for a given database to a given backuppath (on disk).
The backupfile name format is:
{dbname}_backup_{datetime}.trn, where
  {datetime} = 'yyyy_mm_dd_hhMMss_nanosec' (standard backup filename format).

Please run this script with sufficient backup permissions.

This procedure is tested on SQL Server releases 2012 to 2022

PARAMETERS
 - 1 @dbname       NVARCHAR(128)                Name of the database.
 - 2 @backup_path  VARCHAR(265)                 Path to write backupfile(s) to.
 - 3 @subdir       BIT           0/1 (default)  Write backupfile to subdirectory with database name (default). Value 0 means no subdirectory.
 - 5 @options      VARCHAR(128)                 Pass WITH options to BACKUP DATABASE command (comma separated).
 - 6 @verify       BIT           0 (default)/1  Perform restore verify after backup. Value 0 (default) means no restore to verify backup.

Frequently used WITH options:
 - COMPRESSION / NO_COMPRESSION                 The default value depends on server default.
 - CHECKSUM / NO_CHECKSUM                       WITH CHECKSUM is default for compressed backup.

USED OBJECTS
 - dbo.fun_FolderExist          (UDF in local database)
 - dbo.fun_uniquename (UDF in local database)
 - master.dbo.xp_create_subdir
 - msdb..backupset

SQLSERVER CONFIGURATION
 - OLE Automation has to be enabled.

EXAMPLES
-- 1) Log backup to file on subdirectory with database name
EXEC dbo.prc_backup_log @dbname='DBGERRIT', @backup_path='C:\Data\MSSQL\BACKUP'
-- Backup is written to C:\Data\MSSQL\Backup\DBGERRIT\PCF_backup_2011_01_28_091500_1234567.trn

HISTORY
2011-04-28 - Created procedure
2014-05-12 - @backup_path may end with '\*', which means write backupfile to subfolder
2014-08-14 - RAISERROR on parameter errors
2016-09-19 - Parameter @backup_path shortened from 4000 to 265, because no multiple directories (splits) are possible
2023-03-13 - Comment updated for releases
2025-05-24 - Use fun_uniquename
           - Changed error messages
2026-03-28 - Handle messages with RAISERROR
2026-04-03 - Changed parameter @bakdir to @backup_path
           - Added parameter @subdir

TAGS
<program>
  <description>Make a transaction log backup for a given database</description>
  <generic>1</generic>
  <minversion>11</minversion>
  <author>Gerrit Mantel</author>
  <created>2011-04-28</created>
  <lastmodified>2026-04-03</lastmodified>
</program>
*/
  SET NOCOUNT ON

  DECLARE @bakfile VARCHAR(265) = ''
  DECLARE @disk NVARCHAR(4000) = ''
  DECLARE @cmd NVARCHAR(MAX)
  DECLARE @backup_set_id INT
  DECLARE @message VARCHAR(512)

  SET @message = 'Executing procedure prc_backup_log ...'
  RAISERROR (@message, 0, 1) WITH NOWAIT;

  -- Replace explicit NULL values to ''
  SET @dbname = ISNULL(@dbname,'');
  SET @backup_path = ISNULL(@backup_path,'');

  IF @dbname = ''
  BEGIN
    SET @message = 'ERROR - No database name given';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF @backup_path = ''
  BEGIN
    SET @message = 'ERROR - No backup path given';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  -- Strip '\' from backup_path
  IF RIGHT(@backup_path,1) = '\' SET @backup_path = LEFT(@backup_path,LEN(@backup_path)-1);

  -- Check if backup_path exists
  IF dbo.fun_FolderExist(@backup_path) = 0
  BEGIN
    SET @message = 'ERROR - Backup path "' + @backup_path + '" does not exist';
    RAISERROR(@message, 16, 1);
    RETURN 1;
  END

  -- Define backupfile name
  SET @bakfile = @dbname + '_backup_' + dbo.fun_uniquename() + '.trn';

  -- Define and create backup_path with subdir when asked for
  IF @subdir = 1
  BEGIN
    SET @backup_path = @backup_path + '\' + @dbname;
    EXEC master.dbo.xp_create_subdir @backup_path;
  END

  -- Initialize backup command
  SET @cmd = N'BACKUP LOG [' + @dbname + N'] TO ';

  SET @disk = N'DISK=''' + @backup_path + N'\'+ @bakfile + N'''';

  SET @cmd = @cmd + @disk;

  SET @cmd = @cmd + N' WITH NAME=''' + @dbname + N'-Transaction Log Backup''';

  IF @options <> '' SET @cmd = @cmd + N', ' + @options;

  SET @message = '-> Executing cmd: ' + @cmd;
  RAISERROR (@message, 0, 1) WITH NOWAIT;
  EXEC sp_executesql @stmt=@cmd;

  IF @@ERROR <>0
  BEGIN
    SET @message = N'ERROR - Log backup ' + @dbname +' failed';
    RAISERROR(@message, 16, 1);
    RETURN 1;
  END

  -- Continue with RESTORE VERIFYONLY if requested
  IF @verify = 1
  BEGIN
    SET @cmd = 'SELECT @p1 = [position]
FROM msdb..backupset
WHERE [database_name] = @p2
AND [backup_set_id] = (SELECT MAX([backup_set_id]) FROM msdb..backupset WHERE [database_name] = @p2)';

    EXEC sp_executesql @stmt=@cmd, @params=N'@p1 INT OUTPUT, @p2 NVARCHAR(128)', @p1=@backup_set_id OUTPUT, @p2=@dbname

    IF @backup_set_id IS NULL
    BEGIN
      SET @message = N'ERROR: Backupset needed to restore verifyonly not found for database: ' + @dbname;
      RAISERROR(@message, 16, 1);
      RETURN 1;
    END

    SET @cmd = N'RESTORE VERIFYONLY FROM ' + @disk + N' WITH FILE = ' + CONVERT(VARCHAR,@backup_set_id);

    SET @message = '-> Executing cmd: ' + @cmd;
    RAISERROR (@message, 0, 1) WITH NOWAIT;
    EXEC sp_executesql @stmt=@cmd;

    IF @@ERROR <> 0
    BEGIN
      SET @message = N'ERROR - Restore verifyonly '+@dbname+' failed';
      RAISERROR(@message, 16, 1);
      RETURN 1;
    END
  END
END
GO