----- 1 --------------

/********************************************************/
-- Charge  : KIM JU YOUNG
-- tabled  : DZ_SCTRL_D SCTRL_D
-- Update  : 2008.06.18
-- Command : 기초코드테이블 사이즈 VARCHAR(30)-->(60)
/********************************************************/
IF EXISTS( SELECT 1 FROM SYSOBJECTS O, SYSCOLUMNS C WHERE O.ID = C.ID AND O.NAME = 'DZ_SCTRL_D' AND C.NAME ='CTD_KOR_NM' )
BEGIN
ALTER TABLE DZ_SCTRL_D
ALTER COLUMN CTD_KOR_NM VARCHAR(60) Korean_Wansung_CI_AS NULL
END
GO
IF EXISTS( SELECT 1 FROM SYSOBJECTS O, SYSCOLUMNS C WHERE O.ID = C.ID AND O.NAME = 'DZ_SCTRL_D' AND C.NAME ='CTD_ENG_NM' )
BEGIN
ALTER TABLE DZ_SCTRL_D
ALTER COLUMN CTD_ENG_NM VARCHAR(60) NULL
END
GO
IF EXISTS( SELECT 1 FROM SYSOBJECTS O, SYSCOLUMNS C WHERE O.ID = C.ID AND O.NAME = 'DZ_SCTRL_D' AND C.NAME ='CTD_CHS_NM' )
BEGIN
ALTER TABLE DZ_SCTRL_D
ALTER COLUMN CTD_CHS_NM VARCHAR(60) COLLATE Chinese_PRC_CI_AS NULL
END
GO
IF EXISTS( SELECT 1 FROM SYSOBJECTS O, SYSCOLUMNS C WHERE O.ID = C.ID AND O.NAME = 'DZ_SCTRL_D' AND C.NAME ='CTD_JPN_NM' )
BEGIN
ALTER TABLE DZ_SCTRL_D
ALTER COLUMN CTD_JPN_NM VARCHAR(60) NULL
END
GO

IF EXISTS( SELECT 1 FROM SYSOBJECTS O, SYSCOLUMNS C WHERE O.ID = C.ID AND O.NAME = 'SCTRL_D' AND C.NAME ='CTD_NM' )
BEGIN
ALTER TABLE SCTRL_D
ALTER COLUMN CTD_NM VARCHAR(60) COLLATE Chinese_PRC_CI_AS NULL
END
GO
IF EXISTS( SELECT 1 FROM SYSOBJECTS O, SYSCOLUMNS C WHERE O.ID = C.ID AND O.NAME = 'SCTRL_D' AND C.NAME ='CTD_NMK' )
BEGIN
ALTER TABLE SCTRL_D
ALTER COLUMN CTD_NMK VARCHAR(60) COLLATE Korean_Wansung_CI_AS NULL
END
GO
/********************************************************/
declare @module_cd as varchar(3),
	@ver_info as varchar(8)
set @module_cd = 'XST'
set @ver_info = '20080610'
delete from smadiary where module_cd = @module_cd and ver_info = @ver_info
insert into smadiary (module_cd, upgrade_dt, ver_info, solution_cd) values (@module_cd, getdate(), @ver_info, 'X')
GO



----- 2 --------------

--------------------备份数据库文件恢复1---------------------------------------------------------------
declare @dZGOlD_BAK_NAME as varchar(50),
	@dZGOlD_BAK_DATA_NAME as varchar(50),
	@dZGOlD_BAK_LOG_NAME as varchar(50)

-- 需要修改部分
set @dZGOlD_BAK_NAME      = 'F:\ERPX_DB\temp\2006042615_dZGOlD.DAT'  --备份数据库文件路径及文件名称
set @dZGOlD_BAK_DATA_NAME = 'F:\ERPX_DB\temp\dZGOlD.DAT'	     --恢复数据库数据文件路径及文件名称
set @dZGOlD_BAK_LOG_NAME  = 'F:\ERPX_DB\temp\dZGOlDLOG.DAT'	     --恢复数据库日志文件路径及文件名称
-- 需要修改部分结束

RESTORE DATABASE dZGOlD_bak 
   FROM DISK= @dZGOlD_BAK_NAME
   WITH NORECOVERY, 
--      MOVE 'DZ_GOLD' TO 'F:\ERPX_DB\temp\dZGOlD.DAT', 
--      MOVE 'DZ_GOLDLOG' TO 'F:\ERPX_DB\temp\dZGOlDLOG.DAT'
      MOVE 'DZ_GOLD' TO @dZGOlD_BAK_DATA_NAME, 
      MOVE 'DZ_GOLDLOG' TO @dZGOlD_BAK_LOG_NAME

RESTORE LOG dZGOlD_bak
--   FROM DISK= 'F:\ERPX_DB\temp\2006042615_dZGOlD.DAT'
   FROM DISK= @dZGOlD_BAK_NAME
   WITH RECOVERY

go


----- 3 --------------


UPDATE DZGOLD.DZGOLD.SCTRL_D 
SET CTD_NM = BAK.CTD_NM, CTD_NMK = BAK.CTD_NMK 
FROM DZGOLD_bak.DZGOLD.SCTRL_D BAK, DZGOLD.DZGOLD.SCTRL_D D
WHERE  D.CTRL_CD = BAK.CTRL_CD and D.MODULE_CD = BAK.MODULE_CD and D.CTD_CD = BAK.CTD_CD
go

----- 4 --------------

use [master]
go
EXEC sp_detach_db 'DZGOLD_bak', 'true'
go