SQL Database Automatic Backups

reddragn 1 Reputation point
2021-11-18T13:43:21.603+00:00

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.
12,692 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,741 Reputation points
    2021-11-18T14:40:09.16+00:00

    Why re-inventing the wheel, use Ola Hallengren's backup script; most DBA do that.
    https://ola.hallengren.com/sql-server-backup.html

    1 person found this answer helpful.
    0 comments
  2. Tom Phillips 17,716 Reputation points
    2021-11-18T18:03:03.457+00:00

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

    0 comments