用批处理文件自动备份SQLServer数据库

 

@ECHO off

REM Backup Database Script Log Files
SET dbbak_dir=D:\DBBak\
if not exist %dbbak_dir% mkdir %dbbak_dir%

REM Backup Database Name
SET B_Database_Name=dtest
SET B_DATE=%date:~0,4%%date:~5,2%%date:~8,2%
ECHO use master ;>%dbbak_dir%FULL_backup.sql
ECHO go >>%dbbak_dir%FULL_backup.sql
ECHO BACKUP DATABASE %B_Database_Name% >>%dbbak_dir%FULL_backup.sql
ECHO TO DISK = '%dbbak_dir%%B_Database_Name%_%B_DATE%_FULL.bak' >>%dbbak_dir%FULL_backup.sql
ECHO WITH INIT , NOUNLOAD ;>>%dbbak_dir%FULL_backup.sql
ECHO go >>%dbbak_dir%FULL_backup.sql

REM osql.exe path
SET OSQL_PATH=C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\

REM Database user name and password
SET DBUser=sa
SET DBpassword=password
ECHO BACKUP DATABASE
"%OSQL_PATH%osql.exe" -U %DBUser% -P %DBpassword% -i %dbbak_dir%FULL_backup.sql -o %dbbak_dir%%B_Database_Name%_%B_DATE%_FULL.log

REM Delete old backup files
FORFILES /P %dbbak_dir% /D -7 -M %B_Database_Name%*.bak /C "cmd /c del
@file"
FORFILES /P %dbbak_dir% /D -7 -M %B_Database_Name%*.log /C "cmd /c del
@file"

REM Delete Temp files
DEL /Q %dbbak_dir%FULL_backup.sql