We plan to backup our SQL Server 2012 databases every night, FTP the backup files to our test servers and restore them. Then deploy the latest schemas to those databases. The reason for this is so we have the latest data with the latest schemas to test on.

I'm using Ola Hallengren's SQL Server Maintenance Solution to back up the databases and Core FTP to FTP the backup files.

The problem is that Ola Hallengren's SQL Server Maintenance Solution puts each backup file in a different folder and the Core FTP has been setup to FTP all the backup files from a single folder.

As I see it, I can:

  1. change the location the databases are backed up to
  2. change the way Core FTP is setup, or
  3. add some SQL script to the main store procedure to copy the backup files to a single folder
  4. find new FTP software that can FTP files from many subfolders with a single folder (any suggestions?)

Regarding #1, on his website, Ola Hallengren doesn't recommend changing the backup location

DatabaseBackup backs up to a directory with the SQL Server instance name, the database name, and the backup type in the directory path. Can I change this behavior?

DatabaseBackup has been designed to delete backup files only when the backup and verification were successful. Even then, DatabaseBackup deletes only backups of the same instance, database, and type. Therefore, you’re guaranteed to always have the most recent backup on disk. If you remove any of this information from the directory path, you no longer have this guarantee.

This is because of how xp_delete_file works: xp_delete_file is the extended stored procedure that DatabaseBackup (and the maintenance plans) use to delete backup files. xp_delete_file deletes backup files based on a directory, a file extension, and a modified date.

Hopefully, there will be a reliable way to copy all the backup files to a single folder -- even when we add a new database.

EDIT: changed title of the question - used to be "Move SQL Server backup files to a single folder"

EDIT: added option #4 ("find new FTP software...")

  • I would have interpreted that to mean "do not move the files after they have been backed up" not necessarily meaning "do not change where the backups go". And, of course, msdb keeps track of where backups are first written. – RLF Jan 13 '15 at 23:24
  • I would go with option #2. – mrdenny Jan 14 '15 at 1:02
  • @RLF I agree, so I would copy the files instead of move them to a single folder – Craig HB Jan 14 '15 at 7:51

Your Answer

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

Browse other questions tagged or ask your own question.