Programs

SQL_STORED_PROCEDURE - prc_backup_database

Main program

Name	prc_backup_database
Type_desc	SQL_STORED_PROCEDURE
Comment	SQLServer Stored Procedure PURPOSE Make a full (or differential) database backup for a given database to a given backuppath (on disk). The backupfile name format is: {dbname}_backup_{datetime}.{ext} for single backup files, and {dbname}_backup_{datetime}_{seq}.{ext} for splitted backup files, where {datetime} = 'yyyy_mm_dd_hhMMss_nanosec' (standard backup filename format). {seq} = family sequence number {ext} = 'bak' for full database backups, 'dif' for differential backups 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. - 4 @split_cnt INT 0 (default)/n Split backupfiles to 2, 4 or 8 parts. Value 0 (default) means no split. - 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. - 7 @trace BIT 0 (default)/1 Use trace flags to write backup command parameters to SQL log. Value 0 (default) means no trac flags. Frequently used WITH options: - DIFFERENTIAL Make differential database backup instead of full database backup. - COMPRESSION / NO_COMPRESSION The default value depends on server default. - CHECKSUM / NO_CHECKSUM WITH CHECKSUM is default for compressed backup. - BLOCKSIZE=n BLOCKSIZE=512 is default for disk. Other possible values are 1024, 2048, 4096, 8192, 16384, 32768. BLOCKSIZE=65536 is default for tapes. - BUFFERCOUNT=n The default value depends on systemconfiguration, n=any integer value). The total space used by the buffers is determined by: buffercount * maxtransfersize. Large buffercount numbers may cause 'out of memory'. - MAXTRANSFERSIZE=n n=any multiple values of 65536 (64K), default=1048576 (1M), max=4194304 (4M). Examples are 65536 (64K), 131072 (128K), 262144 (256K), 524288 (512K), 1048576 (1M), 2097152 (2M), 4194304 (4M). - COPY_ONLY Make an independent backup, if used together with DIFFERENTIAL, then COPY_ONLY is ignored. - NOINIT / INIT NOINIT appends to existing backupset (default). INIT overwrites existing backupset. USED OBJECTS - dbo.fun_FolderExist (UDF in local database) - dbo.fun_split (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) Full backup to one file on subdirectory with database name EXEC dbo.prc_backup_database @dbname='DBGERRIT', @backup_path='C:\Data\MSSQL\Backup' -- Backup is written to C:\Data\MSSQL\Backup\DBGERRIT\DBGERRIT_backup_2026_01_28_091500_1234567.bak -- 2) Full backup splitted to 2 files on subdirectory with database name EXEC dbo.prc_backup_database @dbname='DBGERRIT', @backup_path='C:\Data\MSSQL\Backup', @split_cnt=2 -- Backup is written to C:\Data\MSSQL\Backup\DBGERRIT\DBGERRIT_backup_2026_01_28_091500_1234567_1.bak -- and C:\Data\MSSQL\Backup\DBGERRIT\DBGERRIT_backup_2026_01_28_091500_1234567_2.bak -- 3) Differential backup on subdirectory with database name EXEC dbo.prc_backup_database @dbname='DBGERRIT', @backup_path='C:\Data\MSSQL\Backup', @options='DIFFERENTIAL' -- Backup is written to C:\Data\MSSQL\Backup\DBGERRIT\DBGERRIT_backup_2026_01_28_091500_1234567.dif HISTORY 2011-01-17 - Created procedure 2011-04-28 - Correct handling of DIFFERENTIAL backup. 2014-05-12 - @backup_path may end with '\*', which means write backupfile to subfolder 2014-08-14 - RAISERROR on parameter errors 2016-05-17 - Changed traceflags from global to local (removed -1) 2023-03-13 - Updated comment 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 parameters @subdir and @split_cnt - Backup to NUL is not possible anymore - Split to different path's is not possible anymore - Split count can only be 2, 4 or 8 TAGS <program> <description>Make a full (or differential) database backup for a given database</description> <generic>1</generic> <minversion>11</minversion> <author>Gerrit Mantel</author> <created>2011-01-17</created> <lastmodified>2026-04-03</lastmodified> </program>
Description	Make a full (or differential) database backup for a given database
Minversion	11.0
Generic	1
Author	Gerrit Mantel
Created	2011-01-17 00:00:00
Lastmodified	2026-04-03 00:00:00
Xp_cmdshell	0
Ole_automation	0
Subprogram_cnt	2
Sourcecode

Sub programs