[SqlServerExpress] automatic database backup

preface

Because the Express version is used and there is no SqlServer job function, all have the following idea of backing up the database.

Deletes the backup database files before the specified number of days

The following contents are saved as DeleteDB.vbs

be careful:

(1)sFolder, the folder where the backup bak files are saved
(2)iSaveFileDay is the database bak file that was deleted several days ago
(3)C:\SQLScript\Log.txt is the log file of the operation execution process. The file needs to be created in advance. The following script will not create the file

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 "Database backup file scheduled purge Report" &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 "Backup file deleted: " &a
            End If
        End If
Next  
 
objFile.WriteLine "================================================================" &VBCRLF
 
objFile.Close
 
Set objFile = Nothing
Set objFSO = Nothing
Set folder = Nothing
Set files = Nothing

Delete the log data of how many days before the specified table name in all databases

The following contents are saved as DeleteRequestRecord.sql

In the sql statement, you need to fill in the table name and field name. The following statement execution example: delete from requestrecord where starttime < '2021-11-03 00:00:00'

DECLARE @IDENT INT=0--database Id，Initialize 0
DECLARE @Sql VARCHAR(MAX)=''--To be executed sql sentence
DECLARE @SqlDeleteLog VARCHAR(MAX)=''--Delete statement
DECLARE @DBNAME VARCHAR(200)=''--Database name

DECLARE @LogTableName VARCHAR(2000)='RequestRecord'--Table name of the log table
DECLARE @LogTimeName VARCHAR(2000)='StartTime'--Date field of the deleted record in the log table

DECLARE @NowTime DATETIME=GETDATE();--current time 
DECLARE @DeleteTime DATETIME=DATEADD(DAY,-10,@NowTime);--The date to delete, that is, the current time minus 10 days
DECLARE @DeleteTimeStr VARCHAR(100)=CONVERT(varchar(100),@DeleteTime,23)+' 00:00:00';--format: yyyy-MM-dd 00:00:00

--Create temporary table
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
    --The name of the current database
    SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT
    PRINT('===================================   '+@DBNAME+'   =====================================   Start execution');

    --Empty the temporary table first
    DELETE FROM #Table_LogTableName;

    --Insert the log table of the current database into the temporary table
    SET @Sql='INSERT INTO #Table_LogTableName SELECT [name] from '+@DBNAME+'..SysObjects WHERE XType=''U'' AND [name]='''+@LogTableName+''' ';
    PRINT(@SQL);
    EXEC(@SQL);

    --Whether there is a log table in the temporary table and do different operations
    IF(EXISTS(SELECT * FROM #Table_LogTableName WHERE TableName=@LogTableName))
    BEGIN
        PRINT 'existence';
        SET @SqlDeleteLog=' DELETE FROM '+@DBNAME+'.dbo.'+@LogTableName+' WHERE '+@LogTimeName+'<'''+@DeleteTimeStr+''' ';
        PRINT(@SqlDeleteLog);
        EXEC(@SqlDeleteLog);
    END    
    ELSE 
    BEGIN
        PRINT 'non-existent';
    END
    
    PRINT('===================================   '+@DBNAME+'   =====================================   end of execution');
    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;

Back up all databases

Save the following as BackupDB.sql

Back up all databases to the C: \ sqlpack folder

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

Write operation to cmd

Save the following as 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

Add windows scheduled task

1. Run taskschd.msc to open the task scheduler

2. Select = > create task

3. General

(1) Enter a name

(2) Security option, select "run regardless of whether the user logs in". Note: if this option is not checked, this task will not be executed without logging in to the server.

After selection, there will be a prompt box for you to enter the login password of the current computer at the time of last saving

4. Trigger, select: daily, start at: 03:15

5. Operation, startup program: C:\SQLScript\Schedule.cmd

This is the path where the cmd file is saved, subject to the actual situation

6. Click OK and you will be prompted to enter the login password of the current login user.

Note 1: you cannot save without entering a password

Note 2: after the login password of the login user is modified later, the planning task cannot be executed

Posted by Tomcat13 on Tue, 02 Nov 2021 21:03:06 -0700

Programmer Group