Backup--Transfer--FTP! (2)	3.0/5.0 (2 votes total) Rate: Select 1 - Terrible 2 - Poor 3 - Average 4 - Good 5 - Outstanding
MAK [Muthusamy Anantha Kumar] December 22, 2004

Step 4: Create Job

Create a new job.

Add step with the following statement:

exec master.dbo.USP_ftpbackup 'userdatabase','c:\temp\','c:\temp\ftp.txt', 'Database'

Schedule this job to run every day @ 9 AM.

Create another job with the job step using the following statement to backup Transaction log and transfer the file using FTP.

exec master.dbo.USP_ftpbackup 'userdatabase','c:\temp\','c:\temp\ftp.txt', 'log'

Schedule this job to run every hour.

When the Full backup job runs it:

• Executes the full database backup userdatabase to c:\temp folder on the SQL Server box c:\temp\userdatabase_200303180900.bak
  
• Creates dynamic FTP.txt on c:\temp folder with the following information
  
  myuserid
  mypassword
  CD /u/trannet
  binary
  put c:\temp\userdatabase_200303180900.bak
  quit
  
• Executes the FTP.bat under c:\temp\FTP.bat which in turn transfers the backup file to the desired FTP site.
  When the Transaction log backup job runs, it will do the above for every transaction log backup.

Conclusion:

The same process can be used for all of the databases in a server. The above process can be used not only for database backups, but also for transferring files using FTP. This process helps in simplifying the remote standby server process as well as remote disaster recovery file transfer processes.

Prev 1 2