CREATE Proc [dbo].[sp_BackupDatabase]
@dbName varchar(15),
@BkUpPath varchar(100)
WITH RECOMPILE
AS
Declare @@database_name_var varchar(15)
Declare @@database_BackupDevice_var varchar(20)
Declare @@database_Backup_path varchar(100)
Declare @@BkFileName varchar(100)
Declare @@UserName varchar(100)
Declare @@CtDt varchar(20)
set @@BkFileName=''
Set @@database_name_var =@dbName
--Set @@database_BackupDevice_var =@@database_name_var + 'Dump'
BEGIN
SELECT @@UserName=Convert(Varchar(15), CURRENT_USER)
Select @@CtDt = Cast(datepart(yyyy,getdate()) as varchar(4)) +
Cast(datepart(mm,getdate()) as varchar(2)) +
Cast(datepart(dd,getdate()) as varchar(2)) + '_' +
Cast(datepart(hh,getdate()) as varchar(2)) +
Cast(datepart(mi,getdate()) as varchar(2))
Set @@database_BackupDevice_var =@@database_name_var + @@CtDt + 'Dump'
Set @@BkFileName=@BkUpPath + @dbName + @@CtDt + '.bak'
Set @@database_Backup_path = @@BkFileName
--1# Backup log file with truncate only
BACKUP LOG @@database_name_var WITH TRUNCATE_ONLY
--2# Creating Backup Device
EXEC master.dbo.sp_addumpdevice 'disk', @@database_BackupDevice_var ,@@database_Backup_path
--3# Backup Database file
BACKUP DATABASE @@database_name_var TO @@database_BackupDevice_var
-- (WITH MEDIAPASSWORD='gkv2003' )
END
