sql server - Add date to SQL database backup filename

Question

I'm using the below to backup a db from a SQL job. Can someone tell me how to add the current date to the output filename? Preferably in YYYYMMDD format.

BACKUP DATABASE [myDB] TO? DISK = N'\myPathmyDB.bak' WITH NOFORMAT, INIT,? NAME = N'myDB', SKIP, REWIND, NOUNLOAD,? STATS = 10
GO

Thanks!

question from:https://stackoverflow.com/questions/2410674/add-date-to-sql-database-backup-filename

Answer 1

DECLARE @MyFileName varchar(1000)

SELECT @MyFileName = (SELECT '\ServerToSavePathMyDB_' + convert(varchar(500),GetDate(),112) + '.bak') 

BACKUP DATABASE [myDB] TO DISK=@MyFileName ...