SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: SQL_SCALAR_FUNCTION fun_FolderExist Script Date: 2026-05-15 19:31:09.327 ******/
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
Check if folder exists
1
Gerrit Mantel
2015-11-24
2026-03-30
*/
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.327 ******/
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
Return an unique name with format yyyy_mm_dd_hhmiss_nnnnnnn
1
Gerrit Mantel
2000-01-01
2024-02-09
*/
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_database Script Date: 2026-05-15 19:31:09.327 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_backup_database]')) DROP PROCEDURE [dbo].[prc_backup_database];
GO
CREATE PROCEDURE [dbo].[prc_backup_database] (
@dbname NVARCHAR(128),
@backup_path VARCHAR(265),
@subdir BIT = 1,
@split_cnt INT = 0,
@options VARCHAR(128) = '',
@verify BIT = 0,
@trace BIT = 0) AS
BEGIN
/*
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
Make a full (or differential) database backup for a given database
1
11
Gerrit Mantel
2011-01-17
2026-04-03
*/
SET NOCOUNT ON
DECLARE @bakfile VARCHAR(265) = ''
DECLARE @bakext VARCHAR(4) = ''
DECLARE @disk NVARCHAR(4000) = ''
DECLARE @cmd NVARCHAR(MAX)
DECLARE @backup_set_id INT
DECLARE @message VARCHAR(512)
SET @message = 'Executing procedure prc_backup_database ...'
RAISERROR (@message, 0, 1) WITH NOWAIT;
-- Replace explicit NULL values to ''
SET @dbname = ISNULL(@dbname,'');
SET @backup_path = ISNULL(@backup_path,'');
IF ISNULL(@dbname,'') = ''
BEGIN
SET @message = 'ERROR - No database name given';
RAISERROR(@message, 16, 1);
RETURN 1
END
IF ISNULL(@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
IF @split_cnt NOT IN (0,2,4,8)
BEGIN
SET @message = 'ERROR - Split count must be 0, 2, 4, or 8';
RAISERROR(@message, 16, 1);
RETURN 1
END
-- Define backupfile name
SET @bakfile = @dbname + '_backup_' + dbo.fun_uniquename();
-- Define file extension name
IF CHARINDEX('DIFFERENTIAL',@options) > 0
SET @bakext = '.dif';
ELSE
SET @bakext = '.bak';
-- 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 DATABASE [' + @dbname + N'] TO ';
IF @split_cnt = 0
BEGIN
-- No split
SET @disk = N'DISK=''' + @backup_path + N'\' + @bakfile + @bakext + N'''';
END
ELSE
BEGIN
IF @split_cnt >= 2
BEGIN
SET @disk = N'DISK=''' + @backup_path + N'\' + @bakfile + N'_1' + @bakext + N''', DISK=''' + @backup_path + @bakfile + N'_2' + @bakext + N'''';
END
IF @split_cnt >= 4
BEGIN
SET @disk = @disk + N', DISK=''' + @backup_path + N'\' + @bakfile + N'_3' + @bakext + N''', DISK=''' + @backup_path + @bakfile + N'_4' + @bakext + N'''';
END
IF @split_cnt = 8
BEGIN
SET @disk = @disk + N', DISK=''' + @backup_path + N'\' + @bakfile + N'_5' + @bakext + N''', DISK=''' + @backup_path + @bakfile + N'_6' + @bakext + N'''';
SET @disk = @disk + N', DISK=''' + @backup_path + N'\' + @bakfile + N'_7' + @bakext + N''', DISK=''' + @backup_path + @bakfile + N'_8' + @bakext + N'''';
END
END
SET @cmd = @cmd + @disk;
IF CHARINDEX('DIFFERENTIAL',@options) > 0
SET @cmd = @cmd + N' WITH NAME=''' + @dbname + N'-Differential Database Backup''';
ELSE
SET @cmd = @cmd + N' WITH NAME=''' + @dbname + N'-Full Database Backup''';
IF @options <> '' SET @cmd = @cmd + N', ' + @options;
-- With these traceflags enabled, the actual backup parameters are logged into the SQL Errorlog.
IF @trace = 1
BEGIN
DBCC TRACEON (3605);
DBCC TRACEON (3213);
END
SET @message = '-> Executing cmd: ' + @cmd;
RAISERROR (@message, 0, 1) WITH NOWAIT;
EXEC sp_executesql @stmt=@cmd;
IF @@ERROR <> 0
BEGIN
SET @message = N'ERROR - Database backup ' + @dbname + ' failed';
RAISERROR(@message, 16, 1);
RETURN 1;
END
IF @trace = 1
BEGIN
DBCC TRACEOFF(3605);
DBCC TRACEOFF(3213);
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 for ' + @dbname + ' not found';
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