Dynamic backup file name

Question

I use SQL Server 2008 R2 and want to use Backup Devices for create backup of my database in each day. How can I create dynamic file name in `Backup Devices'.

In below code I Create a Backup Devices but want to have Current Date (YYYY-MM-DD) in my backup file.

EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'My Database Backup Location', @physicalname = N'D:\Backup\Backup YYYY-MM-DD.bak'

Thanks in advance

Answer 1

Try this

DECLARE @fn nvarchar(200)
SET @fn=N'D:\Backup\Backup'+CONVERT(VARCHAR(8), GETDATE(), 112) +'.bak'



EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', 
                @logicalname = N'My Database Backup Location',
                @physicalname = @fn