Aplicatii necesare:

Sqlcmd utility

Microsoft Command Line Utilities 15.0.x for SQL Server (x64) (https://go.microsoft.com/fwlink/?linkid=2082790)
Microsoft Command Line Utilities 15.0.x for SQL Server (x86) (https://go.microsoft.com/fwlink/?linkid=2082695)

Program de arhivare WinRAR (https://www.rarlab.com/download.htm) sau 7-Zip (https://www.7-zip.org/download.html)

Intr-un director de lucru C:Backup se creeaza fisierele backup.sql si backup.cmd (fisier text cu extensia .bat sau .cmd)

Se copiaza in fisierul backup.sql urmatorul script si se configureaza.

BACKUP DATABASE $(dbName) TO DISK = N’C:Backupbackup.sqlbkp’
WITH NOFORMAT, INIT, NAME = N’$(dbName) Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Se copiaza in fisierul backup.cmd urmatorul script si se configureaza:

@echo off

cd /d C:Backup
set sqlcmd_dir=
set winrar_dir=
rem set 7zip_dir=

set host=
set user=
set password=
set instanceName=
set dbName=

for /f “skip=1 usebackq delims=” %%D (`wmic os localdatetime ^| findstr /r /v “^$”`) do (
set today=%%D
)

“%sqlcmd_dir%SQLCMD.EXE” -S (local)%instanceName% -i C:Backupbackup.sql –v dbName=%dbName%

“%winrar_dir%winRar.exe” a –afzip backup-%today%.zip backup.sqlbkp
rem “%7zip_dir%7zG.exe” a backup-%today%.7z backup.sqlbkp

del /f /q backup.sqlbkp

Se testeaza functionarea corecta a scriptului.

Se creeaza un nou task in Task Scheduler (taskschd.msc).

La General se selecteaza Run whether user is logged on or not si Run with highest privileges.

La Triggers  se specifica cat de des sa ruleze scriptul de backup (ex. Zilnic la ora 00:00).

La Actions , programul care dorim sa ruleze este sciptul creat C:Backupbackup.cmd