SQL Server备份及备份文件处理可选方法之一

1.通过sql server 维护计划备份数据库,每个库独立建立文件夹

2.通过下面vbs脚本将文件复制到磁带可以备份的共享位置(权限可控),windows schedule定时执行;文件很大时,可直接磁带备本机或直接复制到对应位置.

 1 @echo off
 2 @REM 删除旧的备份
 3 @del \\server_name\sql_bck$\*.bak
 4 
 5 @Rem 取当天备份文件名
 6 set "bakname=*%date:~0,4%_%date:~5,2%_%date:~8,2%*.bak"
 7 @Rem 复制当天的备份
 8 
 9 copy D:\SQLDataBackup\folder1\%bakname%  \\server_name\sql_bck$\ >>d:\backupforsql.log
10 
11 copy D:\SQLDataBackup\folder2\%bakname%  \\server_name\sql_bck$\ >>d:\backupforsql.log
12 
13 copy D:\SQLDataBackup\folder3\%bakname%  \\server_name\sql_bck$\ >>d:\backupforsql.log
14 
15 echo
16 date /t >>d:\backupforsql.log
17 time /t >>d:\backupforsql.log
18 echo Backup END >>d:\backupforsql.log
19 echo ######################################################### >>d:\backupforsql.log
20 
21 @Rem pause

View Code

3.sql job或windows schedule定期删除过期的备份文件

1 DECLARE @OLDDATE DATETIME
2 SELECT @OLDDATE=dateadd(day,-60,GETDATE())
3 
4 EXECUTE master.dbo.xp_delete_file 0,N'D:\SQLDataBackup\A',N'bak',@olddate,1--
5 
6 EXECUTE master.dbo.xp_delete_file 0,N'D:\SQLDataBackup\B',N'bak',@olddate,1--\%bakname%  \\server_name\bck_sql$\ >>d:\backupforsql.log
7 
8 EXECUTE master.dbo.xp_delete_file 0,N'D:\SQLDataBackup\CY',N'bak',@olddate,1--\%bakname%  \\server_name\bck_sql$\ >>d:\backupforsql.log

View Code