데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

재해복구를 위한 SQL Server 역할 가져오기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

SQL Server를 다른 서버로 이전하거나 DR에 의해서 다른 사이트로 이동 될 때 반드시 챙겨야 하는 부분이 로그인 계정과 서버 역할이다. 서버 역할을 복구할 수 있는 스크립트를 만들어서 언제든지 서버 역할을 복원 할 수 있는 방법에 대해서 알아 본다.

 

서버 역할에 대한 정보를 확인 하기 위해서는 다음 두 개의 보안카탈로그 뷰를 사용할 수 있다.

  • sys.server_principals : 로그인의 이름, 시스템 로그인 및 고정 서버 역할을 확인
  • sys.server_role_members : 서버 역할 멤버 자격 및 고정 서버 역할 멤버의 principal_id 확인

 

다음 스크립트는 서버 역할을 확인하고 해당 서버 역할이 존재하지 않는 경우 서버 역할을 생성한다. Principal_id로 필터링 하는 이유는 시스템 관리자 및 securityadmin 같은 고정 서버 역할은 생성 할 필요가 없기 때문이다. 임시로 test_role 역할을 생성하고 스크립트를 생성해보면 사용자가 추가한 서버 역할을 생성하는 스크립트가 생성됨을 확인 할 수 있다.

SET NOCOUNT ON;

 

SELECT 'IF NOT EXISTS(SELECT name

FROM sys.server_principals

WHERE type = ''R'' AND name=''' + [name] + ''')

CREATE SERVER ROLE [' + [name] + '];'

FROM sys.server_principals

WHERE type = 'R'

AND principal_id > 10;

 

 

 

다음 스크립트는 역할 구성원을 복원한다. 역할에 추가하려는 계정이 존재하지 않을 경우 오류가 발생 한다. 생성된 스크립트를 실행하면 역할에 추가 된다.

SET NOCOUNT ON;

 

SELECT 'EXEC sp_addsrvrolemember @loginame = ''' + p.[name] +

''', @rolename = ''' + r.[name] + ''';'

FROM sys.server_principals AS p

JOIN sys.server_role_members AS srm

ON p.principal_id = srm.member_principal_id

JOIN sys.server_principals AS r

ON srm.role_principal_id = r.principal_id

WHERE p.[name] <> 'sa';

 

 

 

[참고자료]

  • Retrieving SQL Server Server Roles for Disaster Recovery :

http://www.mssqltips.com/sqlservertip/2288/retrieving-sql-server-server-roles-for-disaster-recovery/

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
2012 RESOURCE_GOVERNOR_IDLE과 쿼리 성능 jevida(강성욱) 2017.01.11 2025
2011 TDE 암호화된 데이터베이스 복원 jevida(강성욱) 2017.01.11 2476
» 재해복구를 위한 SQL Server 역할 가져오기 jevida(강성욱) 2017.01.11 2287
2009 비관리자 계정에 Profiler 실행 권한 부여하기 jevida(강성욱) 2017.01.11 3132
2008 SQL Server Agent 공유 일정 생성하기 jevida(강성욱) 2017.01.11 2148
2007 인덱스 리빌드는 통계를 업데이트 할까? jevida(강성욱) 2017.01.11 2363
2006 인덱스 유지관리 작업과 SQL Server 쿼리 성능 jevida(강성욱) 2017.01.11 3330
2005 네트워크 드라이브에 데이터베이스 복원하기 jevida(강성욱) 2017.01.11 4096
2004 확장 저장 프로시저를 활용한 논리디스크 용량 확인 jevida(강성욱) 2017.01.11 2465
2003 날짜 참조 테이블 만들기 jevida(강성욱) 2017.01.11 3042
2002 인덱스 상세 정보 확인 jevida(강성욱) 2017.01.11 3763
2001 DTC Transacntion 오버헤드 jevida(강성욱) 2017.01.11 1290
2000 대용량 로드를 위한 BULK INSERT 옵션 jevida(강성욱) 2017.01.11 5317
1999 SQL Server 2014 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1613
1998 SQL Server 2012 Contained Database jevida(강성욱) 2017.01.11 1004
1997 SQL Server 2008R2 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1422
1996 SQL Server 2005 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1362
1995 601 Error, Could not continue scan with NOLOCK due to SQL Server data Movement jevida(강성욱) 2017.01.11 3745
1994 데이터베이스의 모든 인덱스 생성 삭제 스크립트 만들기 jevida(강성욱) 2017.01.11 1664
1993 SQL Server Spinlock 소개 jevida(강성욱) 2017.01.11 1587





XE Login