8

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?

  • You can easily do it with the help of SqlBak sqlbak.com Just set a backup schedule. – Olek Nilson Apr 11 '17 at 10:34
9

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.

  • 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 ? – Senior Systems Engineer Apr 13 '11 at 2:06
2

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
"
1

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

1

Logshipping does this well too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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