SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_FolderExist       Script Date: 2026-05-15 19:31:09.340 ******/
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.340 ******/
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_database       Script Date: 2026-05-15 19:31:09.340 ******/
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
<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>
*/
  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

/****** Object:  SQL_STORED_PROCEDURE  prc_backup_log       Script Date: 2026-05-15 19:31:09.340 ******/
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

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

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

PURPOSE
Make full backup of all system databases.

Only system databases are backupped.
Only database with status ONLINE are backupped.
Database tempdb cannot be backupped.
Backup is made to a subdir (or not) of the given @backup_path (with name {dbname}), depending of parameter @subdir (default is backup to subdir).
Backup is followed by a restore with verifyonly depending on parameter @verify (default is backup without verify).
A log backup of the model database is made (after a full backup) if the model is in FULL or BULK_LOGGED recovery model. This is to prevent growing the logfile.

PARAMETERS
 - 1 @backup_path   VARCHAR(265)                Backup_path
 - 2 @subdir        BIT    0 / 1 (default)      Optional: Backup to subdir 0/1.
 - 3 @verify        BIT    0 (default) / 1      Optional: Backup with verify 0/1.

EXAMPLES
-- Full backups with standard parameters
EXEC [dbo].[prc_backup_systemdb] @backup_path = 'C:\Data\MSSQL\backup';
-- Full backups with verify
EXEC [dbo].[prc_backup_systemdb] @backup_path = 'C:\Data\MSSQL\backup', @verify=1;

USED OBJECTS
 - dbo.prc_backup_database  Stored procedure (in local database)
 - dbo.prc_backup_log       Stored procedure (in local database)
 - dbo.fun_FolderExist      UDF (in local database)

SQLSERVER CONFIGURATION
 - OLE Automation has to be enabled.

HISTORY
2014-07-29 - Created procedure
2015-07-08 - Renamed cursor
2015-07-28 - Changed error messages
2016-01-19 - Changed variable @verify_backup to type BIT
           - Read variable @local_backup and skip backups if 0
2018-01-16 - Variable @local_backup is obsolete now
2019-10-29 - Added logbackup of model database when in FULL of BULK_LOGGED recovery model
2020-07-08 - Replaced table LOCAL_BACKUP_CONFIG by CONFIGURATION
2020-11-03 - Replaced table CONFIGURATION into SETUP_CONFIG
2020-11-04 - Check on parameter backup_base in stead of local_backup to continue
2021-12-14 - SETUP_CONFIG parameter verify_backup is renamed to backup_with_verify
2023-05-12 - Removed @options in commandline prc_backup_db
2023-12-07 - Made suitable for DBMAINT
2023-12-09 - Changed backup path
2024-02-11 - Changed table SETUP to Setup
           - Use dbo.fun_setup2value to read Setup values
2024-04-19 - Extend backup_path with servername
2024-04-23 - Don't extend backup_path anymore
2024-07-23 - Adjusted Setup field lengths and types
2025-05-18 - Renamed table Setup to Ref_setup
2026-04-02 - Made procedure full configurable with parameters
2026-04-03 - Changed pararameters for call to prc_backup_database
2026-05-12 - Renamed procedure from prc_backup_all_systemdb to prc_backup_systemdb

TAGS
<program>
  <description>Make full backup of all system databases</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2014-07-29</created>
  <lastmodified>2026-05-12</lastmodified>
</program>
*/
  SET NOCOUNT ON

  DECLARE @message VARCHAR(512)
  DECLARE @dbname NVARCHAR(128)
  DECLARE @recovery_model NVARCHAR(60)
  DECLARE @return_status INT
  DECLARE @retval INT

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

  -- Check necessary objects in local database
  IF NOT EXISTS (SELECT '' FROM sys.all_objects WHERE [type_desc] = 'SQL_STORED_PROCEDURE' AND [name] = 'prc_backup_database')
  BEGIN
    SET @message = N'ERROR - Procedure prc_backup_database does not exist.';
    RAISERROR(@message, 16, 1);
    RETURN 1;
  END

  IF NOT EXISTS (SELECT '' FROM sys.all_objects WHERE [type_desc] = 'SQL_STORED_PROCEDURE' AND [name] = 'prc_backup_log')
  BEGIN
    SET @message = N'ERROR - Procedure prc_backup_log does not exist.';
    RAISERROR(@message, 16, 1);
    RETURN 1;
  END

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

  -- Strip '\' from @backup_path
  IF RIGHT(@backup_path,1) = N'\' 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

  -- Get system databases to run a full backup (and log backup the model database if necessary)
  DECLARE my_cur_system_backup CURSOR LOCAL READ_ONLY FOR
    SELECT [name], [recovery_model_desc]
    FROM sys.databases
    WHERE [state_desc] = 'ONLINE'
    AND [name] IN ('master', 'model', 'msdb')
    ORDER BY [name];

  SET @return_status = 0;
  OPEN my_cur_system_backup
  WHILE 1 < 2
  BEGIN
    FETCH NEXT FROM my_cur_system_backup INTO @dbname, @recovery_model
    IF @@FETCH_STATUS <> 0 BREAK

    -- Make full backup
    SET @retval = 0;
    EXEC @retval = [dbo].[prc_backup_database] @dbname=@dbname, @backup_path=@backup_path, @subdir=@subdir, @verify=@verify;
    IF @retval <> 0 SET @return_status = 1;

    IF @recovery_model IN ('FULL','BULK_LOGGED')
    BEGIN
      -- Make log backup too
      SET @retval = 0;
      EXEC @retval = [dbo].[prc_backup_log] @dbname=@dbname, @backup_path=@backup_path, @subdir=@subdir, @verify=@verify;
      IF @retval <> 0 SET @return_status = 1;
    END
  END
  CLOSE my_cur_system_backup
  DEALLOCATE my_cur_system_backup

  IF @return_status <> 0
  BEGIN
    SET @message = N'ERROR - One or more backups had errors.';
    RAISERROR(@message, 16, 1);
    RETURN 1;
  END
END
GO