/*
Description: This procedure take a first full backup
and
after that differentail or partial backup each day.Path
location is fixed in @filePath.
*/
alter proc [dbo].UP_Database_bkup
@dbName varchar(100),
@FilePath varchar(250)
@FilePath varchar(250)
as
set nocount on
Declare @bkPath varchar(100), @FileName varchar(50)
Declare @name varchar(40) , @fileExist int
-- set @FilePath ='E:\DatabaseBKup\DailyBKUP\'
begin try
SELECT @bkPath = isnull(BkupPath,'') FROM ATSR_MASTER.dbo.T_GM_DatabaseDetail
where DbName =
@dbName
/* Check File is exist or not is exist return 1 else 0 */
Exec xp_fileexist @bkPath, @fileExist output
if @fileExist = 1
begin
set @name = 'Differential BKUP - ' +
CONVERT(varchar(40), CONVERT(datetime, GETDATE()),106)
/* Below line take the partial or differential backup */
BACKUP DATABASE
@dbName
TO DISK = @bkPath
WITH DIFFERENTIAL
, NOFORMAT, NOINIT
, NAME = @name
, SKIP, NOREWIND, NOUNLOAD, STATS = 10
end
else
begin
set @FileName =
@FilePath + @DbName +
'.bak'
set @name ='Full Backup' + CONVERT(varchar(40), CONVERT(datetime, GETDATE()),106)
/* Below line take full backup */
-- BACKUP DATABASE @DbName To
disk = @FileName
BACKUP DATABASE
@DbName To
disk =
@FileName
WITH NOFORMAT, NOINIT
, NAME = @name
, SKIP, NOREWIND, NOUNLOAD, STATS = 10
end
end try
begin catch
end catch
