關於SQLSERVER備份

關於SQLSERVER備份--首先介紹2個檢視資料庫日誌使用情況很有用的語句：
--DBCC SQLPERF (LOGSPACE)
--DBCC LOGINFO('ZZTZ')

--確認SQLSERVER處於完全恢復模式
--確認SQLSERVER AGENT啟動。
--1、每週日凌晨一點開始執行資料庫全備份。
--2、每天凌晨四點開始執行資料庫增量備份。
--3、每天下午三點開始執行資料庫日誌備份。

--1、全庫備份(同時備份MASTER)
USE MSDB
EXEC SP_ADD_JOB @JOB_NAME = 'ZZTZ_BACKUP_FULL',
@ENABLED = 1,
@DESCRIPTION = '全庫備份',
@START_STEP_ID = 1,
@OWNER_LOGIN_NAME = 'SA'
DECLARE @BACKUPCOMMAND VARCHAR(300)
SELECT @BACKUPCOMMAND = 'DECLARE @FILE VARCHAR(100)
SELECT @FILE = ''E:SQLSERVERBACKUP''+CONVERT(VARCHAR,GETDATE(),12)+''ZZTZ_BACKUP_FULL''
BACKUP DATABASE ZZTZ TO DISK = @FILE WITH INIT
SELECT @FILE = ''E:SQLSERVERBACKUP''+CONVERT(VARCHAR,GETDATE(),12)+''MASTER''
BACKUP DATABASE MASTER TO DISK = @FILE WITH INIT'

EXEC SP_ADD_JOBSTEP @JOB_NAME = 'ZZTZ_BACKUP_FULL',
@STEP_NAME = 'ZZTZ_BACKUP_FULL',
@SUBSYSTEM = 'TSQL',
@COMMAND = @BACKUPCOMMAND ,
@DATABASE_NAME='ZZTZ'
GO

EXEC SP_ADD_JOBSCHEDULE @JOB_NAME = 'ZZTZ_BACKUP_FULL',
@NAME = 'ZZTZ_BACKUP_FULL' ,
@FREQ_TYPE = 8,
@FREQ_INTERVAL = 1,
@FREQ_RECURRENCE_FACTOR = 1,
@ACTIVE_START_TIME = 10000
GO

EXEC SP_ADD_JOBSERVER
@JOB_NAME='ZZTZ_BACKUP_FULL',
@SERVER_NAME='(LOCAL)'

--2、增量備份(同時備份MSDB)
USE MSDB
EXEC SP_ADD_JOB @JOB_NAME = 'ZZTZ_BACKUP_DIFFERENTIAL',
@ENABLED = 1,
@DESCRIPTION = '增量備份',
@START_STEP_ID = 1,
@OWNER_LOGIN_NAME = 'SA'

DECLARE @BACKUPCOMMAND VARCHAR(300)
SELECT @BACKUPCOMMAND = 'DECLARE @FILE VARCHAR(100)
SELECT @FILE = ''E:SQLSERVERBACKUP''+CONVERT(VARCHAR,GETDATE(),12)+''ZZTZ_BACKUP_DIFFERENTIAL''
BACKUP DATABASE ZZTZ TO DISK = @FILE WITH DIFFERENTIAL
SELECT @FILE = ''E:SQLSERVERBACKUP''+CONVERT(VARCHAR,GETDATE(),12)+''MSDB''
BACKUP DATABASE MSDB TO DISK = @FILE WITH INIT'

EXEC SP_ADD_JOBSTEP @JOB_NAME = 'ZZTZ_BACKUP_DIFFERENTIAL',
@STEP_NAME = 'ZZTZ_BACKUP_DIFFERENTIAL',
@SUBSYSTEM = 'TSQL',
@COMMAND = @BACKUPCOMMAND ,
@DATABASE_NAME='ZZTZ'
GO

EXEC SP_ADD_JOBSCHEDULE @JOB_NAME = 'ZZTZ_BACKUP_DIFFERENTIAL',
@NAME = 'ZZTZ_BACKUP_DIFFERENTIAL' ,
@FREQ_TYPE = 4,
@FREQ_INTERVAL = 1,
@ACTIVE_START_TIME = 40000
GO

EXEC SP_ADD_JOBSERVER
@JOB_NAME='ZZTZ_BACKUP_DIFFERENTIAL',
@SERVER_NAME='(LOCAL)'

--3、日誌備份
USE MSDB
EXEC SP_ADD_JOB @JOB_NAME = 'ZZTZ_BACKUP_LOG',
@ENABLED = 1,
@DESCRIPTION = '日誌備份',
@START_STEP_ID = 1,
@OWNER_LOGIN_NAME = 'SA'

DECLARE @BACKUPCOMMAND VARCHAR(300)

SELECT @BACKUPCOMMAND = 'DECLARE @FILE VARCHAR(100)
SELECT @FILE = ''E:SQLSERVERBACKUP''+CONVERT(VARCHAR,GETDATE(),12)+''ZZTZ_BACKUP_LOG''
BACKUP LOG ZZTZ TO DISK = @FILE'

EXEC SP_ADD_JOBSTEP @JOB_NAME = 'ZZTZ_BACKUP_LOG',
@STEP_NAME = 'ZZTZ_BACKUP_LOG',
@SUBSYSTEM = 'TSQL',
@COMMAND = @BACKUPCOMMAND ,
@DATABASE_NAME='ZZTZ'
GO

EXEC SP_ADD_JOBSCHEDULE @JOB_NAME = 'ZZTZ_BACKUP_LOG',
@NAME = 'ZZTZ_BACKUP_LOG' ,
@FREQ_TYPE = 4,
@FREQ_INTERVAL = 1,
@ACTIVE_START_TIME = 150000
GO

EXEC SP_ADD_JOBSERVER
@JOB_NAME='ZZTZ_BACKUP_LOG',
@SERVER_NAME='(LOCAL)'

為了確保線上日誌的安全性以便在災難發生時恢復到失效點，建議採用SQLSERVER的日誌傳送方案（LOGSHIPPING）實時備份線上日誌檔案.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242049/，如需轉載，請註明出處，否則將追究法律責任。