Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

up vote 9 down vote accepted

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.

share|improve this answer
    
This seems to be the way to go. I'll check it out, but it looks like it is what I wanted. –  Urda Feb 23 '10 at 18:34
    
sure thing, let us know how it goes =) –  Malnizzle Feb 23 '10 at 19:11
    
ok, how do you backup the OLAP component then ? –  Server System Specialist Apr 13 '11 at 2:06

An easy script (albiet using an undocumented procedure) is below. This will put it in the default backup directory, but if your service account has rights to other directories you can add that in front of the last question mark. The "init" will over write the last database backup so it doesn't fill up the drive.

set quoted_identifier on

exec sp_MSforeachdb "
if ( '?' not in ( 'tempdb' ) )
begin
    backup database [?] to disk = '?.bak' with init, stats = 10
end
"
share|improve this answer

Backup Exec System Recovery will do the backup and FTP it offsite on any schedule

share|improve this answer

Logshipping does this well too.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.