沒想到，SQL Server Agent 啟無法啟用
所以只好想到使用指令的方式進行備份，看你目前需要什麼樣的備份就執行以下的 script
1.完整備份
DECLARE
@backupTime VARCHAR(20)
DECLARE
@fileName VARCHAR(1000)
SELECT
@backupTime=(CONVERT(VARCHAR(8), GETDATE(), 112) +REPLACE(CONVERT(VARCHAR(5), GETDATE(), 114), ':', ''))
SELECT
@fileName='D:\Backup\DB\DBNAME_'+@backupTime+'.bak'
BACKUP DATABASE [DBNAME] TO DISK =@fileName WITH NOFORMAT, NOINIT, NAME = N'db-ALL', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
2.差異備份
DECLARE
@backupTime VARCHAR(20)
DECLARE
@backupWeek VARCHAR(2)
DECLARE
@fileName VARCHAR(1000)
SELECT
@backupTime=(CONVERT(VARCHAR(8), GETDATE(), 112) +REPLACE(CONVERT(VARCHAR(5), GETDATE(), 114), ':', ''))
SELECT
@backupWeek=datepark(wk,getdate())
SELECT
@fileName='D:\Backup\DB\DBNAME_'+@backupTime+'_'+@backupWeek+'.bak'
BACKUP DATABASE [DBNAME] TO DISK =@fileName WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'db_DIFFERENTIAL', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
3.交易備份
DECLARE
@backupTime VARCHAR(20)
DECLARE
@backupWeek VARCHAR(2)
DECLARE
@fileName VARCHAR(1000)
SELECT
@backupTime=(CONVERT(VARCHAR(8), GETDATE(), 112) +REPLACE(CONVERT(VARCHAR(5), GETDATE(), 114), ':', ''))
SELECT
@backupWeek=datepark(wk,getdate())
SELECT
@fileName='D:\Backup\DB\DBNAME_'+@backupTime+'_'+@backupWeek+'tran.bak'
BACKUP DATABASE [DBNAME] TO DISK =@fileName WITH NO_TRUNCATE, NOFORMAT, NOINIT, NAME = N'db_transaction', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
可以選擇其一種 存到sql script file (EX: dbbackup.sql)，再執行以下command 指令 進行執行
sqlcmd -S instanceName -E -i c:\dbbackup.sql
這樣就可以備份資料庫啦
再利用工作排程把這個指令加入排程即可
