【SqlServerExpress】自动备份数据库
前言
由于使用了Express版本,没有SqlServer作业功能,所有才有了下面的备份数据库的思路。
删除指定天数之前的备份数据库文件
下面的内容保存为DeleteDB.vbs
注意:
(1)sFolder,为保存备份bak文件的文件夹
(2)iSaveFileDay,为删除多少天之前的数据库bak文件
(3)C:\SQLScript\Log.txt,为操作执行过程的日志文件,需要预先创建好文件,下面的脚本不会创建文件
Dim objFSO, folder, files, sFolder, sFolderTarget ,iSaveFileDay Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "C:\SQLBack\" iSaveFileDay=2 Set folder = objFSO.GetFolder(sFolder) Set files = folder.Files Const ForAppending = 8 Set objFile = objFSO.OpenTextFile("C:\SQLScript\Log.txt", ForAppending) objFile.Write "================================================================" &VBCRLF objFile.Write "数据库备份文件定时清除报告" &VBCRLF objFile.Write "Time:" &FormatDateTime(Now(),1) &"" &FormatDateTime(Now(),3) &VBCRLF objFile.Write "================================================================" &VBCRLF For Each itemFiles In files a=sFolder &itemFiles.Name b = objFSO.GetExtensionName(a) If uCase(b)="BAK" Then If DateDiff("d",itemFiles.DateCreated,Now()) >= iSaveFileDay Then objFSO.DeleteFile a objFile.WriteLine "备份文件已删除: " &a End If End If Next objFile.WriteLine "================================================================" &VBCRLF objFile.Close Set objFile = Nothing Set objFSO = Nothing Set folder = Nothing Set files = Nothing
删除所有数据库中指定表名的多少天前的日志数据
下面的内容保存为DeleteRequestRecord.sql
sql语句中,需要填写好表名和字段名,下面的语句执行示例:delete from RequestRecord where StartTime<'2021-11-03 00:00:00'
DECLARE @IDENT INT=0--数据库Id,初始化0 DECLARE @Sql VARCHAR(MAX)=''--待执行的sql语句 DECLARE @SqlDeleteLog VARCHAR(MAX)=''--删除语句 DECLARE @DBNAME VARCHAR(200)=''--数据库名称 DECLARE @LogTableName VARCHAR(2000)='RequestRecord'--日志表的表名 DECLARE @LogTimeName VARCHAR(2000)='StartTime'--日志表的删除记录的日期字段 DECLARE @NowTime DATETIME=GETDATE();--当前时间 DECLARE @DeleteTime DATETIME=DATEADD(DAY,-10,@NowTime);--需要删除的日期,即当前时间减去10天 DECLARE @DeleteTimeStr VARCHAR(100)=CONVERT(varchar(100),@DeleteTime,23)+' 00:00:00';--格式化:yyyy-MM-dd 00:00:00 --创建临时表 CREATE TABLE #Table_LogTableName ( TableName VARCHAR(2000) ) CREATE TABLE #t_exclude ( name varchar(200) ) INSERT INTO #t_exclude SELECT 'master' UNION SELECT 'model' UNION SELECT 'msdb' UNION SELECT 'tempdb' SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE NAME NOT IN (SELECT [name] FROM #t_exclude) WHILE (@IDENT>0) BEGIN --当前数据库的名称 SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT PRINT('=================================== '+@DBNAME+' ===================================== 开始执行'); --先清空临时表 DELETE FROM #Table_LogTableName; --将当前数据库的日志表,插入到临时表 SET @Sql='INSERT INTO #Table_LogTableName SELECT [name] from '+@DBNAME+'..SysObjects WHERE XType=''U'' AND [name]='''+@LogTableName+''' '; PRINT(@SQL); EXEC(@SQL); --临时表是否存在日志表,去做不同的操作 IF(EXISTS(SELECT * FROM #Table_LogTableName WHERE TableName=@LogTableName)) BEGIN PRINT '存在'; SET @SqlDeleteLog=' DELETE FROM '+@DBNAME+'.dbo.'+@LogTableName+' WHERE '+@LogTimeName+'<'''+@DeleteTimeStr+''' '; PRINT(@SqlDeleteLog); EXEC(@SqlDeleteLog); END ELSE BEGIN PRINT '不存在'; END PRINT('=================================== '+@DBNAME+' ===================================== 执行结束'); PRINT(''); PRINT(''); PRINT(''); PRINT(''); SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE NAME NOT IN (SELECT [name] FROM #t_exclude) AND database_id>@IDENT END DROP TABLE #Table_LogTableName; DROP TABLE #t_exclude;
备份所有的数据库
下面的内容保存为BackupDB.sql
将所有的数据库备份到C:\SQLBack文件夹下面
declare @dateString varchar(10) select @dateString=CONVERT(varchar(100),GETDATE(),112) declare @IDENT int declare @sql varchar(1000) declare @DBNAME varchar(200) create table #t_exclude ( name varchar(200) ) insert into #t_exclude select 'master' union select 'model' union select 'msdb' union select 'tempdb' select @IDENT=MIN(database_id) from sys.databases where database_id>0 and [name] not in (select [name] from #t_exclude) while(@IDENT>0) begin select @DBNAME=[name] from sys.databases where database_id=@IDENT set @sql='BACKUP DATABASE '+@DBNAME+' TO DISK = ''C:\SQLBack\'+@DBNAME+'_db_' + @dateString +'.BAK'' WITH INIT' --print (@sql); exec (@sql); select @IDENT=MIN(database_id) from sys.databases where database_id>0 and [name] not in (select [name] from #t_exclude) and database_id>@IDENT end drop table #t_exclude
将操作写入到cmd
下面的内容保存为BackupDB.sql
cd C:\Program Files\Microsoft SQL Server\150\Tools\Binn\ sqlcmd -S iShej8ol9r0jqwE\SQLEXPRESS2019 -E -i"C:\SQLScript\DeleteRequestRecord.sql" sqlcmd -S iShej8ol9r0jqwE\SQLEXPRESS2019 -E -i"C:\SQLScript\BackupDB.sql" C:\SQLScript\DeleteDB.vbs
添加windows计划任务
1.运行taskschd.msc,打开任务计划程序
2.选择=>创建任务
3.常规
(1)输入名称
(2)安全选项,选择“不管用户是否登录都要运行”。备注:如果不勾选这个,在没有登录服务器时这个任务是不会执行的。
选择之后,在最后保存的时候,会有提示框让输入当前电脑的登录密码
4.触发器,选择:每天,开始于:03:15
5.操作,启动程序:C:\SQLScript\Schedule.cmd
此为,上面保存了cmd文件的路径,以实际情况为准
6.点击确定,会提示让输入当前登录用户的登录密码。
备注1:不输入密码就无法保存
备注2:登录用户的登录密码,后期修改了之后,计划任务就无法执行