SQL Server backup file management, and offsite backup

SQL Server backup file management, and offsite backup

1.sqlserver executes exec managerdb.dbo automatically. [Del_bak] Backup db

Managerdb.dbo. [Del_bak] Related scripts see the following links:

http://dwchaoyue.blog.51cto.com/2826417/1439126

2.window Automation Task D:\script\Backup.bat compressed files to the backup server

The file D:\script\Backup.bat content is as follows:

RAR a-k-r-s-m3 d:\winrardb\%date:~0,4%%date:~5,2%%date:~8,2% D:\bak

Copy D:\winrardb\%date:~0,4%%date:~5,2%%date:~8,2%.rar z:\

Note:z:\ is a network drive.

3.sqlserver Automate EXEC ManagerDB.dbo.del_historyfile Delete historical backup files

The relevant scripts for ManagerDB.dbo.del_historyfile are as follows:

DECLARE @del_date varchar (100)

Set @del_date =left (CONVERT (Nvarchar,dateadd (Dd,1,getdate ()), +) + ' T ' +convert (Nvarchar,dateadd (Hh,0,getdate ( )), 108)

EXECUTE master.dbo.xp_delete_file 0,n ' D:\bak ', N ' bak ', @del_date

4.window Automation Task D:\script\delData\Del_History_files.vbs Delete expired compressed files

The file D:\script\delData\Del_History_files.vbs content is as follows:

DIM strdate

DIM Strdatestringold

DIM strpath

DIM FS

Strdate = Date-3

Strdatestringold=year (strdate) & Right ("0" & Month (Strdate), 2) & Right ("0" & Day (Strdate), 2) & ". rar "

strpath = "D:\WinRARDB\" &strdatestringold

FS =createobject ("Scripting.FileSystemObject")

If fs.fileexists (strpath) Then

Fs. DeleteFile (strpath)

End If

Window RAR Command Reference link

Http://www.sy15168.cn/news/html/1468.html

Note:winrar cannot use RAR command directly in CMD window after installation, adding environment variable can be used directly.

This article is from the "SQL Server MySQL" blog, so be sure to keep this source http://dwchaoyue.blog.51cto.com/2826417/1535276