Script to backup table @ database level

This script is used to take a backup of the table @ the database level. This is used in the case where the number of records in the table keeps on growing and the historical records are only used for reporting rather than transactional purpose. This script when executed creates a backup of the table with name TABLENAME_BKP_DATE. If this script is executed more than once on the same day, the already backed up table is renamed as TABLENAME_BKP_DATETIME and new backup table is created.

Script:
******
create procedure TABLEARCHIVE
as
BEGIN
SET NOCOUNT ON
/*******************************************************************************************************/
-- This Procedure was written by Sugesh Kumar Rajendran - Date: 31 July 2006
-- This Procedure is used to back up the table
-- Which will have the name TABLENAME_BKP_getdate() when this procedure is run
-- If this procedure is run more than once in the same day the already existing table will be backed up
-- With TIMESTAMP in it
-- No Input Parameters and No Output Parameters
/*******************************************************************************************************/
declare @sql nvarchar(200)
declare @sql1 nvarchar(200)
declare @ddate int
declare @dmonth varchar(3)
declare @dyear int
declare @dhour int
declare @dmin int
declare @bkptable varchar(100)
declare @bkptable1 varchar(100)
declare @date datetime
declare @bsql nvarchar(200)
declare @count int
declare @count1 int
/* GETTING THE DATE MONTH AND YEAR INTO SEPERATE VARIABLES*/
set @ddate = (select DATEPART(dd,getdate()))
set @dmonth = (select DATENAME(month,getdate()))
set @dyear = (select DATEPART(yyyy,getdate()))
set @dhour = (select DATEPART(hh,getdate()))
set @dmin = (select DATEPART(mi,getdate()))
/* DEFINING THE NAME OF THE BACKUP TABLE*/
select @bkptable = 'TABLENAME_BKP_'+cast(@dyear as varchar)+UPPER(@dmonth)+cast(@ddate as varchar)
/* CHECKING IF THE BACKUP TABLE ALREADY EXISTS IN THE SYSTEM CATALOG*/
/* IF SO THIS TABLE IS BACKED UP TO AN ALTERNATE TABLE TO MAINTAIN HISTORY*/
/* AND THE BACKUP TABLE IS DROPPED SO THAT IT CAN BE CREATED AGAIN WITH SAME NAME*/
set @bsql = 'DROP TABLE '+@bkptable
set @count1 = (select count(*) from sysobjects where name like @bkptable)
if @count1 = 1
BEGIN
select @bkptable1 = 'TABLENAME_BKP_'+cast(@dyear as varchar)+UPPER(@dmonth)+cast(@ddate as varchar)+cast(@dhour as
varchar)+cast(@dmin as varchar)
set @sql1 = 'select * into '+@bkptable1+' from '+@bkptable
exec sp_executesql @sql1
if @@error = 0 PRINT 'RECORDS MOVED TO TABLE '+@bkptable1
exec sp_executesql @bsql
END
/* CREATING THE BACKUP TABLE */
set @sql = 'select * into '+@bkptable+' from TABLENAME
exec sp_executesql @sql
if @@error = 0
PRINT 'TABLE ARCHIVED SUCCESSFULLY'
else PRINT 'TABLE ARCHIVE NOT SUCCESSFUL'
/*DELETING THE RECORDS FROM TABLE*/
delete from TABLENAME
select @count = @@rowcount
if @@error = 0
PRINT cast(@count as varchar) +' ROWS DELETED FROM TABLENAME'
else PRINT 'ERROR PROCESSING DELETION FROM TABLENAME'END