SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_FolderExist       Script Date: 2026-05-15 19:31:09.357 ******/
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_sqlversion       Script Date: 2026-05-15 19:31:09.357 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_sqlversion]')) DROP FUNCTION [dbo].[fun_sqlversion];
GO


CREATE FUNCTION [dbo].[fun_sqlversion] () 
RETURNS NUMERIC(5,1)
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Returns the sqlversion.

The returned format is numeric(5,1), like 16.0.

EXAMPLES
SELECT dbo.fun_sqlversion()
16.0

HISTORY
2026-03-29 - Created function

TAGS
<program>
  <description>Returns the sqlversion</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2026-03-29</created>
  <lastmodified>2026-03-29</lastmodified>
</program>
*/
  DECLARE @result NUMERIC(5,1)
  SET @result = CONVERT(NUMERIC(5,1), (@@MICROSOFTVERSION / 0x01000000) + ((@@MICROSOFTVERSION & 0x0FFF000) /0x010000) /100.0);
  RETURN @result;
END


GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_uniquename       Script Date: 2026-05-15 19:31:09.357 ******/
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.357 ******/
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_userdb_full       Script Date: 2026-05-15 19:31:09.357 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_backup_userdb_full]')) DROP PROCEDURE [dbo].[prc_backup_userdb_full];
GO

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

PURPOSE
Make full backup of all user databases.

Only user databases are backupped.
Only databases with status ONLINE are backupped.
Logshipping secondary databases are skipped.
HADR databases not on primary replica are skipped.
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).
Backupfiles are splitted depending on parameter @split (default is no splitting). The number of parts the backup is 
splitted to is calculated from database size and number of datafiles using following rules:
 - if     nr_of_datafiles >= 8 AND db_size >= 4000000 mb, split parts = 8
 - elseif nr_of_datafiles >= 4 AND db_size >= 2000000 mb, split parts = 4
 - elseif nr_of_datafiles >= 2 AND db_size >= 1000000 mb, split parts = 2
 - else   no splitting
Backupfiles are deleted if older than the given number of days by parameter @keep_days.

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.
 - 4 @split         BIT    0 (default) / 1      Optional: Split backupfiles 0/1 when database is large.
 - 5 @keep_days     INT    90 (default) / n / 0 Optional: Delete old backupfiles older than n days. Value 0 or negative means no deletes.

EXAMPLES
-- Full backups with standard parameters
EXEC [dbo].[prc_backup_userdb_full] @backup_path = 'C:\Data\MSSQL\backup';
-- Full backups with verify
EXEC [dbo].[prc_backup_userdb_full] @backup_path = 'C:\Data\MSSQL\backup', @verify=1;
-- Full backups with standard parameters, don't delete old backup files
EXEC [dbo].[prc_backup_userdb_full] @backup_path = 'C:\Data\MSSQL\backup', @keep_days=0;

USED OBJECTS
 - prc_backup_database       (Stored_procedure in local database)
 - dbo.fun_FolderExist       (UDF in local database)
 - dbo.fun_sqlversion        (UDF in local database)
 - master.dbo.xp_delete_file (Extended stored procedure)

SQLSERVER CONFIGURATION
 - OLE Automation has to be enabled.

HISTORY
2014-07-29 - Created procedure
2015-05-11 - Changed delete period from 15 to 30 days
2015-05-28 - Changed delete period from 30 to 15 days
2015-07-08 - Added logics for HADR databases
           - Added parameter @dbname
2015-07-14 - Minor change in remark text.
2015-07-15 - Error text changed for HADR database not on primary replica
2015-07-28 - Changed error messages
2016-01-19 - Changed variable @backup_with_verify to type BIT
           - Read variable @local_backup and skip backups if 0 (and @dbname is not given)
2016-02-01 - Removed delete backupfiles action
2016-03-03 - Split parts values can only be 2 or 4
2016-07-13 - Read variable @backup_keep_days and delete backupfiles if > 0 (and @dbname is not given)
2016-07-25 - Remove old backupfiles is now performed after backup
2017-08-21 - Split parts values can be 2,4 or 8 (new possible value)
2018-01-16 - Variable @local_backup is obsolete now
2019-12-12 - Table LOCAL_BACKUP_SPLIT is obsolete now. Variable @parts is calculated with formula.
2019-12-18 - Bug fixed. Large database with filestream type datafiles did not split.
2020-02-18 - Removed table LOCAL_BACKUP_SKIP
2020-02-20 - Introduced parm skip_backup in CONFIGURATION to provide a list of databases to skip the backup
2020-07-08 - Replaced table LOCAL_BACKUP_CONFIG by CONFIGURATION
           - Backup_path_hadr is obsolete now
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 keep_days is renamed to backup_keep_days
           - SETUP_CONFIG parameter verify_backup is renamed to backup_with_verify
2022-07-12 - Fixed bug: wrong backup_dir set when one database has more parts than 1
2022-07-27 - Fixed bug: wrong fieldname used for table SETUP_SKIP_BACKUP
2023-05-12 - Removed @hadr_is_primary_replica from cursor
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-18 - Fixed bug: Delete old backupfiles after @backup_keep_days did not work
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-03-28 - Removed dynamic SQL for cursor
           - Minimum version 12.0
2026-04-01 - Made procedure full configurable with parameters
2026-04-03 - Changed pararameters for call to prc_backup_database
2026-04-08 - Fixed bug: Split does not work properly
2026-04-29 - Added version check
2026-05-12 - Renamed procedure from prc_backup_all_userdb_full to prc_backup_userdb_full

TAGS
<program>
  <description>Make full backup of all user databases</description>
  <generic>1</generic>
  <minversion>12</minversion>
  <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 @return_status INT
  DECLARE @retval INT
  DECLARE @cutoffdate NVARCHAR(50)
  DECLARE @sqlcmd NVARCHAR(MAX)
  DECLARE @nr_of_datafiles INT
  DECLARE @db_size_mb INT
  DECLARE @split_cnt INT
  DECLARE @version NUMERIC(5,1)

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

  -- Compliant SQL versions are between SQL2014 and SQL2022
  SET @version = [dbo].[fun_sqlversion]();
  IF @version NOT IN (12.0, 13.0, 14.0, 15.0, 16.0)
  BEGIN
    SET @message = 'ERROR - SQL Server version not supported';
    RAISERROR(@message, 16, 1);
    RETURN 1;
  END

  -- 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

  -- 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 user databases to run a full backup
  -- HADR and sys.fn_hadr_is_primary_replica function is available on sql2014 (12.0) and higher)
  IF ISNULL(SERVERPROPERTY('IsHadrEnabled'),0) = 1
  BEGIN
    -- Skip hadr databases on secondary replicas
    DECLARE my_cur_full_backup CURSOR READ_ONLY FOR
      SELECT t1.[name]
      FROM sys.databases t1
      WHERE t1.[state_desc] = 'ONLINE'
      AND t1.[name] COLLATE database_default NOT IN ('master', 'tempdb', 'model', 'msdb')
      AND t1.[name] COLLATE database_default NOT IN (SELECT [secondary_database] COLLATE database_default FROM [msdb].[dbo].[log_shipping_secondary_databases])
      AND ISNULL(CONVERT(INT,sys.fn_hadr_is_primary_replica(t1.[name])),-1) <> 0
      ORDER BY t1.[name];
  END
  ELSE
  BEGIN
    DECLARE my_cur_full_backup CURSOR READ_ONLY FOR
      SELECT t1.[name]
      FROM sys.databases t1
      WHERE t1.[state_desc] = 'ONLINE'
      AND t1.[name] COLLATE database_default NOT IN ('master', 'tempdb', 'model', 'msdb')
      AND t1.[name] COLLATE database_default NOT IN (SELECT [secondary_database] COLLATE database_default FROM [msdb].[dbo].[log_shipping_secondary_databases])
      ORDER BY t1.[name];
  END

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

    SET @split_cnt = 0
    IF @split = 1
    BEGIN
      -- Calculate if backup split to multiple parts is needed
      SET @sqlcmd = 'SET @p1 = (SELECT COUNT(*) FROM ['+@dbname+'].sys.database_files WHERE [type_desc] <> ''LOG'')';
      EXEC sp_executesql @sqlcmd, N'@p1 INT OUTPUT', @p1 = @nr_of_datafiles OUTPUT;
  
      SET @sqlcmd = 'SET @p1 = (SELECT SUM(CEILING([size]/128.0)) FROM ['+@dbname+'].sys.database_files WHERE [type_desc] <> ''LOG'')';
      EXEC sp_executesql @sqlcmd, N'@p1 INT OUTPUT', @p1 = @db_size_mb OUTPUT;
  
      IF      @nr_of_datafiles >= 8 AND @db_size_mb >= 4000000 SET @split_cnt=8;
      ELSE IF @nr_of_datafiles >= 4 AND @db_size_mb >= 2000000 SET @split_cnt=4;
      ELSE IF @nr_of_datafiles >= 2 AND @db_size_mb >= 1000000 SET @split_cnt=2;
    END

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

  -- Delete old backupfiles
  IF @keep_days > 0
  BEGIN
    -- Delete old backupfiles before @keep_days days
    SET @cutoffdate = REPLACE(CONVERT(NVARCHAR, DATEADD(d, -@keep_days, GETDATE()), 120),' ','T');
    -- The parameters for extended stored procedure xp_delete_file are:
    --  1: File Type (0=backups, 1=maintenance plan logfiles)
    --  2: Folder Path
    --  3: File Extension (file extension name without '.', f.i. bak, trn, dif or *)
    --  4: Date (based on date last modified, up to but not included)
    --  5: Subfolder (0=exclude subfolders, 1=include subfolders (but only first sublevel) 
    EXEC master.dbo.xp_delete_file 0, @backup_path, N'*', @cutoffdate, 1;
  END

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