SQL Job for Backup Database MDF/Log File Detaching.. Copying and Attaching Files

SQL Job for Backup Database MDF/Log File Detaching.. Copying and Attaching Files

declare @cmdstring varchar(1000)

declare @cmdstring1 varchar(1000)

declare @BkpFromPath varchar(1000)

declare @BkpToPath varchar(1000)

declare @DbName varchar(100)

declare @i int

Set @DBName = 'CableBilling_DB' -- Database Name

Set @BkpFromPath = 'H:\LibraCables\Database\' -- DataBase Files location

Set @BkpToPath = 'H:\data11\' -- Destination Folder

Set @i = 15 -- back up Days

--

EXEC master.dbo.sp_detach_db @dbname = @DBName

set @cmdstring = 'copy '+@BkpFromPath+''+@DBName+'.mdf '+@BkpToPath+''+@DBName+'bkp'+

convert(varchar,datepart(year,getdate()))+convert(varchar,datepart(month,getdate()))+convert(varchar,datepart(day,getdate()))+'.mdf'

exec master..xp_cmdshell @cmdstring

set @cmdstring1 = 'copy '+@BkpFromPath+''+@DBName+'_log.ldf '+@BkpToPath+''+@DBName+'bkp'+convert(varchar,datepart(year,getdate()))+convert(varchar,datepart(month,getdate()))+convert(varchar,datepart(day,getdate()))+'_log.ldf'

exec master..xp_cmdshell @cmdstring1

declare @delmdfString varchar(1000)

declare @delmdfString1 varchar(1000)

WHILE @i < 50

BEGIN

    SET @i = @i + 1

  

    set @delmdfString = 'del H:\data\CableBilling_DBbkp'+convert(varchar,datepart(year,DATEADD(day,-@i,getdate())))

    +convert(varchar,datepart(month,DATEADD(day,-@i,getdate())))+convert(varchar,datepart(day,DATEADD(day,-@i,getdate())))+'.mdf'

      set @delmdfString1 = 'del H:\data\CableBilling_DBbkp'+convert(varchar,datepart(year,DATEADD(day,-@i,getdate())))

      +convert(varchar,datepart(month,DATEADD(day,-@i,getdate())))+convert(varchar,datepart(day,DATEADD(day,-@i,getdate())))+'_log.ldf'

      exec master..xp_cmdshell @delmdfString

      exec master..xp_cmdshell @delmdfString1

END

--declare @DbName varchar(100)

--declare @BkpFromPath varchar(100)

declare @PathWithFileNamemdf varchar(100)

declare @pathwithFileNameldf varchar(100)

--Set @DBName = 'CableBilling_DB'

--Set @BkpFromPath = 'H:\LibraCables\Database\'

Set @PathWithFileNamemdf = ''+@BkpFromPath+@DBName+'.mdf'

Set @pathwithFileNameldf = ''+@BkpFromPath+@DBName+'_log.ldf'

Set @DBName = @DbName

EXEC sp_attach_db @dbname = @DBName,

   @filename1 = @PathWithFileNamemdf,

   @filename2 = @pathwithFileNameldf