Path: csiph.com!news.swapon.de!eternal-september.org!feeder3.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: Make a backup only if necessary Date: Mon, 27 May 2024 22:43:08 +0200 Organization: Erland Sommarskog Lines: 23 Message-ID: References: <20240527125929.d701039c2aec3196dc3d4731@g{oogle}mail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Date: Mon, 27 May 2024 22:43:09 +0200 (CEST) Injection-Info: dont-email.me; posting-host="a9505596827cd7d920d5e66709b2f3c8"; logging-data="231093"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/n2Sml4gIYWtsVIEUSwwmH" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:DW4AoiwtwDNDgm71wFm0Slkfsmk= Xref: csiph.com comp.databases.ms-sqlserver:2249 Anton Shepelev (anton.txt@g{oogle}mail.com) writes: > I am working on an in-house backup solution for our MSSQL > databases, and have found that a differential backup may > take considerable space (around 1 Mb) I'm tempted to say that 1MB in size for a database back is very small. > I want to make sure that my differential backups are not > useless. One solution is to compare byte-for-byte each new > diff. backup with the previous one, and delete the copy if > it turn out redundant, but I wondered if there were a > simpler method. To restore a database you need most recent full and most recent diff, so keeping both is not really necessary. Unless you would want to perform some sort of point-in-time restore. But normally, you use log backups for that, not diff backups. In any case, RESTORE HEADERONLY on the backup and checking last LSN seems to be like a tenable solution. You could also use sys.fn_dblog to find the last LSN - but keep in mind that this is an undocumented and unsupported function.