What is the best way to backup a SQL Server 2005 database nightly?
I'm looking for a solution/strategy that would make the database backup file, and place it in an "outgoing" directory. We're wanting our FTP software to move it out to an offsite server.
Any suggestions on how to make this work as desired?
Best Answer
You can do this free. Create a maintenance plan to back up the DB, you can define the location you want to send the file, and add a clean up task.
If it's express and you can't use maint plans, use this tool to run the backup job automatically: http://www.codeplex.com/ExpressMaint and use a simple VB script to clean up the folder as a windows scheduled task
Then create a script to FTP the logs home nightly as windows scheduled task.
We do pretty the exact same thing in one of our setups.
If the offsite server is on a constant VPN with the DB Server, you could DFS the backup folder.
EDIT: If you are wanting to this as "extra" full backup along side local bks, you'll need to use a TSQL statement for the backup job in the plan, and throw in the "COPY_ONLY" for the backup, so differentials aren't using that as their reference, but your local fulls as planned.