From: dpavlin <dpavlin@8392b6e1-25fa-0310-8288-cc32f8e212ea>
Date: Thu, 26 Jan 2006 23:24:11 +0000 (+0000)
Subject: if BackupPC_burnArchiveCLI complains about consistency error in backup_parts,
X-Git-Url: http://git.rot13.org/?p=BackupPC.git;a=commitdiff_plain;h=c5e0b74fda36bc2a7f57da3ddf5e726c234cb7e7

if BackupPC_burnArchiveCLI complains about consistency error in backup_parts,
this query will report which backups are problem

git-svn-id: svn+ssh://llin/home/dpavlin/private/svn/BackupPC/trunk@301 8392b6e1-25fa-0310-8288-cc32f8e212ea
---

diff --git a/sql/dvd_backup_parts_consistency_report.sql b/sql/dvd_backup_parts_consistency_report.sql
new file mode 100644
index 0000000..c3c067b
--- /dev/null
+++ b/sql/dvd_backup_parts_consistency_report.sql
@@ -0,0 +1,33 @@
+-- check consistency of data for one DVD image. This is more detailed than
+-- error message from BackupPC_Bur
+select
+	hosts.name || '_' ||
+	shares.name || '_' ||
+	backups.num as increment_name,
+	backups.date::abstime,
+	backups.type,
+	backups.size,
+	backups.parts,
+	count(backup_parts.backup_id) as backup_parts,
+	backups.inc_size,
+	backups.inc_deleted
+from archive_backup
+join archive on archive_id = archive.id
+join backups on backup_id = backups.id
+join shares on shareid = shares.id
+join hosts on backups.hostid = hosts.id
+full outer join backup_parts on backups.id = backup_parts.backup_id
+-- where dvd_nr = 14
+group by
+	hosts.name,
+	shares.name,
+	backups.num,
+	backups.date,
+	backups.type,
+	backups.size,
+	backups.parts,
+	backups.inc_size,
+	backups.inc_deleted
+having count(backup_parts.backup_id) <> parts
+order by inc_size desc
+;