SQL Database Automatic Backups

reddragn 1 Reputation point

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,538 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 25,726 Reputation points

    Why re-inventing the wheel, use Ola Hallengren's backup script; most DBA do that.

    No comments

  2. Tom Phillips 17,511 Reputation points

    As Olaf said, forget maintenance plans and use Ola's scripts. You will be much happier in the end.

    No comments