Ratakan

Informations :

SP Restore Multiple Table (Recover from Backup)

/*
 query recover dari backup ( Restore Table ) 
*/

DECLARE @DT VARCHAR(10)
SET @DT = 'back' --Ganti dengan kode table sementara, samakan dengan kode waktu backup

DECLARE @NAME VARCHAR(30), @EXIST BIT, @BC BIT, @RECORD BIGINT, @QUERY VARCHAR(MAX), @COLUMNS VARCHAR(MAX), @ERR_MSG VARCHAR(MAX), @COUNT BIGINT
CREATE TABLE #TEMP(
 NAME VARCHAR(30),
 RECORD BIGINT,
 RECORD_TEMP BIGINT,
 ERR BIT,
 ERR_MSG VARCHAR(MAX)
) 
-- Masukkan table yang akan di-recover

INSERT INTO #TEMP(NAME) VALUES('AISM0001')
INSERT INTO #TEMP(NAME) VALUES('AISM0002')
INSERT INTO #TEMP(NAME) VALUES('AISM0003')
INSERT INTO #TEMP(NAME) VALUES('AISM0004')



-- End masukkan table yang akan di-recover


DECLARE RS CURSOR FOR
 SELECT NAME FROM #TEMP
OPEN RS
FETCH NEXT FROM RS INTO @NAME

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @COUNT = 0
 SET @QUERY = ''
 
 IF (SELECT COUNT(*) FROM sysobjects WHERE NAME=@NAME AND TYPE='U') > 0
 BEGIN
  IF (SELECT COUNT(*) FROM sysobjects WHERE NAME=(@NAME + '_' + @DT) AND TYPE='U') > 0
  BEGIN
   SET @QUERY = 'UPDATE #TEMP SET RECORD_TEMP=(SELECT COUNT(*) FROM ' + @NAME + '_' + @DT + ') WHERE NAME=' + CHAR(39) + @NAME + CHAR(39)
   EXEC (@QUERY)
   
   SET @QUERY = ''
   SET @COLUMNS = (SELECT STUFF((SELECT ',' + RTRIM(NAME) FROM syscolumns WHERE ID=(
        SELECT ID FROM sysobjects WHERE NAME=(@NAME + '_' + @DT) AND TYPE='U'
       ) AND NAME<>'DEX_ROW_ID'
       FOR XML PATH('')),1,1,''))
   
   SET @QUERY = 'INSERT INTO ' + @NAME + '(' + @COLUMNS + ')' + CHAR(13)
   SET @QUERY = @QUERY + 'SELECT ' + @COLUMNS + ' FROM ' + @NAME + '_' + @DT
   EXEC (@QUERY)   
   
   SET @QUERY = 'UPDATE #TEMP SET RECORD=(SELECT COUNT(*) FROM ' + @NAME + ') WHERE NAME=' + CHAR(39) + @NAME + CHAR(39)
   EXEC (@QUERY)
   
   IF (SELECT RECORD FROM #TEMP WHERE NAME=@NAME) = (SELECT RECORD_TEMP FROM #TEMP WHERE NAME=@NAME)
   BEGIN
    UPDATE #TEMP SET ERR=0, ERR_MSG='Rebackup succesful.' WHERE NAME=@NAME
   END
   ELSE
   BEGIN
    UPDATE #TEMP SET ERR=1, ERR_MSG='Rebackup failed.' WHERE NAME=@NAME
   END
  END
  ELSE
  BEGIN
   UPDATE #TEMP SET ERR=1, ERR_MSG='The temporary table does not exist.' WHERE NAME=@NAME
  END
 END
 ELSE
 BEGIN
  UPDATE #TEMP SET ERR=1, ERR_MSG='This table does not exist.' WHERE NAME=@NAME
 END
 
 FETCH NEXT FROM RS INTO @NAME
END
GO

CLOSE RS
GO

DEALLOCATE RS
GO

SELECT * FROM #TEMP
DROP TABLE #TEMP


No comments