Home / server / Questions / 115492
Accepted
Urda
Asked: 2010-02-23 10:26:37 +0800 CST

Best way to backup a SQL Server database nightly?

  • 772

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?

sql-server sql-server-2005 backup

4 Answers

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

    • 9

  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
"
    • 2

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

    • 1

  4. Logshipping does this well too.

    • 1