关于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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/756652/viewspace-242049/