SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

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

PURPOSE
Make differential 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).
A full backup of a database is made when a diffential backup cannot be made.

By default, force a full backup of a database when no full backup is found in the msdb backup history (made by this server, no COPY_ONLY type).
This can be disabled with @force_full_backup=0.
When a database is restored from another database, technically a differential backup can be made, but then the backup history of the new database can be confusing.

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 @force_full_backup BIT    0 / 1 (default)      Optional: Force a full backup when there is no full backup found in the msdb history 0/1.

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

USED OBJECTS
 - prc_backup_database       (Stored_procedure in local database)
 - dbo.fun_FolderExist       (UDF in local database)
 - dbo.fun_sqlversion        (UDF in local database)

SQLSERVER CONFIGURATION
 - OLE Automation has to be enabled.

HISTORY
2014-07-29 - Created procedure
2015-07-08 - Added logics for HADR databases
2015-07-14 - Added parameter @dbname
2015-07-15 - Error text changed for database not on primary replica
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 (and @dbname is not given)
2018-01-16 - Variable @local_backup is obsolete now
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 verify_backup is renamed to backup_with_verify
2022-07-27 - Fixed bug: wrong fieldname used for table SETUP_SKIP_BACKUP
2023-05-12 - Take full backup when database needs full backup (only when parameter @dbname <> '')
           - 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-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-02 - Made procedure full configurable with parameters
2026-04-03 - Changed pararameters for call to prc_backup_database
2026-04-29 - Added version check
2026-05-12 - Renamed procedure from prc_backup_all_userdb_diff to prc_backup_userdb_diff
2026-05-13 - Added parameter @force_full_backup (with default 1)
2026-05-15 - Bug fix: Fixed error "Cursorfetch: The number of variables declared in the INTO list must match that of selected columns."

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

  DECLARE @message VARCHAR(512)
  DECLARE @dbname NVARCHAR(128)
  DECLARE @return_status INT
  DECLARE @retval INT
  DECLARE @need_full_backup INT
  DECLARE @version NUMERIC(5,1)
  DECLARE @last_full_backup_date DATETIME

  SET @message = 'Executing procedure prc_backup_userdb_diff ...'
  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 differential backup (or full backup if differential can not be made)
  -- 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_diff_backup CURSOR READ_ONLY FOR
      SELECT
        t1.[name],
        CASE WHEN ((t1.[recovery_model_desc] <> 'SIMPLE' AND t2.[last_log_backup_lsn] = 0) OR t3.[differential_base_lsn] = 0) THEN 1 ELSE 0 END AS [need_full_backup],
        t4.[last_full_backup_date]
      FROM sys.databases t1
      OUTER APPLY (
        SELECT ISNULL([last_log_backup_lsn],0) AS [last_log_backup_lsn]
        FROM master.sys.[database_recovery_status]
        WHERE [database_id] = t1.[database_id]) t2
      OUTER APPLY (
        SELECT MAX(ISNULL([differential_base_lsn],0)) AS [differential_base_lsn]
        FROM master.sys.[master_files] t2
        WHERE [database_id] = t1.[database_id]
        AND [type] = 0) t3
      OUTER APPLY (
        SELECT TOP 1 [backup_start_date] AS [last_full_backup_date]
        FROM msdb.dbo.[backupset]
        WHERE [database_name] = t1.[name]  
        AND [server_name] = @@SERVERNAME
        AND [type] = 'D'
        AND [is_copy_only] = 0
        ORDER BY [backup_finish_date] DESC) t4
      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_diff_backup CURSOR READ_ONLY FOR
      SELECT
        t1.[name],
        CASE WHEN ((t1.[recovery_model_desc] <> 'SIMPLE' AND t2.[last_log_backup_lsn] = 0) OR t3.[differential_base_lsn] = 0) THEN 1 ELSE 0 END AS [need_full_backup],
        t4.[last_full_backup_date]
      FROM sys.databases t1
      OUTER APPLY (
        SELECT ISNULL([last_log_backup_lsn],0) AS [last_log_backup_lsn]
        FROM master.sys.[database_recovery_status]
        WHERE [database_id] = t1.[database_id]) t2
      OUTER APPLY (
        SELECT MAX(ISNULL([differential_base_lsn],0)) AS [differential_base_lsn]
        FROM master.sys.[master_files] t2
        WHERE [database_id] = t1.[database_id]
        AND [type] = 0) t3
      OUTER APPLY (
        SELECT TOP 1 [backup_start_date] AS [last_full_backup_date]
        FROM msdb.dbo.[backupset]
        WHERE [database_name] = t1.[name]  
        AND [server_name] = @@SERVERNAME
        AND [type] = 'D'
        AND [is_copy_only] = 0
        ORDER BY [backup_finish_date] DESC) t4
      WHERE [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_diff_backup
  WHILE 1 < 2
  BEGIN
    FETCH NEXT FROM my_cur_diff_backup INTO @dbname, @need_full_backup, @last_full_backup_date
    IF @@FETCH_STATUS <> 0 BREAK

    IF ((@need_full_backup = 1) OR (@last_full_backup_date IS NULL AND @force_full_backup = 1))
    BEGIN
      -- 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;
    END
    ELSE
    BEGIN
      -- Make differential backup
      SET @retval = 0;
      EXEC @retval = [dbo].[prc_backup_database] @dbname=@dbname, @backup_path=@backup_path, @subdir=@subdir, @options='DIFFERENTIAL', @verify=@verify;
      IF @retval <> 0 SET @return_status = 1;
    END
  END
  CLOSE my_cur_diff_backup
  DEALLOCATE my_cur_diff_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