Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 22, 2003

Backup--Transfer--FTP! - Page 2

By DatabaseJournal.com Staff

by MAK [Muthusamy Anantha Kumar]

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM