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.
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';