0

I create a job for taking backups in MSSQL Server Management Studio and run backup script periodically. I also want to add date and time as the following format:

2020.10.16_18.00.00

I tried some methods, ut I think I have to use special format. How can I get the date as above without using SP, etc. just using date-time methods?

I tried this but not exactly gives the format I want.

select convert(varchar, getdate(), 120)
  • What do you mean by "add"? Add to the filename? Add to the data in the d/b? Add to a log file? – wallyk Oct 19 '20 at 16:10
  • Yes, add to the file name. Forget it, just concentrate on the format I provided. –  Oct 19 '20 at 16:12

1 Answers1

0

You can use FORMAT to customize the output:

SELECT FORMAT(GETDATE(), 'yyyy.MM.dd_hh.mm.ss')

Otherwise, you can manipulate the output with REPLACE:

SELECT REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', '.'), ':', '.'), ' ', '_')

This should allow you to define the path as variable:

DECLARE @Path NVARCHAR(500) = N'D:\backup\CurrencyExchange' + FORMAT(GETDATE(), 'yyyy.MM.dd_hh.mm') + '.log'
BACKUP LOG [CurrencyExchange] TO DISK = @Path
Jason W
  • 13,026
  • 3
  • 31
  • 62
  • What about addinbg this to the backup script? I tries like that but not worked. Should I declare as a variable? How? `BACKUP LOG [CurrencyExchange] TO DISK = N'D:\backup\CurrencyExchange' + SELECT FORMAT(GETDATE(), 'yyyy.MM.dd_hh.mm') + '.log' GO`. –  Oct 19 '20 at 16:23
  • You can remove the `SELECT`, and that should work fine – Jason W Oct 19 '20 at 16:26
  • Not worked, is there a special concatanation? Could you check please? –  Oct 19 '20 at 16:28
  • Maybe I need to convert to string? –  Oct 19 '20 at 16:29
  • I also tried like `BACKUP LOG [CurrencyExchange] TO DISK = N'D:\backup\CurrencyExchange' + SELECT CONVERT(VARCHAR, FORMAT(GETDATE(), 'yyyy.MM.dd_hh.mm')) + '.bak'` but still not working. –  Oct 19 '20 at 16:32
  • Maybe try assigning to variable first: `DECLARE @Path NVARCHAR(500) = N'D:\backup\CurrencyExchange' + FORMAT(GETDATE(), 'yyyy.MM.dd_hh.mm') + '.log' BACKUP LOG [CurrencyExchange] TO DISK = @Path` – Jason W Oct 19 '20 at 16:34
  • Either way, you should not need the `SELECT` – Jason W Oct 19 '20 at 16:34
  • Very strange, I also tried like that but gives *Must declare the scalar variable "@Path".* error. I also use `... + CONVERT(VARCHAR(12), FORMAT(GETDATE(), 'yyyy.MM.dd_hh.mm')) + '.bak'` –  Oct 19 '20 at 16:52
  • I also tried to use this approach: https://stackoverflow.com/questions/7181976/must-declare-the-scalar-variable But not solved –  Oct 19 '20 at 17:00
  • What if you try dynamic sql? `DECLARE @Sql NVARCHAR(MAX) = REPLACE(N'BACKUP LOG [CurrencyExchange] TO DISK = ''D:\backup\CurrencyExchange{date}.log''', '{date}', FORMAT(GETDATE(), 'yyyy.MM.dd_hh.mm')) EXEC sys.sp_executesql @Sql` – Jason W Oct 19 '20 at 17:03
  • But I do not want to use SP, just need a TSQL Backup script :( –  Oct 19 '20 at 17:14
  • Then I would think you'd have to go back to the variable approach. The `@Path` should be defined, but you have to run both statements together in same batch. I updated the answer with what I'd expect you would need. – Jason W Oct 19 '20 at 17:19
  • This time OK, but what if I use the previous dynamic sql? Will it create a SP? I have not used synamic before. –  Oct 19 '20 at 18:04
  • The dynamic sql does not create any SP. It just uses the "sp_executesql" SP that is provided by SQL Server already (https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15) – Jason W Oct 19 '20 at 20:16
  • Thansk a lot, marked as answer. On the other hand, when creating job script, I think SQ Server Agent is also using the same SP in order to execute the job scripts given for the schedule. Any idea? –  Oct 23 '20 at 09:58