커맨드라인 스크립트로 Microsoft SQLServer 백업과 복원을 자동화하기

테크팁·활용

커맨드라인 스크립트로 Microsoft SQLServer 백업과 복원을 자동화하기 - MSSQL 백업&복원

apost 2022. 9. 29. 16:20

항상 켜져있지 않은 백업 서버나 개발용 시스템에 DB 백업본을 자주 복원해 작업해야 할 때, 무거운 SSMS를 실행해서 수동으로 복원 작업을 하지 않아도 되는 커맨드라인 자동화 스크립트에 대한 소개입니다.

1. 복원 자동화하기

DBA나 개발자가 개발 서버에 개발용 데이터베이스를 유지할 때 최근 백업 데이터를 복원해서 데이터 확인 작업을 하는 것은 꽤나 번거로운 작업입니다.

SSMS로 매번 백업본을 복사해서 복원 작업하는 것도 귀찮은 작업일 뿐만 아니라 시간도 꽤나 소요됩니다.

이런 경우 다음과 같은 커맨드라인 스크립트를 이용해 복원을 자동화 할 수 있습니다.

작성한 스크립트는 .cmd 확장자를 가지는 배치파일로 저장한 후 윈도우 작업 스캐줄러에 등록해서 주기적으로, 또는 로그인을 할 때 자동으로 실행되도록 해서 복원 작업을 완전히 자동화 할 수 있습니다.

FOR /F "eol=| delims=" %%I IN ('DIR "c:\_DB\BACKUP\mydatabase\*.bak" /A-D /B /O-D /TW 2^>nul') DO (
    SET NewestBackup=%%I
    GOTO FoundBackup
)
ECHO No *.bak file found!
GOTO :EOF

:FoundBackup
ECHO Newest *.bak file is: %NewestBackup%

sqlcmd -S localhost -U dbuser -P loginpassword -Q "ALTER DATABASE [mydatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;RESTORE DATABASE [mydatabase] FROM DISK = 'c:\_DB\BACKUP\mydatabase\%NewestBackup%' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 5;ALTER DATABASE [mydatabase] SET MULTI_USER;"

:EOF

커맨드라인 스크립트에 익숙하지 않으면 복붙해서 백업파일이 있는 경로와 데이터베이스 이름, 그리고 DB 사용자/패스워드만 변경해서 사용하면 됩니다.

1. ":라벨"은 "GOTO" 커맨드로 이동할 수 있는 점프 위치입니다. ":FoundBackup", ":EOF" 가 위치 라벨입니다.

2. 맨 위의 "FOR" 문은 폴더 안의 파일들 중에서 날짜 기준으로 가장 최근의 파일을 "NewestBackup" 변수에 저장하는 루프문입니다. 가장 최근파일을 찾아서 "NewestBackup" 변수에 저장(SET)한 후 "FoundBackup" 라벨 위치로 점프합니다.

폴더 안에서 백업 파일을 못찾았으면 ":EOF" 라벨로 이동해서 백업 파일 복원 과정 없이 스크립트를 종료합니다.

3. sqlcmd 는 SQLServer의 커맨드라인 sql 스크립트 실행 프로그램입니다.

SSMS로 실행하는 백업과 복원 쿼리문을 커맨드라인으로 실행할 수 있습니다.

데이터베이스를 복원할 때는 싱글유저 모드로 먼저 전환한 후 백업 파일을 복원하고, 다시 멀티유저 모드로 전환해서 데이터베이스를 사용가능하도록 해야 합니다.

sqlcmd로 실행하는 한줄로 된 sql 쿼리문은 실제로는 다음의 3개의 쿼리문입니다.

변수를 사용할 때는 "%변수명%" 과 같이 변수 앞뒤에 "%"를 붙여서 값을 적용합니다.

ALTER DATABASE [mydatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [mydatabase] FROM DISK = 'c:\_DB\BACKUP\mydatabase\%NewestBackup%' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 5;
ALTER DATABASE [mydatabase] SET MULTI_USER;

sqlcmd 쿼리문을 실행할 때는 서버 IP, DB 사용자, 사용자 패스워드를 설정해서 원격 DB에 복원을 할 수도 있습니다.

같은 방법으로 트랜잭션 로그(*.trn)도 복원을 하면 됩니다.

FOR /F "eol=| delims=" %%I IN ('DIR "c:\_DB\BACKUP\mydatabase\*.trn" /A-D /B /O-D /TW 2^>nul') DO (
    SET NewestLog=%%I
    GOTO FoundLog
)
ECHO No *.trn file found!
GOTO :EOF

:FoundLog
ECHO Newest *.trn file is: %NewestLog%

sqlcmd -S localhost -U dbuser -P loginpassword -Q "RESTORE LOG mydatabase FROM DISK = 'ALTER DATABASE [mydatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;c:\_DB\BACKUP\mydatabase\%NewestLog%';ALTER DATABASE [mydatabase] SET MULTI_USER;"

데이터베이스 백업과 트랜잭션 로그를 복원했지만, 데이터베이스에 전용의 DB사용자를 별도로 생성해서 접근을 하는 웹사이트나 앱을 구현한 경우 DB사용자에 복원한 DB에 대한 접근 권한이 없어서 정상적인 서비스 구동이 되지 않습니다.

다음의 sqlcmd를 사용해서 복원한 DB의 기존 DB사용자를 삭제한 후 접근 권한이 있는 새 DB 사용자를 생성합니다.

다음 스크립트는 복원한 DB의 DB 사용자가 "mydatabase_dbuser" 인 경우에 대한 예입니다.

sqlcmd -S localhost -U dbuser -P loginpassword -Q "use mydatabase;drop user mydatabase_dbuser;CREATE USER mydatabase_dbuser FOR LOGIN mydatabase_dbuser;EXEC sp_addrolemember N'db_datareader', N'mydatabase_dbuser';EXEC sp_addrolemember N'db_datawriter', N'mydatabase_dbuser';"

2. 백업 자동화하기

SQL Server Agent 로 백업 스캐줄링을 해도 되지만, sql 백업 스크립트를 만들어서 윈도우 스캐줄러를 이용해 sqlcmd를 실행해도 됩니다.

먼저 다음과 같은 sql 스크립트 파일을 만듭니다. 확장자는 sql로 하면 됩니다.

-- db backup
DECLARE @name NVARCHAR(256);

set @name='d:\mydatabase\DBBackup\mydatabase\mydatabase_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + right ('00'+ltrim(str( DATENAME(hh, GETDATE()))),2 ) + right ('00'+ltrim(str( DATENAME(mi, GETDATE()))),2 ) + right ('00'+ltrim(str( DATENAME(ss, GETDATE()))),2 ) + '.bak';
Backup Database mydatabase To Disk = @name;
set @name='d:\mydatabase\DBBackup\mydatabase\mydatabase_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + right ('00'+ltrim(str( DATENAME(hh, GETDATE()))),2 ) + right ('00'+ltrim(str( DATENAME(mi, GETDATE()))),2 ) + right ('00'+ltrim(str( DATENAME(ss, GETDATE()))),2 ) + '.trn';
Backup LOG mydatabase To Disk = @name;

@name은 백업파일 이름을 정하는 변수명입니다.

특정 폴더에 백업파일을 주기적으로 만들게 되면 파일명에 시간 표시가 되야 파일명 중복이 발생하지 않고 관리가 가능하기 때문에 파일명을 연월일시분초 값을 이용해 정하게 됩니다.

CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + right ('00'+ltrim(str( DATENAME(hh, GETDATE()))),2 ) + right ('00'+ltrim(str( DATENAME(mi, GETDATE()))),2 ) + right ('00'+ltrim(str( DATENAME(ss, GETDATE()))),2 )

년월일시분초를 위의 변환 커맨드로 변환해서 조합하면 "20220814_125709" 과 같은 문자열이 만들어집니다.

최종적으로는 "mydatabase_20220814_125709.bak" 과 같은 백업 파일명이 만들어집니다.

저장한 sql 파일은 윈도우 스캐줄러에 다음과 같이 등록해주면 됩니다.

스캐줄러로 sqlcmd 커맨드 프로그램을 실행하려면 path 로 경로가 등록되어 있어야 합니다. path 등록 문제를 피하려면 다음과 같이 전체 경로를 찾아서 프로그램/스크립트로 입력해야 합니다.

SQLServer 버전에 따라 경로가 다르므로 사용하는 SQLServer 버전에 따라 경로를 수정해야 합니다.

"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE" -S serverip -U dbuser -P userpassword -i c:\_DB\BACKUP\sql_backup_script.sql

3. 일정 기간이 경과한 파일 자동 삭제하기

일정 기간이 경과한 백업 파일들을 일일이 삭제하기 번거로울 때 윈도우 스캐줄러에 등록해서 일정 기간이 경과한 파일들을 삭제해서 백업파일들이 차지하는 공간을 일정하게 유지시킬 수 있습니다.

다음 스크립트는 "Z:\RemoteBackup\DB_Backup" 폴더 안의 모든 파일들 중 31일이 경과한 파일들을 모두 삭제하는 스크립트입니다.(-d 31 이 날짜 제한 옵션입니다.)

.cmd 확장자로 저장한 후 윈도우 스캐줄러에 등록해서 사용하면 됩니다.

특정 파일만 삭제하려면 "*.*" 대신 "*.bak" 과 같이 확장자를 지정해서 삭제할 파일 종류를 제한할 수 있습니다.

forfiles -p "Z:\RemoteBackup\DB_Backup" -s -m *.* -d -31 -c "cmd /c del @PATH"