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 (file)
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
+;