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

Igor Borisenko · Accepted Answer · 2013-08-28T00:08:56.037

6

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

edited Aug 28 '13 at 00:08

answered Oct 31 '12 at 08:28

Igor Borisenko

3,806
3
34
49

Can I Alter Created `Backup Device` for set new physical location. – mehdi lotfi Oct 31 '12 at 08:30
You can drop a backup device with `sp_dropdevice` and create it one more time. – Igor Borisenko Oct 31 '12 at 08:37
If I have deferent `Backup Device`, media contents will be lost. Can I have all backups in several day from my database that have deferent name formated by date in one `Backup Device Media Contents`. – mehdi lotfi Oct 31 '12 at 08:44
What if they have the same logicalname? I never tried it but you can :) – Igor Borisenko Oct 31 '12 at 08:52
thanks for this! fyi, you can do that convert as a single command CONVERT(VARCHAR(8), GETDATE(), 112) – egbutter Aug 27 '13 at 16:59
@egbutter Thank you for mention that. ) – Igor Borisenko Aug 28 '13 at 00:21

score 0 · Answer 2 · edited May 23 '17 at 12:25

0

You can use an intermediate variable, store the file-name appended with the DD-MM-YYYY. and use this intermediate variable in your EXEC statement.

Refer below link for how: Using the result of an expression (e.g. Function call) in a stored procedure parameter list?

edited May 23 '17 at 12:25

Community

1
1

answered Oct 31 '12 at 08:25

tempidope

823
1
12
29

Dynamic backup file name

2 Answers2