SQL Server DB排程備份自動化

2020-11-02

SQL Server

開始寫SQL的備份script為Backup_SampleDB.sql

use SampleDB;

-- Define filename variable
declare @filename NVARCHAR(MAX)

-- Setup filename 
set @filename = 
(
    select N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\'
            + 'SampleDB_' 
            + FORMAT(GETDATE(), 'yyyyMMddHHmm') 
            + '_Cloud_Before' 
            + FORMAT(GETDATE(), 'MMdd')
            + 'Batch.bak' 
);

-- print filename
select @filename

BACKUP DATABASE [SampleDB] TO  DISK = @filename WITH NOFORMAT, NOINIT,  NAME = N'SampleDB-完整 資料庫 備份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

然後寫批次檔案

echo Start of backup SampleDB Database ... 

sqlcmd -U (帳號) -P (密碼)-i Backup_SampleDB.sql
pause
echo 'Check if database.bak file exist in C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup'
echo End of backup SampleDB Database ...

跑完就用排程去跑批次檔案

筆者是用System Scheduler工具，接者定義

開始跑會是