【SqlServer】自动备份数据库

2023-01-08 12:25:48

前言

由于使用了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