主要方式

利用.bat 執行 sqlcmd 去直接執行備份指令
然後使用工作排程去定時運行

直接上我的內容當作範例

@echo off
setlocal enabledelayedexpansion
set "server=instance1\SQLEXPRESS"
set "database=TestDB"
set "username=sa"
set "password=saPassword"
set "backupPath=C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup"

for /f "delims=" %%a in ('wmic os get localdatetime ^| find "."') do set "dt=%%a"
set "dateTime=!dt:~0,14!"
set "backupFile=!database!Backup_!dateTime!.bak"

sqlcmd -S %server% -d %database% -U %username% -P %password% -Q "BACKUP DATABASE %database% TO DISK='%backupPath%%backupFile%'"

來說明一下.bat 內容

@echo off
setlocal enabledelayedexpansion
set "server=你的SQL server名稱"
set "database=這裡放你的資料庫名稱"
set "username=登入者名稱"
set "password=登入者密碼"
set "backupPath=要存入的bakcup資料夾路徑"

將資料庫名稱加入時間戳
for /f "delims=" %%a in ('wmic os get localdatetime ^| find "."') do set "dt=%%a"
set "dateTime=!dt:~0,14!"
set "backupFile=!database!Backup_!dateTime!.bak"

最後就可以執行sqlcmd去做這件事情啦
sqlcmd -S %server% -d %database% -U %username% -P %password% -Q "BACKUP DATABASE %database% TO DISK='%backupPath%%backupFile%'"

echo Backup completed: %backupPath%%backupFile%

不過要注意使用的使用者要有sysadmin 或是 db_backupoperator 權限才可以做這件事情唷

當我們測試完點兩下有產出備份檔案
就可以運用工作排程定時做備份咯!