SQL Server Automatic backup script

The following script performs a full backup of a database in SQL Server and passes the backup file to another machine. Backup file naming rule: Database name _ timestamp. Bak.

Execution Environment: Windows 2003 Server SQL Server 2000

@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
"%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 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
"%ncftp_path%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

Description

1. In order to avoid Windows with FTP client PORT, PASV problem, using NCFTP client

2. Keep the latest 7-day backup, use Resource Kit's command forfiles delete old backup files

3. FTP Purpose machine is a Linux system, with the following shell script delete old backup files

#!/bin/sh
cd /home/dbbak/
if [[ -s mm_db_`date +%Y%m%d`*.BAK ]]
then
#echo "not 0"
find . -mtime +7 | xargs rm
fi