SQL Server automatic backup, automatic compression, automatic deletion of old backup, SQL Server backup Compression

SQL Server automatic backup, automatic compression, automatic deletion of old backup, SQL Server backup Compression

Preparation tools:
SqlServer2008
WinRar

1. Set a scheduled backup for SqlServer
This operation is relatively simple. Simply add a maintenance plan according to the wizard.

2. Automatic compression of scripts

c:\Progra~2\WinRar\winRar.exe a D:\bak\database_buckup_%date:~0,4%_%date:~5,2%_%date:~8,2% d:\bak\database_*%date:~0,4%_%date:~5,2%_%date:~8,2%_*.bak 

The backup file format is database_backup_2015_05_15_0100020.5555338.bak,
The compressed file format is data_2015_06_15.rar.

3. automatically delete the old backup
Set a scheduled task and delete the old backup before compression. The bat script is used to call the vbs script because the vbs script is relatively easy to write.

AutoDeleteOld. bat

cscript d:/bak/autoDeleteOldBak.vbs

AutoDeleteOldBak. vbs

Const reMainCount = 1' reserved several dayDim fso, dc, file, files, folder, subfolders, subfolderset fso = createobject ("Scripting. fileSystemObject ") set folder = fso. getfolder ("D: \ bak") Dim todaytoday = Nowfor each tempFile in folder. files dim arr = Split (tempFile, "_ backup _") if UBound (arr)> 0 then dim tempDate, diff tempDate = Cdate (Replace (Left (arr (1 ), 10), "_", "-") diff = DateDiff ("d", tempDate, today) if (diff> reMainCount) then fso. deleteFile (tempFile), true end if end ifnextset fso = nothing

If there is another ftp server, you can compress the file and remotely synchronize it to another server.