Programs

SQL_STORED_PROCEDURE - prc_backup_log

Main program

Name	prc_backup_log
Type_desc	SQL_STORED_PROCEDURE
Comment	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>
Description	Make a transaction log backup for a given database
Minversion	11.0
Generic	1
Author	Gerrit Mantel
Created	2011-04-28 00:00:00
Lastmodified	2026-04-03 00:00:00
Xp_cmdshell	0
Ole_automation	0
Subprogram_cnt	2
Sourcecode

Sub programs