This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(54.400000000000006, 54%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ER%3C/text%3E%3C/svg%3E)
I want to configure a maintenance plan to back up about 20 databases each week. This I did already but as the databases are backed up weekly the files in the backup location will become numerous and hard to find the backup I want to use. I found and modified a script that will create a folder with the current date/time on the backup drive. The problem I am having is getting the backups to automatically save into the folder created with the current date as that folder name will be different each week. I have tried to create separate agent jobs for each task (folder creation, database backup) but it always fails on the backup job. I saw one answer online that suggestion a T-SQL query can't be used to do this but I would have to create a procedure but that has not been successful for me yet. Looking for ideas, solutions, or to draw on community experience.
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
Additional SQL Server features and topics not covered by specific categories
Well I resolved it with a simple solution. I had created a SQL server Agent Job that would create the backups in a previous step then run a simple PowerShell script to create the desired folders by date and move my weekly backups into the appropriately dated folder. The holdup was so simple as I just had to give that service account permission to write to the backup drive.
I really appreciate the quick comments.
Why re-inventing the wheel, use Ola Hallengren's backup script; most DBA do that.
https://ola.hallengren.com/sql-server-backup.html
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
As Olaf said, forget maintenance plans and use Ola's scripts. You will be much happier in the end.