SQL Agent Backup script

Question

Afternoon all, Created the following Agent step script to backup a DB if the instance is primary (as it will fail on a synchronous commit secondary)......anyone see any bad in it?

DECLARE @name   VARCHAR (150)
DECLARE @path   VARCHAR (50)
DECLARE @time   DATETIME
DECLARE @year   VARCHAR (4)
DECLARE @month  VARCHAR (2)
DECLARE @day    VARCHAR (2)
DECLARE @name   VARCHAR (2)
DECLARE @name   VARCHAR (2)
DECLARE @PathWithName VARCHAR (500)
DECLARE @Role_Desc VARCHAR (20)

--Check role.....
--IF( HAS_PERMS_BY_NAME ('sys.dm_hadr_availability_replica_states', 'OBJECT', 'execute') = 1)
IF (sys.fn_hadr_is_primary_replica('WSS_Content') = 1)  --Primary ready for backup
BEGIN
  -- Could be Primary or Secondary.....if Secondary Abort Backup
  IF EXISTS( SELECT hars.role_desc FROM sys.DATABASES d INNER JOIN sys.dm_hadr_availability_replica_states hars ON d.replica_id = hars.replica_id WHERE hars.role_desc <> 'SECONDARY' ) 
    BEGIN
      SET @path = 'E:\MSSQL\Backups\xxxxxxxx\'  -- Put the name of the Folder in the xxxxx area MAKE SURE TO CREATE FIRST
      SELECT @time = GETDATE();  -- Pull current system date and time vectors
      -- Parse @time into Vars to use later in name creation
      SELECT @year = (SELECT CONVERT(VARCHAR (4), FORMAT (DATEPART(yy, @time), '000') ) )
      SELECT @month = (SELECT CONVERT(VARCHAR (2), FORMAT (DATEPART(mm, @time), '000') ) )
      SELECT @day = (SELECT CONVERT(VARCHAR (2), FORMAT (DATEPART(dd, @time), '000') ) )
      SELECT @hour = (SELECT CONVERT(VARCHAR (2), FORMAT (DATEPART(hh, @time), '000') ) )

      --Set the full filename with path e.g.  drive, folder and filename with date&time  
      SET @PathWithName = @path + @name + '.bak'

      -- Run the backup, with Copy_only, compress the DB, and do a page checksum
      BACKUP DATABASE[set DB name here]  -- Easily copied and paste form SSMS to avid typos
      -- Concatenate all into a var for TO DISK use
      TO DISK = @PathWithName  WITH COPY_ONLY, COMPRESSION, SheckSum

      -- Back up completed now verify the backup file for errors
      RESTORE VERIFYONLY FROM DISK @PathWithName
    END  -- End if Either single instance or Primary replica
  ELSE
    -- Secondary replica, not ready for backup
    THROW 50000, 'This is not the primary replica.', 1

Answer 1

I suggest instead of creating your own custom code, make use of the open source scripts from Ola Hallagren:

The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, and SQL Server 2019. The solution is based on stored procedures. The solution has been designed for the most mission-critical environments, and it is used in many organizations around the world. The SQL Server Maintenance Solution has been voted as Best Free Tool in the 2013, 2012, 2011, and 2010 SQL Server Magazine Awards.

You can configure this for your AlwaysOn environment also. See the article Brent Ozar wrote about this.

Links:

1. Ola Hallengren scripts
2. Brent Ozar: Configure Always on AG backups

Answer 2

You've declared @name twice: DECLARE @name VARCHAR (2) DECLARE @name VARCHAR (2)

You have a variable named @hour that hasn't been declared. SELECT @hour = (SELECT CONVERT(VARCHAR (2).....

Otherwise, it looks fine. Why not just use a maintenance plan though?