Hi Kedalene,

This is possible. A backup job in MS SQL is a maintenance job, and a maintenance job is just a bunch of SQL code, i.e. a query. To achieve your goal you can make your own adaptation of this query, and run it in a normal MS SQL job.

If you do not have the maintenance (backup) job setup in MS SQL yet, first please do so.

Next we get the query from the maintenance job:

1. Open up the maintenance job in MS SQL Server Management Studio
   

   

2. sub 1 double click on the backup part of the maintenance job. Please note that your's may or may not have the Maintenance part.
   sub 2 click the View T-SQL button

   

3. A window will open from which you can see and even copy the necessary T-SQL code. This code contains a section where the name of the backup file gets determined. This part of the code, you can adapt the same way as you would any other query.
   
4. Go to the SQL Server Agent, and then to Jobs
   
5. Right click Jobs, and create a new job. Give it a name, and select the Steps page. Create a new step by clicking the New button, and give it a name. In the Command box write or paste your adapted code.

   

6. Finally make sure to setup the required schedule or schedules to run this job, from the Schedules page.

Regards,

Johan

Hi Johan

Thanks for your tip. How to edit this part to achieve

XXX_MON_1, XXX_TUES_1?

BACKUP DATABASE [SCO_Live] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SCO_Live_backup_2017_05_25_123639_8983299.bak' WITH NOFORMAT, NOINIT, NAME = N'SCO_Live_backup_2017_05_25_123639_8983299', SKIP, REWIND, NOUNLOAD, STATS = 10

Hi

Please guide how edit to rename auto backups to Mondays to Sundays instead of Calendar dates?

Hi Kedalene,

We only need to manipulate the path and file name. Easiest is to separate that from the rest of the code:

/* The folder to save the backup files to */
DECLARE @Path AS NVARCHAR(MAX) = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\'


/* The name of the backup file */
DECLARE @FileName AS NVARCHAR(MAX) = 'SCO_Live_backup_' 
                                   + DATENAME(dw, GETDATE()) + '_' 
                                   + CAST(YEAR(GETDATE()) AS NVARCHAR) + '_'
                                   + DATENAME(MM, GETDATE()) + '_'
                                   + CAST(DAY(GETDATE()) AS NVARCHAR)
                                   + '.bak'


/* Combine path and file name */
DECLARE @PathAndFileName AS NVARCHAR(MAX) = @Path + @FileName


/* The command that creates the backup file to the given folder */
BACKUP DATABASE [SCO_Live] TO DISK = @PathAndFileName WITH NOFORMAT
                                                             , NOINIT
                                                             , NAME = @FileName
                                                             , SKIP
                                                             , REWIND
                                                             , NOUNLOAD
                                                             , STATS = 10

Please note that I am not entirely sure what exact format you want for your file name. The code above will create a file name that looks like this:

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SCO_Live_backup_Tuesday_2017_May_30.bak

You can change the format to match your needs by switching lines around, deleting them, or by adding similar lines. You may want to avoid spaces and special characters.

Regards,

Johan