Windows MSSQL Server DB 복원과 사용자 설정하기(매핑하기)
Windows MSSQL Server DB 복원과 사용자 설정하기(매핑하기)
SQL 서버를 사용하보면 서버에 장애 혹은 다른 SQL서버로 DB를 이전 하는 경우가 있습니다
이번 포스팅에서는 SQL서버를 이용한 DB복원 후 계정연결 까지 같이 알아보겠습니다.
관련글
MSSQL 소개와 설치 : http://server-talk.tistory.com/129
MSSQL DB 백업 : http://server-talk.tistory.com/129
Windows MSSQL Server DB 복원하기 |
데이터베이스 복원하기 위해서 SQL Server Management Studio를 실행후 SQL서버로 접속합니다.
DB 복원을 하기위해서 데이터 베이스에서 우클릭을 하여 데이터베이스 복원을 선택합니다.
이제 데이터베이스 복원 창이 나오게 되며 여기서 백업된 DB 파일 경로를 지정하기위해 일반 탭에서의 장치에 체크 후 '....' 을 선택하여 들어갑니다
백업장치 선택 창이 나오며 추가를 우측 부분의 추가를 선택한 후 핵당 DB 백업 파일을 선택 후 확인을 선택합니다.
이제 데이터 베이스 복원 마지막 과정인데요 상단에 데이터베이스는 복원된 데이터베이스의 사용할 이름을 입력하신 후에 복원 사용할 백업 세트 선택 란에서 위에서 지정한 해당 데이터베이스 백업파일을 체크하신후 확인을 선택하시면 복원이 진행하게 됩니다 복원이 완료되면 복원이 완료 되었습니다라는 메시지가 나오면서 복원이 완료되게 됩니다.
위 내용을 보시면 데이터베이스가 복원된것을 확인 하실 수 있습니다 복원하신 후 꼭 데이터베이스의 내용이 정확이 복원이 된것을 확인하시기 바랍니다.
Windows MSSQL Server DB 복원 후 사용자계정 매핑하기 |
이제 DB(데이터베이스)복원이 완료되었으니 사용자계정을 연결하는 과정이 필요합니다
예를 들어 DB를 복원하였는데 SQL서버를 통한 계정을 로그인을 하지못한다면 데이터베이스를 쓸수 없게 되겟죠??그러한 과정에서 SQL서버로 로그인할 수있는 계정을 연결하여야 됩니다.
좀더 자세한 설명을 위해서 그림으로 설명 드리겠습니다
위 그림을 보시면 좌측은 backup_DB 복원한 DB의 사용자계정 이며, 우측은 SQL서버의 접속을 위한 계정입니다 그런데 좌측에 있는 로그인 계정은 backup_user라는 계정이 있으나 우측의 있는 로그인 계정은 backup_user라는 계정이 없습니다
하지만 SQL계정의 backup_user가 없으니 계정을 생성후 데이터베이스 계정 매핑과정 통해 복원한 DB를 외부접속을 하여 제어할 수가 있습니다.
MSSQL 사용자 계정 매핑 과정
1. 데이터베이스 계정과 동일한 SQL서버 접속계정 생성
2. 데이터베이스와 동일한 계정으로 생성한 계정을 SQL 서버와 매핑
SQL서버에서 보안 탭의 로그인 부분을 우클릭 후 새 로그인을 선택합니다
신규 계정생성 창이 나오며 일반탭으로 이동후 여기서 위에서 표시된 부분을 모두 작성하시면됩니다.
로그인 이름 : SQL로 접속할 계정(DB 계정과 동일한 계정을 하여 주시기 바랍니다.)
SQL Server 인증 : 이부분을 체크하여 주셔야 외부에서 접속이 가능합니다.
암호 만료 강제 적용 : 계정생성시에 이부분을 체크하게 되시면 일정기간이 지나면 암호가 변경되어 로그인이 안되게 됩니다.(꼭 해제 하시기 바랍니다.)
일반 탭에서의 설정이 완료된 후에 사용자 매핑 설정 탭으로 이동합니다
이 로그인으로 매핑된 사용자 지정의 복원한 DB를 체크후 하단의 계정권한을 db_owner와 public 권한을 주시기 바랍니다.
MSSQL 사용자 계정 권한
db_accessadmin : Windows 로그인, Windows 그룹 및 SQL Server 로그인에 대한 액세스를 추가, 제거할 수 있음
db_backupoperator : 데이터베이스를 백업할 수 있음
db_datareader : 사용자 테이블의 모든 데이터를 읽을 수 있음
db_datawriter : 사용자 테이블에서 데이터를 추가, 삭제, 변경할 수 있음
db_ddladmin : 데이터베이스에서 모든 DDL(데이터 정의어) 명령을 실행할 수 있음
db_denydatareader : 데이터베이스 내에 있는 사용자 테이블의 데이터를 읽을 수 없음
db_denydatawriter : 데이터베이스 내의 사용자 테이블의 데이터를 추가, 수정, 삭제할 수 없음
db_owner : 데이터베이스에서 모든 구성 및 유지 관리 작업을 수행할 수 있음
db_secutiryadmin : 멤버 자격을 수정하고 사용권한을 관리할 수 있음
public : 모든 데이터베이스 사용자에게 디폴트로 부여되는 최소 권한을 갖는 역할
마지막으로 상태 탭으로 이동후 데이터베이스 엔진 연결 권한과 로그인을 사용을 체크 후 확인을 선택하여 완료합니다.
Windows MSSQL Server 복원된 DB의 사용자 계정 쿼리로 매핑하기 |
MSSQL 재설치, MDF파일 연결, DB복원 후에는 사용자 연결이 끊어져 있습니다 각 사용자별 권한이 설정되어 있다면 사용자를 삭제하고 재 설정하는것은 번거로운 과정입니다.
하지만 sp_change_users_login 프로시저를 사용하면 사용자를 삭제하지 않고 각 DB 사용자 이름과 SQL 로그인 이름을 쉽게 연결할 수 있습니다
한번씩 위와 같은 에러가 발생하며 계정이 생성되는 경우가 있습니다 이러한 경우 쿼리로 계정을 매핑하는 과정을 하여야 합니다.
우선 초기 SQL Server Management로 들어가 상단의 새쿼리를 클릭합니다.
처음 쿼리를 새쿼리를 선택하게 되면 기본적으로 설정된 DB로 쿼리연결이 되게 됩니다 변경할 DB의 내용은 backup_DB의 내용을 변경이 필요하니 backup_DB로 쿼리연결해야되는데, 변경방법은 두가지가 있습니다.
두가지 방식을 한번에 설명하겠습니다. 좌측 방식은 MSSQL에 있는 DB 목록을 확인한후 선택하는 방식이며 우측은 MSSQL의 use 명령을 사용하여 DB를 선하는 방식 있으며, 두가지중 편한 방식을 사용하시면 됩니다.
위 그림처럼 명령어로 사용하여 적용하시면 됩니다.
go
EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE','로그인할 사용자 계정','로그인할 사용자 계정'
Windows에서 SSMS를 통한 외부접속 테스트 하기 |
이제 모든 설정이 완료 되었으니 마지막으로 접속테스트를 하여야 됩니다
안하시는 분들도 간혹있지만 접속테스트를 안하시고 지나치게 된다면 나중에 DB접속이 안될때에 장애원인을 빠르게 대처하기가 어려움이 발생하실수 있기때문에 꼭 접속테스트를 거치시기 바랍니다.
위 그림의 빨간 박스에 표시된 부분을 모두 입력후 로그인 하시기 바랍니다.
서버이름 : DB복원 SQL 서버의 IP
인증 : SQL Server 인증
로그인 : 위에서 매핑한 사용자 계정
암호 : 위에서 매핑한 사용자 계정 패스워드
접속하게 되면 복원 backup_DB를 확인하실 수 있습니다.
