Hey there! You can automate this task by setting up a job on the SQL server. There are various methods you can explore online to achieve this. I find the SQL Server Agent to be quite handy for this purpose.

Navigate to the SQL Server Agent section in the SQL Object Explorer and proceed to create a new job.

Job properties:

• Add a Step

In the Command section, insert the backup script. In this example, we're performing a full backup of a system folder. Ensure that each backup file is uniquely named with the current date appended to it, guaranteeing that all backups are preserved.

DECLARE @BackupFileName NVARCHAR(500);
DECLARE @DateTimeString NVARCHAR(50);
DECLARE @FormattedDateTimeString NVARCHAR(50);
SET @DateTimeString = REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(50), GETDATE(), 120), ':', ''), '-',''), ' ', '_'); -- Format: YYYYMMDD
SET @FormattedDateTimeString = 'C:\SQL Server\Backups\genfli0_' + @DateTimeString + '.bak';
BACKUP DATABASE GENFLI0 TO DISK = @FormattedDateTimeString;

In this example each backup file follows the naming convention 'getfli0_YYYYMMDD_HHMMSS.bak', where 'YYYYMMDD_HHMMSS' represents the current date and time, ensuring uniqueness for each backup.

• Add the Schedule

Moreover, tailor the job schedule according to your requirements.

To do this, you can set up a schedule that aligns with your preferred timing and frequency.

Note:

Before you proceed, ensure that the SQL Server Agent is up and running. You can verify this by checking either the SQL Server Configuration Manager or navigating to Computer Management > Services.

If you have any questions, feel free to reach out to us.