Hi all
Is it possible save auto daily backup and name it as XXX_MON_1, XXX_MON_2, XXX_TUES_1, KSG_XXX_TUES_2…
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:
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