Tuesday, June 3, 2014

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