SQL Backup Script

I make the following scripts to do a Backup for Microsoft SQL Server then compress it using RAR and after that it will be sent to an FTP Server

1. Create folder Named C:\backup and all backup will be there

2. Create the Backup File which will backup all user database and will be named as backup.sql

backup.sql

exec expressmaint

@database = 'ALL_USER',

@optype = 'DB',

@backupfldr = 'c:\backup\db',

@reportfldr = 'c:\backup\db',

@verify = 1,

@dbretainunit = 'days',

@dbretainval = 1,

@rptretainunit = 'weeks',

@rptretainval = 1,

@report = 1

3. Create FTP Send Command to Send the Data to Remote FTP Server - you shall have access to FTP Server The Following example user Name: SQLuser and password SQLpassword

ftpsend.ftp

user SQLuser

SQLPassword

cd BACKUP

cd DatabaseName

mput *.rar

quit

4. Creating the Backup Command File, which will Backup the Database using the (backup.sql) Generate a compressed backup file using Date and Time, after that sending the Files to the remote server using (ftpsend.ftp) and after it fininsh it will delete the RAR,BAK files.

dobackup.bat

md c:\backup

md c:\backup\db

sqlcmd -S . -i backup.sql

rar a -r c:\backup\backup.rar c:\backup\db\*.* -ag+YYYYMMDDHHMM

ftp -n -s:ftpsend.ftp ftp.hostname.com

del c:\backup\*.rar

del c:\backup\*.bak /s

5. Download RAR.EXE, UNRAR.EXE from http://www.rarlab.com and put it on the same directory for the script or backup folder.

6. Test your Backup by executing the Command doBackup.bat

7. Schedule your backup using Windows Scheduling by running "taskschd.msc" or from Control Panel - Administrative Tools - Task Scheduler

8. Follow the steps on the schedule for the backup procedure

Comments