로그 전달에 새로운 데이터베이스 파일 추가하기

 

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

 

SQL Server 로그 전달 구성에서 주 데이터베이스의 파일 구성이 변경되었을 때 (데이터 파일의 추가 또는 삭제) 전체 복원을 하지 않고 로그 전달을 유지하는 방법에 대해서 알아 본다.

 

로그 전달을 사용하기 위해서는 데이터베이스의 복구 모드가 전체모드이어야 하며 로그 백업이전에 반드시 전체 백업이 이루어 져야 한다.

 

다음 스크립트는 데이터베이스의 복구 모델을 [전체]로 변경하고 전체 백업하는 스크립트이다. 주 데이터베이스의 복구 모델 변경과 전체 백업을 실행 한다.

USE [master] GO   ALTER DATABASE [SQLMVP] SET RECOVERY FULL; GO   BACKUP DATABASE [SQLMVP] TO DISK = 'C:\SQL_Backup\SQLMVP.BAK' WITH CHECKSUM, FORMAT, INIT, STATS = 10; GO

 

 

주 데이터베이스의 백업을 사용하여 로그전달에 사용할 보조 데이터베이스에 복원한다.

USE [master] GO   RESTORE DATABASE [SQLMVPCopy] FROM DISK = 'C:\SQL_Backup\SQLMVP.BAK' WITH CHECKSUM, NORECOVERY, REPLACE, STATS = 10, MOVE 'SQLMVP' TO 'C:\SQL_Data\SQLMVPCopy.mdf', MOVE 'SQLMVP_log' TO 'C:\SQL_Data\SQLMVPCopy_log.ldf'

 

 

 

주 데이터베이스에서 로그 백업을 생성한다.

USE [master] GO   BACKUP LOG [SQLMVP] TO DISK = 'C:\SQL_Backup\SQLMVP_1.trn' WITH CHECKSUM, FORMAT, INIT, STATS = 10; GO

 

주 데이터베이스에 새 데이터 파일을 추가 한다. 다음 스크립트를 사용하여 데이터 파일을 추가 할 수 있다.

USE [master] GO   ALTER DATABASE [SQLMVP] ADD FILE ( NAME = N'SQLMVP_Data2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SQLMVP_Data2.ndf', SIZE = 2048KB, FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY];

 

SSMS에서 데이터베이스 속성에서 추가 가능하다.

 

주 데이터베이스에서 두 번째 로그 백업을 진행 한다.

USE [master] GO   BACKUP LOG [SQLMVP] TO DISK = 'C:\SQL_Backup\SQLMVP_2.trn' WITH CHECKSUM, FORMAT, INIT, STATS = 10;

 

 

로그전달의 보조 데이터베이스에서 주 데이터베이스의 첫 번째 로그 백업을 복원한다.

USE [master] GO   RESTORE LOG [SQLMVPCopy] FROM DISK = 'C:\SQL_Backup\SQLMVP_1.trn' WITH CHECKSUM, NORECOVERY, STATS = 10;

 

 

로그 전달의 보조 데이터베이스에서 주 데이터베이스의 두 번째 로그 백업을 복원한다.

USE [master] GO   RESTORE LOG [SQLMVPCopy] FROM DISK = 'C:\SQL_Backup\SQLMVP_2.trn' WITH CHECKSUM, NORECOVERY, STATS = 10;

 

다음의 오류메시지와 함께 복원이 실패 한다.

메시지 1834, 수준 16, 상태 1, 줄 2 파일 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SQLMVP_Data2.ndf'을(를) 덮어쓸 수 없습니다. 데이터베이스 'SQLMVP'에서 사용하고 있습니다. 메시지 3156, 수준 16, 상태 4, 줄 2 파일 'SQLMVP_Data2'을(를) 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SQLMVP_Data2.ndf'(으)로 복원할 수 없습니다. WITH MOVE를 사용하여 올바른 파일 위치를 확인하십시오. 메시지 3119, 수준 16, 상태 1, 줄 2 RESTORE 문을 계획하는 동안 문제가 발견되었습니다. 자세한 내용은 이전 메시지를 참조하십시오. 메시지 3013, 수준 16, 상태 1, 줄 2 RESTORE LOG이(가) 비정상적으로 종료됩니다.

 

 

WITH MOVE 구문을 이용하여 추가된 NDF 데이터 파일을 생성하고 로그 복원을 진행한다. 아래 그림과 같이 복원이 완료 되면 NDF 파일이 생성된 것을 확인 할 수 있다.

USE [master] GO   RESTORE LOG [SQLMVPCopy] FROM DISK = 'C:\SQL_Backup\SQLMVP_2.trn' WITH CHECKSUM, NORECOVERY, STATS = 10, MOVE 'SQLMVP_Data2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SQLMVPCopy_Data2.ndf'

 

 

 

다음 스크립트를 사용하여 데이터베이스 파일 정보를 확인해 보면 추가된 보조 데이터 파일을 확인 할 수 있다.

SELECT DB_NAME([database_id]) [database_name], [file_id], [type_desc] [file_type], [name] [logical_name], [physical_name] FROM sys.[master_files] WHERE [database_id] IN (DB_ID('SQLMVP'), DB_ID('SQLMVPCopy')) ORDER BY DB_NAME([database_id]), [type] ;

 

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3122/fix-sql-server-log-shipping-after-a-new-database-file-has-been-added/

 

2014-02-10 / 강성욱 / http://sqlmvp.kr