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

Create 7 days cycle backup devices

This is the stored procedure that creates 7 days cycle backup devices. The idea is to create a maximum of 7 backup files and recycle it every week. This backup concept is handy when you need a simple backup.

create proc mgt_create_backup_device (
 @db nvarchar(255),
 @folder nvarchar(100) 
)
as

begin

declare @device_name sysname,
 @file_name sysname

 select @device_name = @db + '_mon_bak', 
   @file_name = @folder + @db + '_mon_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_tue_bak', 
   @file_name = @folder + @db + '_tue_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_wed_bak', 
   @file_name = @folder + @db + '_wed_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_thu_bak', 
   @file_name = @folder + @db + '_thu_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_fri_bak', 
   @file_name = @folder + @db + '_fri_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_sat_bak', 
   @file_name = @folder + @db + '_sat_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_sun_bak', 
   @file_name = @folder + @db + '_sun_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

end
go