Friday, March 30, 2012

How to FTP DB backup File from one server to the other

I would like to set up sort of schedule by using FTP to move database back up file from Production server to Staging server every night. Is someone telling me how to do this and any other good way to handle it? I appreciate help!
JQIf both servers are on your network, make a DTS package that does the backup and "On success" run a DOS batch command to copy the .BAK file from server to server. Then Schedule the package.|||tdudley,

thanks for your help! Can you give me more details for how to do that. I am kind of new to DTS package.

JQ|||Choose add new package:

Select the connection icon for SQL Server and point to the database of your choice.

Select the Task "Execute SQL Task" Input something like this into the "SQL Statement:" box: BACKUP DATABASE [YourDatabaseNameHere] TO DISK = N'e:\MSSQL\BACKUP\YourDatabaseNameHere.bak' WITH INIT , NOUNLOAD , NAME = N'Backup YourDatabaseNameHere', NOSKIP , STATS = 10, NOFORMAT

Then add another task "Execute process task" place your dos bat command call like this "E:\Files\Ftp\YourDatabaseNameHere.bat" in the "Win32 process:" box.

Then click the Execute SQL Task hold control and click the execute process task click workflow and choose "On Success"

Save this locally to SQL Server as say "OFF_SITE_BACKUP" and then schedule the job. Hope this was helpful.

No comments:

Post a Comment