SQL Server 2005自动备份及删除旧备份文件

     我们可以通过Windows中的任务管理，结合VBSript和TSQL来达到在SQl Server 2005 Express 版本中自动备份系统和数据的目的。

问题提出

当越来越多的多商业应用采用SQl Server 2005 Express 版本作为后台数据库的时候，我们必须确保能顺利的备份系统以及运行在实例上的用户数据库。不幸的是，SQl Server 2005 Express 版本没有SQL代理，也就不能使用SQL代理来创建可以备份所有数据库的数据库维护计划。那么对于SQl Server 2005 Express 版本，我们如何像其它版本一样来备份系统和数据呢?

专家解答

我们可以通过Windows中的任务管理，结合VBSript和TSQL来达到在SQl Server 2005 Express 版本中自动备份系统和数据的目的。

注意：所有的文件都必须保存在如下文件夹：E:\SQL_Backup\scripts。这个目录可以改变。但在该例中设定的是这个目录。如果将文件保存到其它目录，必须相应的更新脚本中的目录设置。

第一步-创建TSQL脚本

以下的TSQL脚本将生成一个数据库备份文件，同时考虑到生成的日期和时间，其格式类似于数据库维护计划生成的备份文件。我们将之保存为后缀名.sql的文件：E:\SQL_Backup \scripts\backupDB.sql。该文件将在批处理命令文件中使用sqlcmd调用。

DECLARE @dateString CHAR(12), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2)

--month variable

IF (Select LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2

SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2))

ELSE

SET @monthSTR= '0' + CAST(MONTH(GETDATE()) AS CHAR(2))

--day variable

IF (Select LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2

SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2))

ELSE

SET @daySTR='0' + CAST(DAY(GETDATE()) AS CHAR(2))

--hour variable

IF (Select LEN(DATEPART(hh, GETDATE())))=2

SET @hourStr=CAST(DATEPART(hh, GETDATE()) AS CHAR(2))

ELSE

SET @hourStr= '0' + CAST(DATEPART(hh, GETDATE()) AS CHAR(2))

--minute variable

IF (Select LEN(DATEPART(mi, GETDATE())))=2

SET @minStr=CAST(DATEPART(mi, GETDATE()) AS CHAR(2))

ELSE

SET @minStr= '0' + CAST(DATEPART(mi, GETDATE()) AS CHAR(2))

--name variable based on time stamp

SET @dateString=CAST(YEAR(GETDATE()) AS CHAR(4)) + @monthStr + @dayStr + @hourStr + @minStr

--=================================================================

DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200)

Select @IDENT=MIN(database_id) FROM SYS.DATABASES Where [database_id] > 0 AND NAME NOT IN ('TEMPDB')

WHILE @IDENT IS NOT NULL

BEGIN

Select @DBNAME = NAME FROM SYS.DATABASES Where database_id = @IDENT

Select @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''E:\SQL_Backup\'+@DBNAME+'_db_' + @dateString +'.BAK'' WITH INIT'

EXEC (@SQL)

Select @IDENT=MIN(database_id) FROM SYS.DATABASES Where [database_id] > 0 AND database_id>@IDENT AND NAME NOT IN ('TEMPDB')

END

第二步-创建VBSript文件

接下来我们需要创建一个VBSript文件，它将负责清理旧的数据库备份文件。该脚本同时也将生成日志文件，记录数据库备份文件的变化。

1. 你需要创建一个名为E:\SQL_Backup\scripts\LOG.txt的空文件来保存删除文件的日志。

2. 将以下的脚本保存为E:\SQL_Backup\scripts\deleteBAK.vbs。

On Error Resume Next

Dim fso, folder, files, sFolder, sFolderTarget

Set fso = CreateObject("Scripting.FileSystemObject")

'location of the database backup files

sFolder = "E:\SQL_Backup\"

Set folder = fso.GetFolder(sFolder)

Set files = folder.Files

'used for writing to textfile - generate report on database backups deleted

Const ForAppending = 8

'you need to create a folder named “scripts” for ease of file management &

'a file inside it named “LOG.txt” for delete activity logging

Set objFile = fso.OpenTextFile(sFolder & "\scripts\LOG.txt", ForAppending)

objFile.Write "================================================================" & VBCRLF & VBCRLF

objFile.Write " DATABASE BACKUP FILE REPORT " & VBCRLF

objFile.Write " DATE: " & FormatDateTime(Now(),1) & "" & VBCRLF

objFile.Write " TIME: " & FormatDateTime(Now(),3) & "" & VBCRLF & VBCRLF

objFile.Write "================================================================" & VBCRLF

'iterate thru each of the files in the database backup folder

For Each itemFiles In files

'retrieve complete path of file for the DeleteFile method and to extract

'file extension using the GetExtensionName method

a=sFolder & itemFiles.Name

'retrieve file extension

b = fso.GetExtensionName(a)

'check if the file extension is BAK

If uCase(b)="BAK" Then

'check if the database backups are older than 3 days

If DateDiff("d",itemFiles.DateCreated,Now()) >= 3 Then

'Delete any old BACKUP files to cleanup folder

fso.DeleteFile a

objFile.WriteLine "BACKUP FILE DeleteD: " & a

End If

End If

Next

objFile.WriteLine "================================================================" & VBCRLF & VBCRLF

objFile.Close

Set objFile = Nothing

Set fso = Nothing

Set folder = Nothing

Set files = Nothing

第三步-创建调用TSQL脚本和VBSript的批处理命令文件

我们需要创建一个批处理命令文件，用来调用 TSQL脚本和VBSript脚本。批处理命令文件的内容是对sqlcmd.exe的简单调用、以及使用wscript.exe或者简单的调用 VBSript文件。将以下内容保存为E:\SQL_Backup\scripts\databaseBackup.cmd，这就是我们需要的批处理命令。

REM Run TSQL Script to backup databases

sqlcmd -S-E -i"E:\SQL_Backup\scripts\backupDB.sql"

REM Run database backup cleanup script

E:\SQL_Backup\scripts\deleteBAK.vbs

第四步-在Windows任务计划中创建任务

在windows任务计划中创建一个日常任务，用之来调用之前创建的批处理命令。任务计划的位置如下：开始菜单 -> 所有程序 -> 附件 -> 系统工具 ->任务计划。

因为我们运行TSQL脚本时开启了windows认证，因此需要使用具备数据库db_backupoperator权限的用户。

启动任务计划。

点击添加任务计划。

浏览目录“E:\SQL_Backup\scripts”，选择databaseBackup.cmd。

选择频率标签并设定运行备份的时间。

Lastly, enter a Windows account that has at least db_backupoperator role privileges for all of the databases

最后，输入一个windows账号。该帐号对所有数据库至少要具备db_backupoperator的角色权限