마이크로소프트의 MS SQL 서버를 운영하면서 데이터베이스를 백업하고 복원하는 작업은 무척 중요한 작업 중에 하나 입니다.
SQL Server 관리 차원의 Daily Backup과는 다른 의미의 SQL 서버의 데이터베이스를 백업하고 복원하는 방법에 대해 설명해 보려 합니다.
SQL 서버를 다른 윈도우 서버로 이전을 하거나 SQL 서버의 버전을 업그레이드 할 때 데이터베이스 마이그레이션 하는 방법에 대한 설명이라고 이해하시면 됩니다.
일반적으로 데이터베이스를 백업하고 복원할 때 가장 많이 사용하는 방법은 Microsoft SQL Server Management Studio(SSMS)에서 태스크의 백업과 복원을 통한 방법과 데이터 내보내기와 데이터 가져오기를 통한 방법으로 데이터베이스를 백업하고 백업한 파일을 가지고 다른 서버에서 복원하는 과정을 거쳐 데이터베이스를 마이그레이션 하게 됩니다. MS SQL Server 업그레이드도 마찬가지 방식으로 마이그레이션 하는 것이 일반적입니다.
하지만 위의 방법으로 백업 및 복원을 할 때 데이터베이스가 대용량이거나 MS SQL Server의 버전이 서로 다른 경우 복원이 제대로 안되는 경우가 발생할 때가 있습니다.
이를 경우 테스크 메뉴의 스크립트 생성으로 SQL 형식의 백업 파일을 만들고 Sqlcmd 명령어로 복원을 하는 방법을 사용해서 데이터베이스를 마이그레이션 할 수도 있습니다.
이번 글에서는 MS SQL 서버 대용량 DB를 스크립트 생성으로 백업 및 복원하는 방법(SSMS 마이그레이션)에 대해 알아보겠습니다.
아래 예제 화면은 MS SQL Server 2014의 데이터베이스를 MS SQL Server 2016으로 복원하는 과정을 화면 캡처한 내용입니다.
동일한 방법으로 SQL Server 2008의 데이터베이스를 SQL Server 2019로 복원하는 작업을 가장 최근에 진행한 적도 있습니다.
참고로 일반적인 백업 및 복원 또는 데이터 내보내기 및 가져오기 방식으로는 SQL Server 2008에서 SQL Server 2019로 바로 마이그레이션이 되지 않습니다. SQL Server 2014 또는 SQL Server 2016으로 먼저 마이그레이션 한 다음 다시 SQL Server 2019로 마이그레이션을 해 주어야 합니다.
하지만 스크립트 생성 방식으로 진행할 경우 SQL Server 2008에서 SQL Server 2019 또는 SQL Server 2022로 바로 마이그레이션이 가능합니다.
SSMS 스크립트 생성으로 MS SQL 데이터베이스의 SQL 백업 파일 만들기
1. DB의 스크립트 생성을 위해 Microsoft SQL Server Management Studio를 실행해서 엽니다.
2. 백업(마이그레이션)할 데이터베이스를 선택하고 마우스 오른쪽 버튼을 클릭하고 태스크 메뉴에서 스크립트 생성을 클릭해 줍니다.
3. 스크립트 생성 화면이 나타나면 첫 번째 개체 선택 단계를 진행합니다.
4. 복원하는 서버에 사용자는 이미 만들어져 있기에 [테이블]만 선택하고 [다음]을 클릭해 줍니다.
5. [테이블]을 펼쳐 원하는 테이블만 체크하고 다음으로 진행할 수도 있습니다.
6. 스크립팅 옵션 설정 단계에서 [고급]을 클릭해서 고급 스크립팅 옵션 창을 엽니다.
6-1. DROP 및 CREATE 스크립팅의 옵션을 선택합니다. [DROP 및 CREATE 스크립팅]로 선택해 줍니다.
- CREATE 스크립팅 : 테이블을 생성하는 스크립팅 구문이 포함됩니다.(디폴트 옵션)
- DROP 및 CREATE 스크립팅 : 기존의 테이블을 삭제하고 다시 생성하는 스크립팅 구문이 포함됩니다.
- DROP 스크립팅 : 테이블을 삭제하는 스크립팅 구문이 포함됩니다.
6-2. 스크립트 될 데이터 형식의 옵션에서 [스키마 및 데이터]로 선택해 줍니다.
- 스키마 : 테이블을 생성합니다.(디폴트 옵션)
- 스키마 및 데이터 : 테이블을 생성하고 데이터를 추가합니다.
- 데이터만 : 테이블이 생성되어 있다는 전제 하에 데이터만 추가합니다.
6-3. 인덱스 스크립팅의 옵션에서 [True]로 선택해 줍니다.
- False : 테이블의 인덱스를 생성하지 않습니다.(디폴트 옵션)
- True : 테이블의 인덱스를 생성합니다.
7. 고급 스크립팅 옵션의 지정이 끝났으면 [확인]을 클릭해서 고급 스크립팅 옵션 창을 닫습니다.
8. 스크립팅 옵션 설정 단계 화면으로 돌아와서 스크립트 파일 저장 위치와 파일 이름을 지정해 줍니다. 그리고 [다음]을 클릭해서 다음 단계로 넘어갑니다.
9. 개체 선택 및 스크립팅 옵션에 대한 요약 화면이 나타납니다.
10. 요약 정보가 모두 맞는지 확인하고 아래 [다음]을 클릭해서 스크립트 생성을 진행합니다.
11. 스크립트 생성이 진행되고, 선택한 테이블의 스키마와 데이터가 파일에 저장이 완료되면 [마침]을 클릭해서 스크립트 생성을 종료하면 됩니다.
12. 이렇게 해서 만들어진 sql 파일을 복원한 서버에서 사용하여 DB를 복원할 수 있습니다.
MS SQL 데이터베이스 스크립트 파일(sql)을 SQLCMD 명령어로 실행하는 방법(복원)
앞에서 생성한 스크립트 파일을 이용하여 MS SQL 서버에 DB를 복원하는 과정에 대한 설명입니다.
예제에서 생성한 스크립트 파일에는 테이블을 생성하는 스키마와 테이블의 데이터가 모두 포함되어져 있습니다.
▶ 만약 DROP 및 CREATE 스크립팅 옵션에서 디폴트인 [CREATE 스크립팅]으로 스크립트 파일을 생성했다면 기존의 테이블은 모두 삭제하고 작업을 진행해야 오류가 발생하지 않습니다.
▶ 옵션을 [DROP 및 CREATE 스크립팅]으로 선택했다면 아래의 테이블 삭제 과장은 건너 띄어도 됩니다. (5번부터 읽으시면 됩니다.)
1. MS SQL Server Management Studio에서 복원할 데이터베이스의 테이블을 모두 삭제합니다.
만약 스크립트 생성할 때 특정 테이블을 선택해서 생성했다면 선택한 테이블만 삭제해 주어야 합니다.
2. 이번 예제에서는 모든 테이블을 선택했기에 복원하는 서버의 테이블도 모두 삭제하겠습니다.
3. 데이터베이스에 테이블이 없는게 아래 화면을 통해 알 수 있습니다.
4. 이렇게 테이블을 삭제한 상태에서 sqlcmd 명령어로 스크립트 파일을 실행(복원)하는 작업을 진행하면 됩니다.
5. 명령어 프롬프트(cmd.exe) 창을 엽니다.
6. 스크립트 생성으로 만든 sql 파일이 있는 위치로 이동합니다.
위치로 이동하지 않고 파일을 지정할 때 Full Path를 지정해도 됩니다.
7. [Sqlcmd] SQL Server 명령어 도구로 복원 작업을 진행합니다.
사용법 : sqlcmd -S 서버 -i 입력파일 -o 출력파일 -U SQL로그인ID -P 암호 -d 데이터베이스이름
- -S : SQL Server의 서버이름 또는 IP주소를 지정해 줍니다.
- -i : 스크립트 파일 이름을 지정해 줍니다. Full Path 경로를 지정해도 됩니다.(C:\Temp\abc.sql)
- -o : 스크립트 파일이 실행되는 과정에서 만들어지는 Log가 기록되는 파일을 지정해 줍니다.
- -U : SQL Server의 사용자 ID를 지정해 줍니다.
- -P : 사용자 ID의 암호를 지정해 줍니다.
- -d : 스크립트가 실행될 DB를 지정해 줍니다. 지정한 DB에서 sql 쿼리문일 실행됩니다.
8. 스크립트 파일의 크기에 따라 실행되는 시간이 길어질 수 있습니다.
9. 아래 화면은 sqlcmd 명령어에 사용하는 옵션에 대한 상세 정보 입니다. 옵션이 위치하는 순서는 실행 결과에 영향을 미치지 않습니다.
10. sqlcmd를 실행한 뒤에 DB에 테이블이 생성된 것을 확인해 볼 수 있습니다.
결론 및 참고할 사항
MS SQL 서버의 데이터베이스 백업 및 복원(마이그레이션)을 스크립트 생성 파일로 진행하는 과정을 설명하였습니다.
스크립트로 처리할 경우 SQL Server 버전에 상관없이 마이그레이션을 할 수 있다는 큰 장점이 있습니다.
SQL서버 2008에서 SQL서버 2019 또는 SQL서버 2022로 마이그레이션 하는데도 전혀 문제가 발생하지 않고 별다른 어려움 없이 작업을 진행할 수 있습니다.
하지만 스크립트 생성으로 만들어진 파일이 대략 2GB를 초과하는 경우 모든 테이블의 데이터가 복원되지 않는 문제점이 있습니다.
따라서 하나의 스크립트 파일에 만들어지는 테이블의 수는 상관이 없지만 스크립트 파일 사이즈가 2GB를 초과하는 경우 적절하게 테이블을 나누어 스크립트 파일을 여러 개로 생성해 주어야 합니다.
만약 하나의 테이블 사이즈가 2GB를 초과하는 경우에는 다소 번거롭지만 테이블을 몇 개로 분리하는 작업을 먼저 한 뒤에 스크립트 생성 작업을 진행하고, sqlcmd로 복원한 뒤에 다시 하나의 테이블로 합치는 작업을 진행해 주어야 합니다.
※ 함께 읽으면 도움이 될 만한 다른 포스팅 글입니다.
이 글이 도움이 되었기를 바랍니다. ^-^