SQL Server 2005Express自动备份

说明：本文源自：http://www.mssqltips.com/tip.asp?tip=1486

提出问题：
  
       由于很多在线的商业应用均构建于SQL Server 2005 Express Edition的作为他们的后端数据库，我们需要确保我们运行在这些事例上的系统和用户数据库上正常备份。不幸的是，SQL Server 2005 Express Edition并不具有SQL代理功能，我们通常会创建一个数据库维护计划备份所有数据库。我们如何执行备份我们的系统和用户数据库，我们如何做，在其他版本？

解决方案：
   
      我们可以使用VB脚本和T-SQL来完成这些数据库备份自动化过程。

注意：

      所有文件应保存在E:/SQL_Backup/Scrips目录下，当然，该目录也可以手动修改，本例使用此目录。

步骤一：创建T-SQL脚本

      下面的T-SQL脚本产生的数据库备份同数据库维护计划生成的相似，考虑到备份文件生成的时间，这里我们将此脚本保存为一个.sql文件，位于E:/SQL_Backup/Scripts/backupDB.sql，这样可以使用sqlcmd来调用。

DECLARE @dateString CHAR(12), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2)  --定义月变量 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))  --定义天变量  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))  --定义小时变量  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))  --定义分变量  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))  --定义基于当前时间戳变量  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

步骤二：创建VBScript文件

接下来要做的是创建一个用于定期清理较早的数据库备份的VBScrip脚本，该脚本并记录日志中删除的备份文件。

注意：需要在E:/SQL_Backup/scripts/目录下创建一个空文件log.txt，用于保存删除文件的日志记录

On Error Resume Next    Dim fso, folder, files, sFolder, sFolderTarget      Set fso = CreateObject("Scripting.FileSystemObject")    '保存数据库备份文件路径  sFolder = "E:/SQL_Backup/"  Set folder = fso.GetFolder(sFolder)    Set files = folder.Files      '用于写入文本文件，并生成删除数据库备份报告 Const ForAppending = 8  '在scripts下创建一个空txt文件：Log.txt Set objFile = fso.OpenTextFile(sFolder & "/scripts/LOG.txt", ForAppending)  objFile.Write "================================================================" & VBCRLF & VBCRLF  objFile.Write "                     数据库备文件报告                " & VBCRLF  objFile.Write "                     日期:  " &    FormatDateTime(Now(),1)   & "" & VBCRLF  objFile.Write "                     时间:  " &    FormatDateTime(Now(),3)   & "" & VBCRLF & VBCRLF  objFile.Write "================================================================" & VBCRLF   '枚举备份文件目录文件 For Each itemFiles In files      '获取要删除文件的文件名    a=sFolder & itemFiles.Name     '获取文件扩展名    b = fso.GetExtensionName(a)         '检查扩展名是否为BAK        If uCase(b)="BAK" Then             '检查数据库备份是否为3天以前            If DateDiff("d",itemFiles.DateCreated,Now()) >= 3 Then                 '删除旧备份                fso.DeleteFile a                  objFile.WriteLine "备份文件已删除: " & a             End If         End If  Next    objFile.WriteLine "================================================================" & VBCRLF & VBCRLF  objFile.Close  Set objFile = Nothing  Set fso = Nothing  Set folder = Nothing  Set files = Nothing

步骤三：创建调用T-SQL和VBScript文件的批处理

     下面我们需要创建一个用来调用T-SQL和VBScript脚本文件的批处理文件，将其保存到E:/SQL_Backup/scripts/databaseBackup.cmd。

REM Run TSQL Script to backup databases sqlcmd -S<INSTANCENAME>-E -i"E:/SQL_Backup/scripts/backupDB.sql" REM Run database backup cleanup script E:/SQL_Backup/scripts/deleteBAK.vbs

步骤四：创建Windows计划任务

      在Windows计划任务中创建一个每天运行的上步骤3创建的批处理任务，可以通过“控制面板”－》“计划任务”或“所有程序”－》“附件”－》“系统工具”－》“计划任务”中找到。

     由于这里我们使用的是Windows验证来运行T-SQL，因此运行账号必须是所有数据库db_backupoperator角色的成员

     执行“计划任务”
     点击“添加计划任务”
     浏览到“E:/SQL_Backup/scripts”目录，选择“databaseBackup.cmd”     
     选择备份运行的时间