STEP one:
在 master数据库中建立 备份的存储过程
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabase] Script Date: 12/16/2014 11:14:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Author: whl
-- Create date: 20141216
-- Description: 备份数据库
-- @databaseName: 数据库名
-- @backupType: 备份类型 F=全部, D=差异, L=日志
-- @backupDir: 备份文件的目录 如: d:\backup\
ALTER PROCEDURE [dbo].[sp_BackupDatabase]
@databaseName sysname, @backupType CHAR(1), @backupDir varchar(500)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
IF @backupType = 'F'
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
--' TO DISK = ''e:\back_file\' + @databaseName + '_Full_' + @dateTime + '.BAK'''
' TO DISK = '''+ @backupDir + @databaseName + '_Full_' + @dateTime + '.BAK'''
IF @backupType = 'D'
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = '''+ @backupDir + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'
IF @backupType = 'L'
SET @sqlCommand = 'BACKUP LOG ' + @databaseName +
' TO DISK = '''+ @backupDir + @databaseName + '_Log_' + @dateTime + '.TRN'''
EXECUTE sp_executesql @sqlCommand
END
TWO :
新建脚本文件 sqlserver_bk.sql
内容为
exec [sp_BackupDatabase] 'TESTDB','F','e:\back_file\'
go
THREE:
新建任务计划, 选择 程序或脚本 为 D:\install\SqlServer2008\100\Tools\Binn\SQLCMD.EXE 程序,
参数为: -S . -E -i e:\back_file\sqlserver_bk.sql 即可,其他的定时时间依照需要设定即可
至此, 备份就可以啦