使用作業備份單個或所有sqlserver資料庫

點擊 管理／sql server代理／作業
新增作業：
常規選項卡裡,"名稱"填寫"定時備份資料庫"，"啟用"前面選"對勾"，"以本機伺服器為目標"，
 "分類"選擇"資料庫服務"，
步驟選項卡裡，建立步驟，步驟名：備份資料庫；類型：Transact-SQL指令碼(TSQL);

 資料庫：要備份的資料庫
 命令（可同時備份多個資料庫）：

DECLARE
 @FileName VARCHAR(200),
 @CurrentTime VARCHAR(50)

SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)

SET @FileName = 'D:\CE_BPS\DataBaseBackup\DPC_TEXT' + @CurrentTime
BACKUP DATABASE [CE_BPS_DPC_TEXT] TO DISK = @FileName WITH NOINIT, NOUNLOAD, NAME = N'CE_BPS_DPC_TEXT-備份', NOSKIP, STATS = 10, NOFORMAT

SET @FileName = 'D:\CE_BPS\DataBaseBackup\DPC_IMAGE' + @CurrentTime
BACKUP DATABASE [CE_BPS_DPC_IMAGE] TO DISK = @FileName WITH NOINIT, NOUNLOAD, NAME = N'CE_BPS_DPC_IMAGE-備份', NOSKIP, STATS = 10,NOFORMAT

 

備份所有使用者資料庫

 
 1 DECLARE
 2     @FileName VARCHAR(200),
 3     @CurrentTime VARCHAR(50),
 4     @DBName VARCHAR(100),
 5     @SQL VARCHAR(1000)
 6
 7 SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)
 8
 9 DECLARE CurDBName CURSOR FOR SELECT NAME FROM Master..SysDatabases where dbid>4
10 OPEN CurDBName
11     FETCH NEXT FROM CurDBName INTO @DBName
12
13     WHILE @@FETCH_STATUS = 0
14     BEGIN    
15         --Execute Backup
16         SET @FileName = 'D:\backup\' + @DBName + @CurrentTime
17         SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName +
18             ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
19         EXEC(@SQL)
20
21         --Get Next DataBase
22         FETCH NEXT FROM CurDBName INTO @DBName
23     END
24 CLOSE CurDBName
25 DEALLOCATE CurDBName
26