PostgreSQL Source Code Installation

 Build From Source Code:

PostgreSQL Source Code Installation means compiling and installing PostgreSQL directly from its source files instead of using prebuilt packages.

Prerequisite:

Before we install PostgreSQL software/cluster, we need to ensure that we have all the necessary tools and libraries on our system.

We need install software using root user only 

○ C Compiler: PostgreSQL is written in C, so a C compiler is a must. Popular choices include GCC (GNU Compiler Collection) and Clang.

○ Build Tools: Ensure you have essential build tools like make and autoconf installed. These tools help in compiling the source code efficiently.

○ Libraries: PostgreSQL requires certain libraries like readline and zlib. We need to install these development libraries on our system.

○ readline-devel: Provides the necessary libraries for command-line editing and history.

○ zlib-devel: Required for compression support.

○ gcc: The GNU Compiler Collection needed to compile the PostgreSQL source code.

○ make: A build tool used to compile and install the software.

Step1) Install Required Packages

[root@post101 ~]# yum install readline-devel zlib-devel openssl-devel libxml2-devel libxslt-devel systemd-devel wget tar perl perl-core perl-FindBin -y

OR

[root@post101 ~]# sudo dnf install readline-devel zlib-devel openssl-devel libxml2-devel libxslt-devel systemd-devel wget tar perl perl-core perl-FindBin -y

Step2) Create postgres user and add user to sudoers file

[root@post101 ~]# adduser postgres

[root@post101 ~]# passwd postgres

Retype new password:

passwd: all authentication tokens updated successfully.

[root@post101 ~]# vi /etc/sudoers

## Allow root to run any commands anywhere

root    ALL=(ALL)       ALL

postgres ALL=(ALL)      ALL

Step3) 

Download Source Code From https://www.postgresql.org/download/ 

Download software and copy software using WinSCP

OR

Use below command if we have internet connection 

[root@post101 ~]# 

[root@post101 ~]# wget https://ftp.postgresql.org/pub/source/v17.1/postgresql-17.1.tar.gz

Step4) untar zip file and go to postgresql-17.1

[root@post101 ~]# tar -xvzf postgresql-17.1.tar.gz

[root@post101 ~]# cd postgresql-17.1

[root@post101 postgresql-17.1]# ls -lrt

total 820

drwxrwxr-x. 16 root root   4096 Nov 12  2024 src

-rw-rw-r--.  1 root root    983 Nov 12  2024 README.md

-rw-rw-r--.  1 root root   6484 Nov 12  2024 meson_options.txt

-rw-rw-r--.  1 root root 115220 Nov 12  2024 meson.build

-rw-rw-r--.  1 root root   1822 Nov 12  2024 Makefile

-rw-rw-r--.  1 root root    277 Nov 12  2024 HISTORY

-rw-rw-r--.  1 root root   4176 Nov 12  2024 GNUmakefile.in

drwxrwxr-x.  3 root root     87 Nov 12  2024 doc

-rw-rw-r--.  1 root root   1192 Nov 12  2024 COPYRIGHT

drwxrwxr-x. 59 root root   4096 Nov 12  2024 contrib

-rw-rw-r--.  1 root root  88218 Nov 12  2024 configure.ac

-rwxrwxr-x.  1 root root 578504 Nov 12  2024 configure

drwxrwxr-x.  2 root root   4096 Nov 12  2024 config

-rw-rw-r--.  1 root root    365 Nov 12  2024 aclocal.m4

[root@post101 postgresql-17.1]#

Step5) Create directory for software binaries installation

[root@post101 ~]# mkdir -p /u01/pgsql-17/

[root@post101 ~]# cd /u01/pgsql-17/

[root@post101 pgsql-17]# ls -lrt

total 0

[root@post101 pgsql-17]#

 

Step6) ./configure

Configure the Build

This prepares the build environment.

prefix ==> Installation directory

configure ==> Checks OS libraries & prepares compilation

[root@post101 postgresql-17.1]# ./configure --prefix=/u01/pgsql-17

i got the below errors , so can skip by using below command '

checking for icu-uc icu-i18n... no

configure: error: ICU library not found

If you have ICU already installed, see config.log for details on the

failure.  It is possible the compiler isn't looking in the proper directory.

Use --without-icu to disable ICU support.

[root@post101 postgresql-17.1]# ./configure --prefix=/u01/pgsql-17 --without-icu --without-readline

This time this got succeed without any erros 

config.status: creating src/include/pg_config_ext.h

config.status: creating src/interfaces/ecpg/include/ecpg_config.h

config.status: linking src/backend/port/tas/dummy.s to src/backend/port/tas.s

config.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.c

config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c

config.status: linking src/include/port/linux.h to src/include/pg_config_os.h

config.status: linking src/makefiles/Makefile.linux to src/Makefile.port

[root@post101 postgresql-17.1]#

step7) make (Compile the Source Code)

This step builds PostgreSQL binaries.

[root@post101 postgresql-17.1]# make

Steep8) make install (Install PostgreSQL)

[root@post101 postgresql-17.1]# cd /u01/pgsql-17/

[root@post101 pgsql-17]# ls -lrt

total 16

drwxr-xr-x. 6 root root 4096 Mar 11 20:42 include

drwxr-xr-x. 2 root root 4096 Mar 11 20:43 bin

drwxr-xr-x. 6 root root 4096 Mar 11 20:43 share

drwxr-xr-x. 4 root root 4096 Mar 11 20:43 lib

[root@post101 pgsql-17]#

After installation, PostgreSQL will be located at:

 /u01/pgsql-17/

we can see all executable files on bin location

[root@post101 postgresql-17.1]# cd /u01/pgsql-17/

[root@post101 pgsql-17]# ls -lrt

total 16

drwxr-xr-x. 6 root root 4096 Mar 11 20:42 include

drwxr-xr-x. 2 root root 4096 Mar 11 20:43 bin

drwxr-xr-x. 6 root root 4096 Mar 11 20:43 share

drwxr-xr-x. 4 root root 4096 Mar 11 20:43 lib

[root@post101 pgsql-17]# cd bin/

[root@post101 bin]# ls -lrt

total 15360

-rwxr-xr-x. 1 root root 10154008 Mar 11 20:42 postgres

-rwxr-xr-x. 1 root root  1083408 Mar 11 20:42 ecpg

-rwxr-xr-x. 1 root root   170704 Mar 11 20:42 initdb

-rwxr-xr-x. 1 root root   115224 Mar 11 20:42 pg_amcheck

-rwxr-xr-x. 1 root root    49664 Mar 11 20:42 pg_archivecleanup

-rwxr-xr-x. 1 root root   173376 Mar 11 20:42 pg_basebackup

-rwxr-xr-x. 1 root root   114304 Mar 11 20:42 pg_createsubscriber

-rwxr-xr-x. 1 root root   116408 Mar 11 20:42 pg_receivewal

-rwxr-xr-x. 1 root root   116160 Mar 11 20:42 pg_recvlogical

-rwxr-xr-x. 1 root root    85440 Mar 11 20:42 pg_checksums

-rwxr-xr-x. 1 root root   187312 Mar 11 20:42 pg_combinebackup

-rwxr-xr-x. 1 root root    47808 Mar 11 20:42 pg_config

-rwxr-xr-x. 1 root root    66592 Mar 11 20:42 pg_controldata

-rwxr-xr-x. 1 root root    82176 Mar 11 20:42 pg_ctl

-rwxr-xr-x. 1 root root   440376 Mar 11 20:42 pg_dump

-rwxr-xr-x. 1 root root   220784 Mar 11 20:42 pg_restore

-rwxr-xr-x. 1 root root   136528 Mar 11 20:42 pg_dumpall

-rwxr-xr-x. 1 root root    77088 Mar 11 20:42 pg_resetwal

-rwxr-xr-x. 1 root root   169448 Mar 11 20:42 pg_rewind

-rwxr-xr-x. 1 root root    55160 Mar 11 20:42 pg_test_fsync

-rwxr-xr-x. 1 root root    44120 Mar 11 20:42 pg_test_timing

-rwxr-xr-x. 1 root root   189328 Mar 11 20:42 pg_upgrade

-rwxr-xr-x. 1 root root   139072 Mar 11 20:42 pg_verifybackup

-rwxr-xr-x. 1 root root   123536 Mar 11 20:42 pg_waldump

-rwxr-xr-x. 1 root root    76488 Mar 11 20:42 pg_walsummary

-rwxr-xr-x. 1 root root   211344 Mar 11 20:43 pgbench

-rwxr-xr-x. 1 root root   528168 Mar 11 20:43 psql

-rwxr-xr-x. 1 root root    83648 Mar 11 20:43 createdb

-rwxr-xr-x. 1 root root    79032 Mar 11 20:43 dropdb

-rwxr-xr-x. 1 root root    88576 Mar 11 20:43 createuser

-rwxr-xr-x. 1 root root    78968 Mar 11 20:43 dropuser

-rwxr-xr-x. 1 root root    83888 Mar 11 20:43 clusterdb

-rwxr-xr-x. 1 root root    97744 Mar 11 20:43 vacuumdb

-rwxr-xr-x. 1 root root    93024 Mar 11 20:43 reindexdb

-rwxr-xr-x. 1 root root    78472 Mar 11 20:43 pg_isready

[root@post101 bin]#

Step8) Create Data Directory and grant Permissions to postgres user

[root@post101 bin]# mkdir -p /u01/pgdata

[root@post101 bin]# cd /u01/pgdata

[root@post101 pgdata]# ls -lrt

total 0

[root@post101 pgdata]#

[root@post101 pgdata]# chown -R postgres:postgres /u01/pgdata

Step9) switch to postgres user 

[root@post101 pgdata]# su - postgres

[postgres@post101 ~]$ cd /u01/pg

pgdata/   pgsql-17/

[postgres@post101 ~]$ cd /u01/pgdata/

[postgres@post101 pgdata]$ pwd

/u01/pgdata

[postgres@post101 pgdata]$ ls -lrt

total 0

[postgres@post101 pgdata]$

Step10)  Initialize Database Cluster

[postgres@post101 bin]$ /u01/pgsql-17/bin/initdb -D /u01/pgdata

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /u01/pgdata ... ok

creating subdirectories ... ok

selecting dynamic shared memory implementation ... posix

selecting default "max_connections" ... 100

selecting default "shared_buffers" ... 128MB

selecting default time zone ... Asia/Kolkata

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections

initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /u01/pgsql-17/bin/pg_ctl -D /u01/pgdata -l logfile start

[postgres@post101 bin]$

Step11) Start PostgreSQL cluster 

[postgres@post101 bin]$ /u01/pgsql-17/bin/pg_ctl  -D /u01/pgdata start

waiting for server to start....2026-03-11 20:56:42.024 IST [9732] LOG:  starting PostgreSQL 17.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44.0.3), 64-bit

2026-03-11 20:56:42.035 IST [9732] LOG:  listening on IPv6 address "::1%1", port 5432

2026-03-11 20:56:42.035 IST [9732] LOG:  listening on IPv4 address "127.0.0.1", port 5432

2026-03-11 20:56:42.417 IST [9732] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"

2026-03-11 20:56:42.432 IST [9735] LOG:  database system was shut down at 2026-03-11 20:54:02 IST

2026-03-11 20:56:42.446 IST [9732] LOG:  database system is ready to accept connections

 done

server started

[postgres@post101 bin]$

[postgres@post101 bin]$ ps -ef|grep postgres

root      7357  3458  0 20:51 pts/0    00:00:00 su - postgres

postgres  7358  7357  0 20:51 pts/0    00:00:00 -bash

postgres  9732     1  0 20:56 ?        00:00:00 /u01/pgsql-17/bin/postgres -D /u01/pgdata

postgres  9733  9732  0 20:56 ?        00:00:00 postgres: checkpointer

postgres  9734  9732  0 20:56 ?        00:00:00 postgres: background writer

postgres  9736  9732  0 20:56 ?        00:00:00 postgres: walwriter

postgres  9737  9732  0 20:56 ?        00:00:00 postgres: autovacuum launcher

postgres  9738  9732  0 20:56 ?        00:00:00 postgres: logical replication launcher

postgres 10878  7358 99 20:59 pts/0    00:00:00 ps -ef

postgres 10879  7358  0 20:59 pts/0    00:00:00 grep --color=auto postgres

[postgres@post101 bin]$

Step12) Set Environment Variables

[postgres@post101 ~]$ vi .bash_profile

export PATH=$PATH:/u01/pgsql-17/bin

export PGDATA=/u01/pgdata

[postgres@post101 ~]$ . .bash_profile

[postgres@post101 ~]$ source .bash_profile

[postgres@post101 ~]$ psql

psql (17.1)

Type "help" for help.

postgres=# \l

                                                     List of databases

   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU

Rules |   Access privileges

-----------+----------+----------+-----------------+-------------+-------------+--------+-----

------+-----------------------

 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |

      |

 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |

      | =c/postgres          +

           |          |          |                 |             |             |        |

      | postgres=CTc/postgres

 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |

      | =c/postgres          +

           |          |          |                 |             |             |        |

      | postgres=CTc/postgres

(3 rows)

Step13) How to start postgres services auto start whenever server rebooted/shutdown 

Switch to root user

[postgres@post101 ~]$ su -

Password:

Last login: Wed Mar 11 19:59:13 IST 2026 from 2406:7400:9a:e591:fc9d:9082:d6a7:20c1 on pts/0

[root@post101 ~]# 

[root@post101 ~]# cd /etc/rc.d

[root@post101 rc.d]# ls -lrt

total 8

drwxr-xr-x. 2 root root   61 Jun 12  2024 rc6.d

drwxr-xr-x. 2 root root   61 Jun 12  2024 rc5.d

drwxr-xr-x. 2 root root   61 Jun 12  2024 rc4.d

drwxr-xr-x. 2 root root   61 Jun 12  2024 rc3.d

drwxr-xr-x. 2 root root   61 Jun 12  2024 rc2.d

drwxr-xr-x. 2 root root   61 Jun 12  2024 rc1.d

drwxr-xr-x. 2 root root   61 Jun 12  2024 rc0.d

-rw-r--r--. 1 root root  473 Oct 10  2024 rc.local

drwxr-xr-x. 2 root root 4096 Feb  6 17:55 init.d

[root@post101 rc.d]# vi rc.local

[root@post101 rc.d]# cat rc.local

#!/bin/bash

# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES

#

# It is highly advisable to create own systemd services or udev rules

# to run scripts during boot instead of using this file.

#

# In contrast to previous versions due to parallel execution during boot

# this script will NOT be run after all other services.

#

# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure

# that this script will be executed during boot.

touch /var/lock/subsys/local

su - postgres -c 'pg_ctl -D $PGDATA start'

[root@post101 rc.d]#

[root@post101 rc.d]# chmod +x /etc/rc.d/rc.local

RMAN Backup scripts ( Full, Level 0 and Level 1 Incremental )

Oracle Database, Recovery Manager (RMAN) backups are mainly of two types:

Full Backup and Incremental Backup (Level 0, Level 1).

Level 1 can be Differential or Cumulative.

RMAN Full Backup

A Full Backup copies all blocks of the database datafiles, regardless of whether they changed or not.

Backs up entire database

Includes used + unused blocks

Does not act as a base for incremental backups

Larger backup size

Syntax 

    Backup database; ## FRA Location

    Backup database plus archivelog;

    Backup database format ='/backup/rman/Bak_%d_%T_%U.bkp'

    Backup database format ='/backup/rman/Bak_%d_%T_%U.bkp' plus archivelog format ='/backup/rman/Arch_%d_%T_%U.bkp';

RMAN Incremental Backup

Incremental backups copy only changed blocks since a previous backup.

Two levels of backups

Level 0

Level 1

Level 0 Backup

A Level 0 backup is the base backup for incremental strategy.

Copies all used blocks

Acts as base for Level 1 backups

Similar to full backup but supports incremental chain

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE format ='/backup/rman/Bak_L0_%d_%T_%U.bkp';

Level 0 = foundation backup

Level 1 Backup

Level 1 backs up only blocks changed since the Level 0 backup or previous Level 1 backup.

Two types exist:

Differential

Cumulative

Level 1 Differential Backup

Backs up blocks changed since the last incremental backup (Level 0 or Level 1).

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE format ='/backup/rman/Bak_L0_%d_%T_%U.bkp';

Level 0 (Sunday)

     |---- Level 1 Differential (Mon)

     |---- Level 1 Differential (Tue)

     |---- Level 1 Differential (Wed)

     |---- Level 1 Differential (Thu)

     |---- Level 1 Differential (Fri)

     |---- Level 1 Differential (Sat)

Level 1 Cumulative Backup

Backs up all blocks changed since the last Level 0 backup.

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE  DATABASE format ='/backup/rman/Bak_L0_%d_%T_%U.bkp';

    Level 0 (Sunday)

     |---- Level 1 Cum (Mon -Changes from since Sun)

     |---- Level 1 Cum (Tue -Changes from since Sun)

     |---- Level 1 Cum (Wed -Changes from since Sun)

     |---- Level 1 Cum (Thu -Changes from since Sun)

     |---- Level 1 Cum (Fri -Changes from since Sun)

     |---- Level 1 Cum (Sat -Changes from since Sun)

Backup Archive Log

RMAN> Backup archivelog all;

RMAN> Backup archivelog all format='/backup/rman/Arch_%d_%T_%U.bkp';

Crontab Schedule :

crontab -e

# Sunday Level 0 Backup at 2 AM

0 2 * * 0 /u01/scripts/rman_level0.sh >> /u01/scripts/logs/l0_backup.log 2>&1

# Monday–Saturday Level 1 Cumulative Backup at 2 AM

0 2 * * 1-6 /u01/scripts/rman_level1_cum.sh >> /u01/scripts/logs/l1_cum_backup.log 2>&1

#Sunday – Level 0 Backup Script

#!/bin/bash

export ORACLE_SID=PROD

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'WEEKLY_SUN_L0';

BACKUP ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL c1;

}

EXIT;

EOF

# Weekdays – Level 1 Differential Script

#!/bin/bash

export ORACLE_SID=PROD

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'DAILY_L1';

BACKUP ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL c1;

}

EXIT;

EOF

Sunday Level 0 RMAN Script

#!/bin/bash

export ORACLE_SID=PRODDB

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

BACKUP INCREMENTAL LEVEL 0 DATABASE

TAG 'WEEKLY_L0'

FORMAT '/backup/rman/L0_%d_%T_%U.bkp';

BACKUP ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL c1;

}

EXIT;

EOF

#Weekdays Level 1 Cumulative Script

# %d = Database, %T = Date Time and %U= 8 number 

#!/bin/bash

export ORACLE_SID=PROD

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE

TAG 'DAILY_L1_CUM'

FORMAT '/backup/rman/L1C_%d_%T_%U.bkp';

BACKUP ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL c1;

}

EXIT;

EOF

### Script 

export ORACLE_SID=PROD

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

run

{

Allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

crosscheck backup;

delete noprompt expired backup;

crosscheck archivelog all;

delete noprompt archivelog all completed before 'SYSDATE -7'; 

backup as backupset format='/u01/app/data/PROD/Backup_%T_%d_%U'

plus archivelog format ='/u01/app/data/PROD/Archive_%T_%d_%U';

backup current controlfile format='/u01/app/data/PROD/controlfile_%F';

release channel ch1;

release channel ch2;

}

quit;

EOF

RMAN Settings/Configuration Parameters

[oracle@ORA-TEST301 ~]$ rman target /

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name TEST are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

       CONFIGURE RETENTION POLICY TO Redundancy 7; ## Number of backup files

       CONFIGURE RETENTION POLICY TO Recovery window 7 days; ## Number of days backup files

CONFIGURE BACKUP OPTIMIZATION OFF; # default

        CONFIGURE BACKUP OPTIMIZATION ON;

            Read only tablespace and data files not backup.

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

    Defines where backups are stored Disk or Tape/SBT

CONFIGURE CONTROLFILE AUTOBACKUP ON;

    Automatically backs up the control file and SPFILE after backup operations.

    Full backup,Level0 , Level 1. Archive log backup and data file backup

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/2c/data/oracle12c/rman_bkup/SDADS/%F';

    %F 12 character alph numaric 

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

    How many backup streams run simultaneously.

    RMAN uses 4 channels

    Backup runs faster because multiple files are backed up in parallel.

    CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

    Backup Types

        Backup of copy ==> An Image Copy is an exact byte-to-byte copy of a datafile.

        Backup as copy database;

        Backup sets ==> RMAN stores backups in compressed binary files.

        Backup as bakupset database;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/2c/data/oracle12c/rman_bkup/TEST/bkup_%U';

    Backup file naming convention.

    %d database name

    %U unique backup identifier

    %T date

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

    Only for backup sets

    If we keep MAXSETSIZE to 100GB if your database size is 300GB it will take 3 backup files

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

    Backup will be encryption 

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

   Default AES125 Backup algorithm, we need to enable TDE 

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

    Backup size will be reduced 

    Default is Basic but we have Low , Medium and High

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

    How many days RMAN Backup information we have to keep on control file

    List backup;

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

    NONE means anyone can delete Archive log files on OS level

    If we have Standby server, if we keep NONE Primary and DR will go out of sync 

    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/2c/data/oracle12c/rman_bkup/TEST/snapcf_TEST.f';

    During backup:

            Datafiles are being backed up

            Control file records are constantly updating

            Archivelogs are generated

 

SQL Server Always ON Readbale Secondary Options

 Always ON Readbale Secondary Options

NO

Secondary replica cannot accept any read connections. Only used for failover. Yes Secondary replica accepts all read connections (even if the connection string does not specify read-intent). READ_INTENT_ONLY Secondary replica accepts connections only if the client specifies ApplicationIntent=ReadOnly in the connection string. Server=AOAGListener; Database=DB; ApplicationIntent=ReadOnly;

How to validate the Backups in SQL server

 How to checks whether the SQL backup file is structurally valid and readable.

1) RESTORE VERIFYONLY

2) RESTORE HEADERONLY

3) RESTORE FILELISTONLY 

RESTORE VERIFYONLY

FROM DISK = 'D:\SQLData\Backup\DBA_FULL.bak';

RESTORE HEADERONLY

FROM DISK = 'D:\SQLData\Backup\DBA_FULL.bak';

RESTORE FILElistonly

FROM DISK = 'D:\SQLData\Backup\DBA_FULL.bak';

PostgreSQL Architecture

PostgreSQL Cluster / Instance is combination of Memory and Background process, cluster is nothing but collection / Group of databases managed by single instance of the PostgreSQL cluster 

Memory Components

Shared Memory 

Shared Buffer ==> This is the primary cache of PostgreSQL, where data blocks writes/reads from the disk. 

WAL Buffer ==> Buffer for Write ahead logging (WAL) data before it is written to disk.

CLOG Buffer ==> Store commit log information to manage transaction states

Temp Buffers ==> Used for storing temporary tables. Default 8MB

Work Memory ==> Used for sorting operations , Bitmap operation, hash join and merge join operations. Default 4MB 

Maintenance Work Memory ==> Used for vacuum and index operations. Default 64MB

Catalog Cache and Execution Memory ==> Memory used for caching system catalogs and                 executing SQL queries

Backend Process

Postmaster Process

    When we start PostgreSQL cluster ,the postmaster process is the first to initialize 

    Responsibilities of Postmaster

            Accepting incoming connection 

            Forks new backend process

            Starts background process

            Handles server restart and crash recovery 

BG Writer ==> Periodically Writes Dirty buffers/pages from shared buffer to the disk

WAL Writer ==> Writes contents of the WAL buffers to WAL files

Checkpoint ==> Writes dirty buffers to disk when a checkpoint occurs , this reduces the recovery time after a crash. 

Stats Collector ==> Collect DBMS usage statistics information such as session activity information(pg_stat_activity) and table usage statistics information (pg_stat_all_tables)

Gathers and maintains statistical data about the database, which the query planner uses to optimize query execution plans 

Archiver ==> When in archive log mode , copies the WAL files to the archive log location 

Sys Logger  ==> Logs error messages and a variety of information to log files

Auto vacuum Launcher ==> Fork the auto vacuum worker when vacuum is required(Automatically reclaims storage occupied by dead tuples( Rows that have updated or deleted), to prevent table bloat and maintains query performance.

Logical Replication Launcher ==> Manages the logical replication workers that handle replication of changes to other databases.

WAL Sender  ==> Sending WAL records to standby servers, runs on the primary server

WAL Receiver ==> Receive WAL records and apply on standby server , runs on the standby server

RMAN Expired and Obsolete Backups

 RMAN Expired and Obsolete Backups

Expired Backups:

Backup set or backup pieces are deleted on OS Level, Database control file have backup details on disk but OS level files are not available.

Action:

RMAN> crosscheck backup;

RMAN> delete expired backup;

Obsolete Backups:

The general meaning of OBSOLETE is no longer used or required for recovery.

RMAN considers backups as OBSOLETE when they are no longer required for database recovery.This is done by one of the RMAN CONFIGURATION parameters.

Action:

RMAN> report obsolete;

RMAN> delete obsolete;

RMAN Backups Methodology Full and Incremental

RMAN provides multiple backup types, but the most commonly used in production environments are Full backups and Incremental backups. Understanding their differences helps DBAs design efficient backup strategies that optimize storage, speed, and recovery time.

1. Full Backup

A Full Backup (or Level 0 backup) is a complete backup of the entire database.

Key Characteristics

Backs up all data blocks, regardless of whether they changed.

Forms the baseline for incremental backup strategies.

Larger in size than incremental backups.

Takes more time and requires more storage.

Recovery is straightforward—restore full backup + apply archived logs.

2. Incremental Backups

Incremental backups only capture blocks that have changed since a previous L0 backup.

RMAN supports two types:

Differential Incremental Backup (Level 1 Differential)

Cumulative Incremental Backup (Level 1 Cumulative)

Both rely on a Level L0 full backup as a baseline.

2.1. Differential Incremental Backup

A Differential Level 1 backup captures all blocks changed since the last backup (Level 0 or Level 1).

Key Characteristics

Smaller and faster than full backups.

Captures daily changes.

During recovery, RMAN may use multiple Level 1 backups.

When it comes to recovery we need L0 Backup and all L1 backups including Archive logs need to be apply.

2.2. Cumulative Incremental Backup

A Cumulative Level 1 backup captures all blocks changed since the last Level 0.

Key Characteristics

Slightly larger than differential backups.

Faster recovery (fewer incremental files to apply).

Preferred for large, mission-critical systems.

When it comes to recovery we need L0 and recent L1 Cumulative backup including Archive logs need to be apply.

Understanding RMAN ( Recovery Manager) and Backups

 RMAN Key Points:

    - RMAN (Recovery Manager)

    - Oracle Database backup and recovery automation

    - It Introduced in Oracle 8i version

    - It performs block level backup

    - Parallelism

    - Detecting corruption in datafiles

    - Validating the backup

    - Incremental Backup

    - Recovery Catalog etc

    - Multi Destination Backups

    - Archive log mode is must to use RMAN

RMAN is faster because it takes block level backups.

RMAN is faster because we can initiate parallel processes.

RMAN will data block corruptions and repair it for you.

RMAN stores backup metadata information in the database control file, you can also store the information into deferent database on different server which is called as RECOVERY catalog

RMAN Incremental Recovery

              - Level 0 - Full database backup

              - Level 1 - Backup of only changed blocks taken by referring data block header for updated SCN

              -  

              - As per recovery, we must only restore the database from level 0 and recovery the Database using level 1. When you try to take level 1 backup, RMAN checks if already you have level 0 backup or not. If not then it will take level 0 backup automatically.

Recovery is of two parts:

              - Restore and Recover - We cannot perform recovery without restore. Hence Level 0 is used for restore and level 1 is used for recovery.

RMAN configuration Items

              - show all;

RMAN Utility:

RMAN utility comes with Oracle Binaries

No special installation or license required for using RMAN

At command prompt just type rman

It defaults connects to database environmental variables defined

RMAN utility can be used only when your DB is in at least MOUNT stage

RMAN is used while the DB is up and running and have very little performance impact if the backup is running

RMAN Backup Methodology:

- Full Backup

        - Entire Database backup plus archive logs 

        - Cannot apply Incremental backup on full backup

                            

- Incremental Backup

        - Level 0 - FULL Database backup

        - Level 1 - Backup changes only from last incremental backup

- Differential Backup 

                - Cumulative Backup

Components of RMAN:

  - RMAN Prompt

  - Target Database

  - Recovery Catalog

  - Auxiliary Database --- When cloning, Clone DB is called as auxiliary Database

  - Media Management Layer --- layer between RMAN 3rd part vendor backup tools netbackup,  Veritos and Tape backups

  - RMAN Channels

RMAN Configuration Parameter:

- RETENTION POLICY - tells till what date our backup will be stored which we can use for recovery

               - Redundancy -- How many backups to be retained

               - Recover Window -- How many days backup to be retained

        - Channels -- You can define channel to take backup to disk or tape

        - Control file auto backup -- includes control file and spfile auto backup

        - Parallelism -- Creates multiple processes to speed up backup

        - Encryption -- to secure the backup

Backups and RMAN Backups ( Recovery Manager)

 

Why we need backup

The primary purpose of taking backups is to protect the database from data loss and ensure the ability to reconstruct the database in case of corruption, errors, or failures.

Categories of Database Failure:

Data loss can occur at any time

       Human errors, accidental deletions, logical corruption, hardware failures, or storage issues can lead to partial or complete data loss.

Backups preserve data integrity

    When data corruption or inconsistencies arise, backups enable you to restore the database to a consistent and trustworthy state.

Support for disaster recovery

    In the event of serious failures—like server crashes, media corruption, or site outages—backups are the foundation for bringing the database back online.

Compliance and business continuity

    Many industries require proper backup policies to meet audit standards and ensure uninterrupted business operations.

Backup Types:

Oracle backups are broadly classified into two categories:

1. Logical Backups

Logical backups extract database objects in a logical format.

Examples: Table-level, Schema-level and Tablespace-level export

Tools Used: Data Pump (expdp / impdp) and  Export/Import (exp / imp)

Logical backups are mainly used for:

    Application-level migrations

    Object-level recoveries

    Copying data between databases

2. Physical Backups

Physical backups involve copying actual database files at the OS level.

Files Included Datafiles, Control files, SPFILE and Archived redo logs

Types of Physical Backups

    RMAN Backups (most recommended)

    Hot Backup (User‑Managed Online Backup)

    Cold Backup (User‑Managed Offline Backup)

Hot and Cold Backups

Hot and Cold backups are known as manual backups, as they are performed manually without using RMAN or any backup tool.

Cold Backup (Consistent Backup)

    Database is shut down.

    All database files are copied at OS level.

    Backup is consistent.

    No recovery required during restore.

    Also referred to as a consistent backup.

Hot Backup (Inconsistent Backup)

    Database is open and available to users.

    Tablespaces are placed in backup mode.

    Datafiles are copied manually at OS level.

    Recovery is required because the backup is inconsistent.

    Archived logs are needed for full recovery.

Backup States / Backup Types in Oracle

Oracle backups fall under two main categories based on the state of the database:

1) Consistent Backup

    A backup is consistent when: The database is shut down cleanly using SHUTDOWN IMMEDIATE / NORMAL / TRANSACTIONAL.

All files (datafiles, control files, redo logs) are synchronized.

2) Inconsistent Backup

A backup is inconsistent when the database is in OPEN or MOUNT mode during the backup.

Files are not synchronized; hence recovery is mandatory.

Examples

    Hot Backup (User‑managed online backup)

    RMAN Backups (always inconsistent unless database is closed)

Oracle RMAN (Recovery Manager)

Oracle Recovery Manager (RMAN) is a powerful, built‑in Oracle database utility used for performing backup, restore, and recovery operations. It is a no‑cost, command‑line tool provided by Oracle to help DBAs protect critical database files—including datafiles, control files, SPFILEs, and archived redo logs.

RMAN integrates tightly with the Oracle database engine, enabling advanced features such as:

Block‑level corruption detection

RMAN scans blocks during backup and restore operations, identifying corrupted blocks early and ensuring data integrity.

Automated backup retention policies

You can define retention periods or redundancy levels, and RMAN will automatically manage obsolete backups.

Backup compression

Built‑in compression reduces storage costs and speeds up backup operations.

Parallelization

File identification

Block recovery

Media recovery

RMAN can run backup and restore operations across multiple channels, improving performance and meeting high‑availability needs.

What to Backup 

Parameter file / SPfile , Control file , archive log files and data files

What file needed in different stages of database 

1) Shutdown - Nothing needed

2) No Mount - Pfile or SPfile

3) Mount - Control file

4) Open - Data and redo log files

Recovery:

       1. Complete Recovery

          --- Recovering the database exactly till the point of failure

                                          

       2. Incomplete Recovery

          --- It cannot recover the database till the point of failure

  --- You can recover the database till the time you had taken the backup

SQL Server 2022 In place Upgrade - Fails and resumes , succeeded, But SSRS failed to install.

 During in place upgrade of SQL Server 2022, Installation may fail with below error or warning, but it will end with successful message. During SSRS or PBIRS installation it may fail with Second error as shown below -

First Error/Warning During upgrade

Second Error SSRS/PBI RS:

UnexpectedError: setup failed for packageId: SSReportingServicesSetup, package: Microsoft SQL Server Reporting Services, errorCode: -2147483648, errorMessage: Something went wrong, please find details in setup logs.

Resolution :

Sometimes the SQL Server WMI namespace (root\Microsoft\SqlServer\ComputerManagement) is missing or corrupted.

Run this in Command Prompt:

mofcomp "C:\Program Files (x86)\Microsoft SQL Server\150\Shared\sqlmgmproviderxpsp2up.mof"

Note: make sure you are in the right folder and right version of the SQL Server.

SQL Server Configuration Manager Missing

 Go to respective shared folder and register - 

SQL Server Daily Health Check - Script

/* =======================================================================

   SQL Server Health Check – HTML Email (Final)

   - DBA's Heart Beat

   ======================================================================= */

SET NOCOUNT ON;

DECLARE @html    NVARCHAR(MAX) = N'';

DECLARE @newline NVARCHAR(10)  = CHAR(13) + CHAR(10);

-- =========================

-- HTML Header + Styles

-- =========================

SET @html = @html + N'<html><head><style>

body { font-family: Segoe UI, Arial, sans-serif; font-size: 13px; color: #222; }

h3 { margin: 18px 0 8px; }

table { border-collapse: collapse; width: auto; max-width: 100%; }

th, td { border: 1px solid #ddd; padding: 6px 8px; text-align: left; }

th { background-color: #f2f2f2; }

tr.alert { color: #b00020; font-weight: 600; }

.small { color:#666; font-size:12px; }

</style></head><body>' + @newline;

-- =========================

-- SQL Server Version & EOL

-- =========================

DECLARE @Servername NVARCHAR(128) = CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128));

DECLARE @version    NVARCHAR(128) = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

DECLARE @edition    NVARCHAR(128) = CAST(SERVERPROPERTY('Edition')        AS NVARCHAR(128));

DECLARE @eol        NVARCHAR(256);

DECLARE @MajorVersion NVARCHAR(256);

SET @MajorVersion = CASE

     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '8%'    THEN 'SQL2000'

     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '9%'    THEN 'SQL2005'

     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '10.0%' THEN 'SQL2008'

     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '10.5%' THEN 'SQL2008 R2'

     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '11%'   THEN 'SQL2012'

     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '12%'   THEN 'SQL2014'

     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '13%'   THEN 'SQL2016'

     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '14%'   THEN 'SQL2017'

     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '15%'   THEN 'SQL2019'

     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '16%'   THEN 'SQL2022'

     ELSE 'Unknown/New Version'

END;

SET @eol = CASE

    WHEN @@VERSION LIKE '%2005%'    THEN 'Mainstream: 2011-04-12, Extended: 2016-04-12'

    WHEN @@VERSION LIKE '%2008 R2%' THEN 'Mainstream: 2014-07-08, Extended: 2019-07-09'

    WHEN @@VERSION LIKE '%2008%'    THEN 'Mainstream: 2014-07-08, Extended: 2019-07-09'

    WHEN @@VERSION LIKE '%2012%'    THEN 'Mainstream: 2017-07-11, Extended: 2022-07-12'

    WHEN @@VERSION LIKE '%2014%'    THEN 'Mainstream: 2019-07-09, Extended: 2024-07-09'

    WHEN @@VERSION LIKE '%2016%'    THEN 'Mainstream: 2021-07-13, Extended: 2026-07-14'

    WHEN @@VERSION LIKE '%2017%'    THEN 'Mainstream: 2022-10-11, Extended: 2027-10-12'

    WHEN @@VERSION LIKE '%2019%'    THEN 'Mainstream: 2025-02-28, Extended: 2030-01-08'

    WHEN @@VERSION LIKE '%2022%'    THEN 'Mainstream: 2028-01-11, Extended: 2033-01-11'

    ELSE 'Unknown/New Version'

END;

SET @html = @html + N'<h3>SQL Server Version and EOL</h3><table><tr>

<th>Instance Name</th><th>Version</th><th>PatchLevel</th><th>Edition</th><th>EOL Info</th></tr>';

SET @html = @html + N'<tr><td>' + @Servername + N'</td><td>' + @MajorVersion + N'</td><td>' +

             @version + N'</td><td>' + @edition + N'</td><td>' + @eol + N'</td></tr></table>' + @newline;

-- =========================

-- Uptime (Start + Duration)

-- =========================

SET @html = @html + N'<h3>SQL Server Uptime</h3><table><tr><th>Start Time</th><th>Up Since</th></tr>';

DECLARE @start DATETIME = (SELECT sqlserver_start_time FROM sys.dm_os_sys_info);

DECLARE @now   DATETIME = GETDATE();

DECLARE @diffSeconds BIGINT = DATEDIFF(SECOND, @start, @now);

DECLARE @days   INT    = @diffSeconds / 86400;

DECLARE @hours  INT    = (@diffSeconds % 86400) / 3600;

DECLARE @mins   INT    = (@diffSeconds % 3600) / 60;

DECLARE @secs   INT    = @diffSeconds % 60;

SELECT @html = @html +

    N'<tr><td>' + CONVERT(NVARCHAR(19), @start, 120) + N'</td><td>' +

    CAST(@days AS NVARCHAR(10)) + N' days ' +

    CAST(@hours AS NVARCHAR(10)) + N' hrs ' +

    CAST(@mins AS NVARCHAR(10)) + N' mins ' +

    CAST(@secs AS NVARCHAR(10)) + N' secs</td></tr>';

SET @html = @html + N'</table>' + @newline;

-- =========================

-- Offline Databases

-- =========================

SET @html = @html + N'<h3>Offline Databases</h3>';

IF EXISTS (SELECT 1 FROM sys.databases WHERE state_desc = 'OFFLINE')

BEGIN

    SET @html = @html + N'<table><tr><th>Database</th><th>Status</th></tr>';

    SELECT @html = @html + N'<tr><td>' + name + N'</td><td>' + state_desc + N'</td></tr>'

    FROM sys.databases WHERE state_desc = 'OFFLINE';

    SET @html = @html + N'</table>' + @newline;

END

ELSE

BEGIN

    SET @html = @html + N'<p>All databases are online.</p>' + @newline;

END

/* =======================================================================

   Database Default Locations + All Disks (Fixed Drive Letters only)

   WMI via OLE Automation – decimal math for Free%

   ======================================================================= */

-----------------------------

-- Database Default Locations

-----------------------------

DECLARE @DefaultData   NVARCHAR(4000) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(4000));

DECLARE @DefaultLog    NVARCHAR(4000) = CAST(SERVERPROPERTY('InstanceDefaultLogPath')  AS NVARCHAR(4000));

DECLARE @DefaultBackup NVARCHAR(4000);

BEGIN TRY

    SET @DefaultBackup = CAST(SERVERPROPERTY('InstanceDefaultBackupPath') AS NVARCHAR(4000));

END TRY

BEGIN CATCH

    SET @DefaultBackup = NULL;

END CATCH;

IF @DefaultBackup IS NULL OR LTRIM(RTRIM(@DefaultBackup)) = ''

BEGIN

    DECLARE @bk NVARCHAR(4000);

    EXEC master.dbo.xp_instance_regread

        N'HKEY_LOCAL_MACHINE',

        N'Software\Microsoft\MSSQLServer\MSSQLServer',

        N'BackupDirectory',

        @bk OUTPUT,

        'no_output';

    SET @DefaultBackup = @bk;

END

SET @html = @html + N'<h3>Database Default Locations</h3>'

           + N'<table><tr><th>Setting</th><th>Path</th></tr>'

           + N'<tr><td>Default Data</td><td>' + ISNULL(@DefaultData,'') + N'</td></tr>'

           + N'<tr><td>Default Log</td><td>'  + ISNULL(@DefaultLog,'')  + N'</td></tr>'

           + N'<tr><td>Default Backup</td><td>' + ISNULL(@DefaultBackup,'') + N'</td></tr>'

           + N'</table>' + @newline;

---------------------------------------------

-- All Disks: Fixed Drive Letters only (no \\?\Volume)

---------------------------------------------

IF OBJECT_ID('tempdb..#AllDisks') IS NOT NULL DROP TABLE #AllDisks;

CREATE TABLE #AllDisks

(

    DriveLetter NVARCHAR(10) NOT NULL,  -- 'C:'

    TotalBytes  BIGINT       NOT NULL,

    FreeBytes   BIGINT       NOT NULL

);

DECLARE @hr INT = 0, @locator INT = NULL, @svc INT = NULL, @set INT = NULL, @item INT = NULL, @count INT = 0, @i INT = 0;

BEGIN TRY

    -- Connect to WMI

    EXEC @hr = sp_OACreate 'WbemScripting.SWbemLocator', @locator OUT;

    IF @hr <> 0 BEGIN RAISERROR('Failed to create WMI locator (sp_OACreate).', 16, 1); RETURN; END;

    EXEC @hr = sp_OAMethod @locator, 'ConnectServer', @svc OUT, '.', 'root\cimv2';

    IF @hr <> 0 BEGIN RAISERROR('Failed to connect to WMI root\cimv2.', 16, 1); RETURN; END;

    -- Query: Fixed disks with letters (DriveType=3)

    EXEC @hr = sp_OAMethod @svc, 'ExecQuery', @set OUT,

        'SELECT DeviceID, Size, FreeSpace FROM Win32_LogicalDisk WHERE DriveType=3';

    IF @hr <> 0 BEGIN RAISERROR('WMI ExecQuery failed for Win32_LogicalDisk.', 16, 1); RETURN; END;

    EXEC @hr = sp_OAGetProperty @set, 'Count', @count OUT;

    SET @i = 0;

    WHILE @i < ISNULL(@count, 0)

    BEGIN

        EXEC @hr = sp_OAMethod @set, 'ItemIndex', @item OUT, @i;

        IF @hr <> 0 BREAK;

        DECLARE @DeviceID NVARCHAR(50), @Size NUMERIC(38,0), @Free NUMERIC(38,0);

        EXEC @hr = sp_OAGetProperty @item, 'DeviceID',  @DeviceID OUT;

        EXEC @hr = sp_OAGetProperty @item, 'Size',      @Size     OUT;

        EXEC @hr = sp_OAGetProperty @item, 'FreeSpace', @Free     OUT;

        -- Insert only if Size is present (>0)

        IF @Size IS NOT NULL AND @Size > 0

        BEGIN

            INSERT INTO #AllDisks(DriveLetter, TotalBytes, FreeBytes)

            VALUES (UPPER(@DeviceID), CONVERT(BIGINT, @Size), CONVERT(BIGINT, @Free));

        END

        SET @i = @i + 1;

    END

END TRY

BEGIN CATCH

    DECLARE @err NVARCHAR(4000) = ERROR_MESSAGE();

    RAISERROR('WMI/OLE disk collection failed: %s', 16, 1, @err);

END CATCH

-- Cleanup WMI objects

IF @set     IS NOT NULL BEGIN EXEC sp_OADestroy @set;     END;

IF @svc     IS NOT NULL BEGIN EXEC sp_OADestroy @svc;     END;

IF @locator IS NOT NULL BEGIN EXEC sp_OADestroy @locator; END;

-- Render All Disks (decimal math for Free%)

SET @html = @html + N'<h3>All Disks</h3>'

           + N'<table><tr>'

           + N'<th>Drive</th><th>Total (GB)</th><th>Free (GB)</th>'

           + N'<th>Used (GB)</th><th>Free (%)</th></tr>';

;WITH calc AS

(

    SELECT

        Drive      = DriveLetter,

        totalGB    = CONVERT(DECIMAL(18,2), TotalBytes / 1024.0 / 1024 / 1024),

        freeGB     = CONVERT(DECIMAL(18,2), FreeBytes  / 1024.0 / 1024 / 1024),

        usedGB     = CONVERT(DECIMAL(18,2), (TotalBytes - FreeBytes) / 1024.0 / 1024 / 1024)

    FROM #AllDisks

),

calc2 AS

(

    SELECT

        Drive, totalGB, freeGB, usedGB,

        freePct = CONVERT(DECIMAL(5,2), CASE WHEN totalGB > 0 THEN (freeGB * 100.00) / totalGB ELSE 0 END)

    FROM calc

)

SELECT @html = @html +

       CASE WHEN freePct < 10 THEN N'<tr class="alert">' ELSE N'<tr>' END +

       N'<td>' + Drive + N'</td><td>' +

       CONVERT(NVARCHAR(50), totalGB) + N'</td><td>' +

       CONVERT(NVARCHAR(50), freeGB)  + N'</td><td>' +

       CONVERT(NVARCHAR(50), usedGB)  + N'</td><td>' +

       CONVERT(NVARCHAR(50), freePct) + N'</td></tr>'

FROM calc2

ORDER BY Drive;

SET @html = @html + N'</table>' + @newline;

DROP TABLE #AllDisks;

-- =========================

-- Backup Status (exclude tempdb & mirrored DBs)

-- =========================

SET @html = @html + N'<h3>Backup Status (Failed or No Backup in Past 2 Days)</h3>';

IF EXISTS (

    SELECT 1

    FROM sys.databases d

    LEFT JOIN msdb.dbo.backupset bs ON bs.database_name = d.name

    LEFT JOIN sys.database_mirroring dm ON d.database_id = dm.database_id

    WHERE d.name <> 'tempdb'

      AND d.is_read_only = 0

      AND dm.mirroring_guid IS NULL

    GROUP BY d.name

    HAVING MAX(bs.backup_finish_date) IS NULL

        OR MAX(bs.backup_finish_date) < DATEADD(DAY, -2, GETDATE())

)

BEGIN

    SET @html = @html + N'<table><tr><th>Database</th><th>Last Backup</th></tr>';

    SELECT @html = @html + N'<tr><td>' + d.name + N'</td><td>' +

           COALESCE(CONVERT(NVARCHAR(19), MAX(bs.backup_finish_date), 120), N'No Backup') + N'</td></tr>'

    FROM sys.databases d

    LEFT JOIN msdb.dbo.backupset bs ON bs.database_name = d.name

    LEFT JOIN sys.database_mirroring dm ON d.database_id = dm.database_id

    WHERE d.name <> 'tempdb'

      AND d.is_read_only = 0

      AND dm.mirroring_guid IS NULL

    GROUP BY d.name

    HAVING MAX(bs.backup_finish_date) IS NULL

        OR MAX(bs.backup_finish_date) < DATEADD(DAY, -2, GETDATE());

    SET @html = @html + N'</table>' + @newline;

END

ELSE

BEGIN

    SET @html = @html + N'<p>All backups successful (within last 2 days).</p>' + @newline;

END

-- =========================

-- Mirroring Status

-- =========================

SET @html = @html + N'<h3>Mirroring Status</h3>';

IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL)

BEGIN

    SET @html = @html + N'<table><tr><th>Database</th><th>Mirroring State</th><th>Safety Level</th></tr>';

    SELECT @html = @html + N'<tr><td>' + d.name + N'</td><td>' +

           ISNULL(dm.mirroring_state_desc, N'Unknown') + N'</td><td>' +

           ISNULL(dm.mirroring_safety_level_desc, N'Unknown') + N'</td></tr>'

    FROM sys.databases d

    JOIN sys.database_mirroring dm ON d.database_id = dm.database_id

    WHERE dm.mirroring_guid IS NOT NULL;

    SET @html = @html + N'</table>' + @newline;

END

ELSE

BEGIN

    SET @html = @html + N'<p>No mirroring configured.</p>' + @newline;

END

-- =========================

-- Replication Status (GUID-safe + resilient + idempotent)

-- =========================

SET @html = @html + N'<h3>Replication Status</h3>';

IF DB_ID('distribution') IS NOT NULL

BEGIN

    IF OBJECT_ID('distribution.dbo.MSreplication_monitordata') IS NOT NULL

    BEGIN

        -- Clean up any leftovers from previous runs in this session

        IF OBJECT_ID('tempdb..#DA')           IS NOT NULL DROP TABLE #DA;

        IF OBJECT_ID('tempdb..#MM')           IS NOT NULL DROP TABLE #MM;

        IF OBJECT_ID('tempdb..#JobLast')      IS NOT NULL DROP TABLE #JobLast;

        IF OBJECT_ID('tempdb..#MonitorRepl')  IS NOT NULL DROP TABLE #MonitorRepl;

        /* 1) Distribution Agents with publication + subscriber */

        SELECT

            p.publisher_db,

            a.publication,

            a.subscriber_db,

            a.job_id,

            job_id_str = UPPER(CONVERT(NVARCHAR(36), a.job_id))

        INTO #DA

        FROM distribution.dbo.MSdistribution_agents AS a

        INNER JOIN distribution.dbo.MSpublications   AS p

                ON p.publisher_id = a.publisher_id

               AND p.publication  = a.publication;

        /* 2) Monitor data: normalize job_id string (trim, uppercase, remove braces) */

        SELECT

            publisher_db       = m.publisher_db,

            publication        = m.publication,

            job_id_str         = NULLIF(UPPER(REPLACE(REPLACE(LTRIM(RTRIM(m.job_id)), '{', ''), '}', '')), ''),

            [Status]           = m.[Status],

            [Warning]          = m.[Warning],

            cur_latency        = m.cur_latency

        INTO #MM

        FROM distribution.dbo.MSreplication_monitordata AS m

        WHERE m.agent_type = 3  -- distribution agents

          AND NULLIF(LTRIM(RTRIM(m.job_id)), '') IS NOT NULL;

        /* 3) Last job outcome for each agent (fallback when MM has no rows) */

        SELECT

            j.job_id,

            job_id_str = UPPER(CONVERT(NVARCHAR(36), j.job_id)),

            last_status = ISNULL(h_last.run_status, -1)  -- -1 = unknown

        INTO #JobLast

        FROM msdb.dbo.sysjobs AS j

        INNER JOIN #DA        AS da ON da.job_id = j.job_id

        OUTER APPLY (

            SELECT TOP (1) run_status

            FROM msdb.dbo.sysjobhistory

            WHERE job_id = j.job_id AND step_id = 0

            ORDER BY run_date DESC, run_time DESC

        ) AS h_last;

        /* 4) Final projection: prefer MM status; else fallback to job history; else Unknown */

        SELECT DISTINCT

            da.publisher_db,

            da.publication,

            da.subscriber_db,

            Status =

                COALESCE(

                    /* MM status mapping */

                    CASE mm.[Status]

                        WHEN 1 THEN 'Started'

                        WHEN 2 THEN 'Succeeded'

                        WHEN 3 THEN 'In Progress'

                        WHEN 4 THEN 'Idle'

                        WHEN 5 THEN 'Retrying'

                        WHEN 6 THEN 'Failed'

                        ELSE NULL

                    END,

                    /* Job history mapping (fallback) */

                    CASE jl.last_status

                        WHEN 0 THEN 'Failed'

                        WHEN 1 THEN 'Succeeded'

                        WHEN 2 THEN 'Retrying'

                        WHEN 3 THEN 'Canceled'

                        WHEN 4 THEN 'In Progress'

                        ELSE 'Unknown'

                    END,

                    'Unknown'

                )

        INTO #MonitorRepl

        FROM #DA AS da

        LEFT JOIN #MM AS mm

               ON (mm.job_id_str   = da.job_id_str)

              OR (mm.publisher_db  = da.publisher_db AND mm.publication = da.publication)

        LEFT JOIN #JobLast AS jl

               ON jl.job_id        = da.job_id;

        /* 5) Render HTML */

        SET @html = @html + N'<table><tr>'

            + N'<th>Publisher DB</th><th>Publication</th><th>Subscriber DB</th><th>Status</th>'

            + N'</tr>';

        IF EXISTS (SELECT 1 FROM #MonitorRepl)

        BEGIN

            SELECT @html = @html + N'<tr><td>' + publisher_db + N'</td><td>' + publication

                             + N'</td><td>' + subscriber_db + N'</td><td>' + Status + N'</td></tr>'

            FROM #MonitorRepl

            ORDER BY publisher_db, publication, subscriber_db;

        END

        ELSE

        BEGIN

            SET @html = @html + N'<tr><td colspan="4">No replication agent rows found.</td></tr>';

        END

        SET @html = @html + N'</table>' + @newline;

        /* 6) Cleanup */

        IF OBJECT_ID('tempdb..#MonitorRepl') IS NOT NULL DROP TABLE #MonitorRepl;

        IF OBJECT_ID('tempdb..#JobLast')     IS NOT NULL DROP TABLE #JobLast;

        IF OBJECT_ID('tempdb..#MM')          IS NOT NULL DROP TABLE #MM;

        IF OBJECT_ID('tempdb..#DA')          IS NOT NULL DROP TABLE #DA;

    END

    ELSE

    BEGIN

        SET @html = @html + N'<p>No replication activity object (MSreplication_monitordata) found in distribution database.</p>' + @newline;

    END

END

ELSE

BEGIN

    SET @html = @html + N'<p>No replication configured.</p>' + @newline;

END

-- =========================

-- Always On Availability Groups

-- =========================

SET @html = @html + N'<h3>Always On Availability Groups</h3>';

IF EXISTS (SELECT 1 FROM sys.availability_groups)

BEGIN

    SET @html = @html + N'<table><tr>

        <th>Availability Group</th>

        <th>Replica Server</th>

        <th>Role</th>

        <th>Database</th>

        <th>Sync State</th>

        <th>Health</th>

    </tr>';

    ;WITH ags AS (

        SELECT DISTINCT

               ag.name                           AS AGName,

               ar.replica_server_name            AS ReplicaServer,

               ars.role_desc                     AS RoleDesc,

               adc.database_name                 AS DBName,

               dhs.synchronization_state_desc    AS SyncState,

               dhs.synchronization_health_desc   AS Health

        FROM sys.availability_groups ag

        JOIN sys.availability_replicas ar

             ON ag.group_id = ar.group_id

        JOIN sys.dm_hadr_availability_replica_states ars

             ON ar.replica_id = ars.replica_id

        JOIN sys.availability_databases_cluster adc

             ON ag.group_id = adc.group_id

        JOIN sys.dm_hadr_database_replica_states dhs

             ON ars.replica_id = dhs.replica_id

            AND dhs.group_database_id = adc.group_database_id

    )

    SELECT @html = @html + N'<tr><td>' + AGName + N'</td><td>' + ReplicaServer + N'</td><td>' +

           RoleDesc + N'</td><td>' + DBName + N'</td><td>' + SyncState + N'</td><td>' + Health + N'</td></tr>'

    FROM ags

    ORDER BY AGName, ReplicaServer, DBName;

    SET @html = @html + N'</table>' + @newline;

END

ELSE

BEGIN

    SET @html = @html + N'<p>No Always On Availability Groups configured.</p>' + @newline;

END

-- =========================

-- HTML Footer

-- =========================

SET @html = @html + N'</body></html>';

-- =========================

-- Send Email

-- =========================

DECLARE @sub VARCHAR(MAX);

SET @sub = 'Alert: SQL Server Health Check Report - ' + CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128));

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Default',

    @recipients   = 'DBA@sqlserver.com',

    @subject      = @sub,

    @body         = @html,

    @body_format  = 'HTML';