首先实现自动备份
在master数据库新建存储过程
USE
[
master
]
GO
/* ***** Object: StoredProcedure [dbo].[sp_BackupDatabase] Script Date: 10/19/2012 10:46:55 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Author: RyanDing
-- Create date: 2010-10-10
-- Description: 备份数据库
-- Parameter1: 数据库名
-- Parameter2: 备份类型 F=全部, D=差异, L=日志
CREATE PROCEDURE [ dbo ]. [ sp_BackupDatabase ]
@databaseName sysname, @backupType CHAR( 1)
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 = '' D:\db\backup\ ' + @databaseName + ' _Full_ ' + @dateTime + ' .BAK '''
IF @backupType = ' D '
SET @sqlCommand = ' BACKUP DATABASE ' + @databaseName +
' TO DISK = '' D:\db\backup\ ' + @databaseName + ' _Diff_ ' + @dateTime + ' .BAK '' WITH DIFFERENTIAL '
IF @backupType = ' L '
SET @sqlCommand = ' BACKUP LOG ' + @databaseName +
' TO DISK = '' D:\db\backup\ ' + @databaseName + ' _Log_ ' + @dateTime + ' .TRN '''
EXECUTE sp_executesql @sqlCommand
END
GO
GO
/* ***** Object: StoredProcedure [dbo].[sp_BackupDatabase] Script Date: 10/19/2012 10:46:55 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Author: RyanDing
-- Create date: 2010-10-10
-- Description: 备份数据库
-- Parameter1: 数据库名
-- Parameter2: 备份类型 F=全部, D=差异, L=日志
CREATE PROCEDURE [ dbo ]. [ sp_BackupDatabase ]
@databaseName sysname, @backupType CHAR( 1)
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 = '' D:\db\backup\ ' + @databaseName + ' _Full_ ' + @dateTime + ' .BAK '''
IF @backupType = ' D '
SET @sqlCommand = ' BACKUP DATABASE ' + @databaseName +
' TO DISK = '' D:\db\backup\ ' + @databaseName + ' _Diff_ ' + @dateTime + ' .BAK '' WITH DIFFERENTIAL '
IF @backupType = ' L '
SET @sqlCommand = ' BACKUP LOG ' + @databaseName +
' TO DISK = '' D:\db\backup\ ' + @databaseName + ' _Log_ ' + @dateTime + ' .TRN '''
EXECUTE sp_executesql @sqlCommand
END
GO
在 D:\db\backup 新建一个backup.sql文件,内容如下:
exec [sp_BackupDatabase] 'dbname','F';
go
(要备份多个数据库,请复制以上两行)
用计划任务执行备份,计划任务执行的程序
D:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE
程序参数: -S 本机名\SQLEXPRESS -E -i D:\db\backup\backup.sql
自动清理7天以前的备份文件:
新建一个bat文件,内容如下:
FORFILES /P D:\db\backup\ /D -7 /M *.bak /C "cmd /c del /f @path"
可以实现自动删除7天以前的文件
然后就是做计划任务来执行这个bat了
呵呵