The following script executes full backup of a database in SQL Server and transfers the backup file to another Server. Backup File naming rules: Database name_timestamp. BAK.
Execution Environment windows 2003 server SQL Server 2000
Source code:
@ Echo off
Rem backup database script log files
Set dbbak_dir = E: testdbbak
If not exist % dbbak_dir % mkdir % dbbak_dir %
Rem backup database name
Set B _database_name = db1
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
Rem echo sp_addumpdevice 'disk',> % dbbak_dir % full_backup. SQL
Rem echo '% B _database_name % _ % B _date % _ full',> % dbbak_dir % full_backup. SQL
Rem echo '% dbbak_dir % B _database_name % _ % B _date % _ full. Bak'; >>% dbbak_dir % full_backup. SQL
Rem 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
ECHO.
REM osql.exe path
SET OSQL_PATH = C: Program FilesMicrosoft SQL Server80ToolsBinn
REM Database user name and password
SET DBUser = sa
SET DBpassword = passwd
ECHO BACKUP DATABASE
"Export osql_path1_osql.exe"-U % DBUser %-P % DBpassword %-I % dbbak_dir % FULL_backup. SQL-o % dbbak_dir % B _Database_Name % _ % B _DATE % _ FULL. log
Rem FTP info
Set ftp_user = user1
Set ftp_pw = passwd
Set ftp_ip = 10.1.9.1
Set ftp_port = 21
Set ftp_dir =/home/dbbak
Set ncftp_path = D: Program filesncftp
"Export ncftp_path1_ncftpput.exe"-U % ftp_user %-P % ftp_pw %-P % ftp_port % ftp_ip % ftp_dir % dbbak_dir % B _database_name % _ % B _date % _ full .*
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
Note:
1. To avoid PORT and PASV problems of Windows FTP Client, use ncftp client
2. Keep the latest 7-day backup and use the FORFILES command in Resource kit to delete the old backup file.
3. the ftp target machine is a linux system. Use the following shell script to delete the old backup file.
#! /Bin/sh
Cd/home/dbbak/
If [[-s mm_db _ 'date + % Y % m % d' *. BAK]
Then
# Echo "not 0"
Find.-mtime + 7 | xargs rm
Fi
