☰

백업 및 트랜잭션 로그를 자동으로 삭제하면 백업 지점이 손상됩니다.

tag-icon • tag-icon sql-server backup-restoration ssms incremental-backup
백업 및 트랜잭션 로그를 자동으로 삭제하면 백업 지점이 손상됩니다.

TLDR: 기본 로그 전달 작업 및 유지 관리 계획은 디스크에서 오래된 파일을 삭제하지만 백업은 여전히 ​​SSMS에 표시되므로 "복원" 창을 표시하는 속도가 매우 느립니다. 오래된 .trn/.bak를 올바르게 삭제하는 방법은 무엇입니까?

(Azure 가상 머신, Windows Server 2016 Datacenter(10.0), Microsoft SQL Server Web(2017 호환성 수준), SMMS 17.3)

(표시된 날짜는 24시간제 YYYY-MM-DD 또는 DD-MM-YYYY 형식입니다.)

데이터베이스 관리는 내 기술이나 직무에 포함되어 있지 않지만 MS SQL Server에서 백업을 준비하는 임무를 맡았으므로 이 주제에 대해서는 일반인처럼 대해 주십시오.

나는 매일 전체 데이터베이스 백업을 생성하고(물론 외부 저장소에 복사) 편의를 위해 그 중 일부를 서버에 보관하고 오래된 백업을 삭제한 다음 며칠 동안 특정 시점 복원을 위해 트랜잭션 로그로 구멍을 채우기로 결정했습니다. 약간의 인터넷 검색 후 테스트 데이터베이스에서 다음을 수행했습니다.

  1. 로그 전달에 필요한 수동 전체 백업을 생성했습니다. 여기서는 실패할 일이 없습니다.

  2. 오래된 파일을 삭제하여 기본 트랜잭션 로그 전달 일정 작업을 생성했습니다. 잘 작동합니다. 1분마다 .trn을 스팸으로 보내고 디스크에서 오래된 파일을 삭제합니다. 로그 전달 작업

  3. 자정에 테스트 데이터베이스에 대한 오래된 전체 백업을 생성하고 삭제하도록 유지 관리 계획을 설정하세요. 여기서도 문제 없습니다.

유지 관리 계획

  1. 금요일부터 월요일까지 실행해 보겠습니다. .trn과 .bak가 모두 적절하게 생성 및 삭제되었으므로 이틀 동안 사용할 수 있는 백업이 있었습니다. 로그

  2. 백업에서 복원을 시도했는데 트랜잭션 파일 누락으로 인해 문제가 발생했습니다. Tasks\Restore\Database를 여는 것은 매우 느립니다. SMMS는 ~10MB의 .trn 빈 데이터베이스에 대해 몇 분 동안 100% 디스크를 연삭하고 있습니다. 자동 전체 백업이 없는 데이터베이스의 경우 지금까지 생성된 모든 .trn(및 처음에는 수동 전체 백업)이 나열되었으며 분명히 "파일을 찾을 수 없음" 오류가 동반되었습니다. 일일 전체 백업이 있는 데이터베이스의 경우 기본적으로 제한된 결과가 다시 다음으로 설정됩니다. 마지막 전체 백업 및 복원은 제대로 작동했지만 백업 목록만 표시하는 데는 여전히 매우 느렸습니다. 탐색 타임라인도 매우 느립니다("응답 없음"이 몇 분 동안 지속됨).

스크립트 기록을 가져오는 스크립트를 찾았습니다.https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/(관심 있는 데이터베이스의 모든 백업을 반환하도록 약간 수정됨)

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE 
--(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
--and
 [database_name] = 'test'
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date

여기에는 삭제된 .trn 및 .bak 파일이 포함됩니다. 여기에 이미지 설명을 입력하세요

그리고 바로 지금까지 계속해서 총 29682개의 백업 지점이 있습니다.

"복원" 창이 다시 반응하기를 원하며 자유롭게 테스트할 수 있는 두 개의 테스트 데이터베이스가 있습니다. 백업 목록을 안전하게 지울 수 있나요? 현재 일정 작업을 잘 수행하려면 어떻게 자동으로 만들 수 있나요?

나는 db와 os 모두에 대한 완전한 관리 권한을 갖고 있고, 완전한 창의적 제어권과 vm에 많은 리소스가 남아 있으므로 가능한 솔루션에 대한 제한이 거의 없습니다.

답변1

다음을 사용하는 것이 가능합니다.sp_delete_backuphistory오래된 기록을 지우는 저장 프로시저. 무엇을 삭제했는지 여부는 알 수 없지만 사용자가 제공한 X 날짜보다 오래된 기록은 삭제됩니다.

방대한 양의 백업 항목을 지우면 응답이 가능해집니다. 작업의 마지막 단계로 현재 날짜에서 X 일 수를 뺀 SP를 호출하여 에이전트 작업으로 쉽게 래핑할 수 있습니다.

관련 정보