dbms/README.md

# dbms

Collection for database management systems

* [`mssql-jobs-by-fsaver`](https://git.hmp.today/pavel.muhortov/dbms#mssql-jobs-by-fsaver)
* [`mysqldump-wrapper.sh`](https://git.hmp.today/pavel.muhortov/dbms#mysqldump-wrapper-sh)

____

## `mssql-jobs-by-fsaver`

**Description:**  
> running Microsoft SQL Server agent jobs by Effector Saver with handling of executing time and errors

**Dependencies:**  
>
> * [SQL Server](https://www.microsoft.com/en-us/sql-server) (tested version 2022 on [Windows Server 2022](https://learn.microsoft.com/en-us/windows-server/get-started/whats-new-in-windows-server-2022))
> * [SQL Server Management Studio](https://learn.microsoft.com/en-us/sql/ssms) (tested version 19.1 on [Windows Server 2022](https://learn.microsoft.com/en-us/windows-server/get-started/whats-new-in-windows-server-2022))
> * [Effector Saver](https://mixbackup.com/) (tested version 4.11 on [Windows Server 2022](https://learn.microsoft.com/en-us/windows-server/get-started/whats-new-in-windows-server-2022))

### Create maintenance plan with subplan name
>
> example maintanence plane name: `Daily-Database_Name`  
> example subplan name: `Backup`  

Microsoft SQL Server Management Studio -> Management -> Maintenance Plans -> New Maintenance Plan -> Name: `Daily-Database_Name` -> Subplan properties -> Name: `Backup`

### Check auto made agent job and its name

Microsoft SQL Server Management Studio -> SQL Server Agent -> Jobs -> `Daily-Database_Name.Backup`

### Create Effector Saver task

Effector Saver -> Tasks -> Add task -> Other tasks -> Commands -> Name: Daily-Database_Name -> Main preferences -> Add command

Name:

```text
Daily-Database_Name.Backup
```

Command type:

```text
SQL script
```

Command text:

```sql
SET NOCOUNT ON

DECLARE  @jobNM NVARCHAR(MAX) = 'Daily-Database_Name.Backup',
         @jobID UNIQUEIDENTIFIER,
         @maxID INT

SELECT @jobID = job_id FROM msdb..sysjobs WHERE name = @jobNM
SELECT @maxID = MAX(instance_id) FROM msdb..sysjobhistory WHERE job_id = @jobID AND step_id = 0
SET @maxID = COALESCE(@maxID, -1)

EXEC msdb..sp_start_job @job_id = @jobID
WHILE (SELECT MAX(instance_id) FROM msdb..sysjobhistory WHERE job_id = @jobID AND step_id = 0) = @maxID
    WAITFOR DELAY '00:00:01'

SELECT @maxID = MAX(instance_id) FROM msdb..sysjobhistory WHERE job_id = @jobID AND step_id = 0
IF (SELECT run_status FROM msdb..sysjobhistory WHERE instance_id = @maxID) = 0
    RAISERROR (N'#ERROR# Microsoft SQL Server Job finished with error', 16, 1) WITH NOWAIT

```

____

## `mysqldump-wrapper.sh`

**Description:**  
> creating database dump, copying to additional smb share, copies rotating
> and
> sending report to email

**Dependencies:**  
>
> * privileged rights
> * [mysqldump](https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html) (tested version 5.6.35 on [CentOS 7](https://wiki.centos.org/Manuals/ReleaseNotes/CentOS7.2009))
> * [gzip](https://www.gnu.org/software/gzip/manual/gzip.html) (tested version 1.5 on [CentOS 7](https://wiki.centos.org/Manuals/ReleaseNotes/CentOS7.2009))
> * [cifs-utils](https://wiki.samba.org/index.php/LinuxCIFS_utils) (tested version 2.08 on [CentOS 7](https://wiki.centos.org/Manuals/ReleaseNotes/CentOS7.2009))
> * [Python 3](https://www.python.org/downloads/) (tested version [3.9.5](https://git.hmp.today/pavel.muhortov/utils#build-python-sh) on [CentOS 7](https://wiki.centos.org/Manuals/ReleaseNotes/CentOS7.2009))
> * [sendmail.py](https://git.hmp.today/pavel.muhortov/utils#sendmail-py)

|  POSITION | PARAMETERS   |       DESCRIPTION      |    DEFAULT    |
|-----------|--------------|------------------------|---------------|
|     1     |**[/path/to/file.conf]**|path to config file|**REQUIRED**|

### Installing mysqldump-wrapper.sh and setting up crontab

```bash
# edit config
sudo tee /usr/local/bin/mysqldump-wrapper.conf > /dev/null <<'EOF'
# mysql connection parameters
db_host=db-server.domain.zone
db_user=db-username
db_pass=db-password
db_name=database

# dump repository parameters
dump_root=/home/user/backup
dump_save=1

# copy smb-repository parameters
smb_host=smb-server.domain.zone
smb_path=smb-share/backup
smb_user=smb-username
smb_domn=smb-domain
smb_pass=smb-password
copy_save=7

# sendmail parameters
smtp_pyth=/usr/local/opt/python-3.9/bin/python3.9
smtp_send=/usr/local/bin/sendmail.py
smtp_host=mail-server.domain.zone
smtp_port=587
smtp_from=mail-from@domain.zone
smtp_pass=mail-password
smtp_dest=mail-dest@domain.zone
EOF
```

```bash
# download
sudo wget https://git.hmp.today/pavel.muhortov/dbms/raw/branch/master/mysqldump-wrapper.sh -O /usr/local/bin/mysqldump-wrapper.sh
sudo chmod +x /usr/local/bin/mysqldump-wrapper.sh
```

```bash
# sudo sh -c "EDITOR=nano crontab -e"
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
00 20 * * * bash /usr/local/bin/mysqldump-wrapper.sh /usr/local/bin/mysqldump-wrapper.conf
```