0

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 @Current_Date DATETIME;
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;
GO

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 
TO DISK = 'WEEKLY_EXPORTS';
Improve this question
1

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

BACKUP DATABASE test_student_db
TO WEEKLY_EXPORTS;

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.

Improve this answer
3
  • 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 '20 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 '20 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 '20 at 17:08
0

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).

Improve this answer
1
  • 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 '20 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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