--------------------备份数据库文件恢复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      = 'D:\ERPX_DB\DZGOLD_DB_20110721.BAK'  --备份数据库文件路径及文件名称
set @DZGOLD_BAK_DATA_NAME = 'D:\ERPX_DB\DZGOLD.DAT'	     --恢复数据库数据文件路径及文件名称
set @DZGOLD_BAK_LOG_NAME  = 'D:\ERPX_DB\DZGOLDLOG.DAT'	     --恢复数据库日志文件路径及文件名称
-- 需要修改部分结束

RESTORE DATABASE DZGOLD
   FROM DISK= @DZGOLD_BAK_NAME
   WITH NORECOVERY, 
      MOVE 'DZ_GOLD' TO @DZGOLD_BAK_DATA_NAME, 
      MOVE 'DZ_GOLDLOG' TO @DZGOLD_BAK_LOG_NAME

RESTORE LOG DZGOLD

   FROM DISK= @DZGOLD_BAK_NAME
   WITH RECOVERY
----------------------------------------------------------------------------------------------------

--分离数据库
use [master]
go
EXEC sp_detach_db 'DZGOLD', 'true'
go

use [master]
go

--  데이터베이스파일이 있는 폴더주소를 지정하는데 환경에 맞게 수정해야됩니다.
--  根据数据库存放位子,修改路径.
sp_attach_db N'DZGOLD', N'D:\ERPX_db\DZGOLD.DAT',N'D:\ERPX_db\DZGOLDLOG.DAT'
go
use [DZGOLD]
go

--  사용자추가
sp_addlogin 'DZGOLD','DZGOLD','DZGOLD'
go

use [DZGOLD]
go
exec sp_dbcmptlevel N'DZGOLD'
go
use [DZGOLD]
go
exec sp_defaultlanguage N'DZGOLD'
go
use [DZGOLD]
go
exec sp_defaultdb N'DZGOLD', N'DZGOLD'
go
use [DZGOLD]
go

--  사용자변경
SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', 'DZGOLD', 'DZGOLD'
GO

-- CHANGE SA PASSWORD -- SA PASSWORD IS IMPUTY -- SA 암호가 없을때 암호설치 NEW_PASSWORD를 대응암호로 변경
-- exec sp_password NULL,'NEW_PASSWORD','Victoria'
-- GO

-- CHANGE SA PASSWORD -- SA PASSWORD IS existed -- SA 암호가 있을때 다른 암호로변경
-- EXEC SP_PASSWORD 'OLD_PASSWORD','NEW_PASSWORD'
-- GO

---------------------------------------------------------------------------------------