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.
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:
- change the location the databases are backed up to
- change the way Core FTP is setup, or
- add some SQL script to the main store procedure to copy the backup files to a single folder
- 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...")