Backing up the database using dump device

Once you have created the dump device, the next nice thing to have is to create a stored procedure that is able to backing up the database to the dump device based on current week day. You have to execute the following stored procedure in SQL Server Management Studio. Then, create a job in SQL Agent and setup the schedule properly.

create proc mgt_backup_db (
 @db_name sysname
)
as
begin

declare @d int

select @d = datepart( weekday, getdate())

if (@d = 2)
 backup database @db_name
 to db_mon_bak
 with init

else if (@d = 3)
 backup database @db_name
 to db_tue_bak
 with init

else if (@d = 4)
 backup database @db_name
 to db_wed_bak
 with init

else if (@d = 5)
 backup database @db_name
 to db_thu_bak
 with init

else if (@d = 6)
 backup database @db_name
 to db_fri_bak
 with init

else if (@d = 7)
 backup database @db_name
 to db_sat_bak
 with init

end

go