데이터베이스 개발자 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
» 재해복구를 위한 SQL Server 역할 가져오기 jevida(강성욱) 2017.01.11 2279
2009 비관리자 계정에 Profiler 실행 권한 부여하기 jevida(강성욱) 2017.01.11 3109
2008 SQL Server Agent 공유 일정 생성하기 jevida(강성욱) 2017.01.11 2146
2007 인덱스 리빌드는 통계를 업데이트 할까? jevida(강성욱) 2017.01.11 2317
2006 인덱스 유지관리 작업과 SQL Server 쿼리 성능 jevida(강성욱) 2017.01.11 3263
2005 네트워크 드라이브에 데이터베이스 복원하기 jevida(강성욱) 2017.01.11 4041
2004 확장 저장 프로시저를 활용한 논리디스크 용량 확인 jevida(강성욱) 2017.01.11 2445
2003 날짜 참조 테이블 만들기 jevida(강성욱) 2017.01.11 3023
2002 인덱스 상세 정보 확인 jevida(강성욱) 2017.01.11 3707
2001 DTC Transacntion 오버헤드 jevida(강성욱) 2017.01.11 1289
2000 대용량 로드를 위한 BULK INSERT 옵션 jevida(강성욱) 2017.01.11 5185
1999 SQL Server 2014 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1608
1998 SQL Server 2012 Contained Database jevida(강성욱) 2017.01.11 1000
1997 SQL Server 2008R2 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1420
1996 SQL Server 2005 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1355
1995 601 Error, Could not continue scan with NOLOCK due to SQL Server data Movement jevida(강성욱) 2017.01.11 3627
1994 데이터베이스의 모든 인덱스 생성 삭제 스크립트 만들기 jevida(강성욱) 2017.01.11 1659
1993 SQL Server Spinlock 소개 jevida(강성욱) 2017.01.11 1584
1992 Ad-hoc 쿼리와 실행계획 jevida(강성욱) 2017.01.11 2569
1991 로그인 계정이 접근할 수 있는 데이터베이스 확인 jevida(강성욱) 2017.01.11 2986





XE Login