I am new to SQL Server coming from a software development background. I created a backup device which creates a backup with the date and time appended to the name to make it unique:

DECLARE @pname varchar(300);
SET @Current_Date = SYSDATETIME();
set @pname = (select 'C:\WEEKLY_EXPORTS\LEARNING_DB_Backup_' + replace(CONVERT(varchar,@Current_Date, 20), ':', '_') + '.bak' );
EXEC sp_addumpdevice @devtype = 'disk',
@logicalname = 'WEEKLY_EXPORTS',
@physicalname = @pname;

When backing up the database using SSMS, I can use the backup device without having to specify a backup name everytime.

select backup destination

I was looking for an example on how to create a backup using the logical backup device in T-SQL but found none. Is there a way to create the backup in a simple way in T-SQL like the following:

BACKUP DATABASE test_student_db 

2 Answers 2


To use a backup device the proper T-SQL would be:

BACKUP DATABASE test_student_db

Check the Syntax section of the Backup doc to better understand the backup options.

It seems you're trying to develop a backup solution from scratch, so I recommend you take a look at Ola Hallengren's solution as it might save you a lot of work.

  • i had tried that earlier but my mistake was to put it as 'WEEKLY_EXPORTS' in quotes. I appreciate the quick response
    – user44649
    Nov 19, 2020 at 14:57
  • @user44649, glad I could help. I've updated the answer to add a free backup solution that might be helpful.
    – Ronaldo
    Nov 19, 2020 at 14:59
  • Thanks for the link. I have started checking it out. It looks like I will also have use for the other tools like the Integrity Check and Index Optimization.
    – user44649
    Nov 19, 2020 at 17:08

FWIW, I've written a backup solution (many years ago) where I created "logical backup devices" (I hate the lack of precise and good terminology). It was a mess! I wouldn't go in this direction.

One thing is when you want to restore. What does this point to? The most recent file? I.e., you modify it for every new backup. As I remember, we created new backup devices for each backup, with date&time in the logical backup device name. I also recall issues with backup history in msdb, and having a job modifying the backuphistory in order to get it right.

I'm vague, since this was a long time ago, but I would take a step back and carefully consider if this is the right direction. IMO, use the filename directly in the backup command, and depending on your circumstances, use something which already done the heavy lifting (Ola's procedures).

  • I see your point on how it can be messy, especially for restores. My initial thought was that restores would be done only in emergency, however, if I need to keep in line with a RTO then restores should be done quickly with minimum effort in sorting which backup files are required. I have been checking out Ola's tools since Ronaldo's comment and they seem to have a simple way to do the backups with additional tools for checking the integrity of the database.
    – user44649
    Nov 19, 2020 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.