主要方式

利用.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 权限才可以做这件事情唷

当我们测试完点两下有产出备份档案
就可以运用工作排程定时做备份咯!