Path: csiph.com!weretis.net!feeder8.news.weretis.net!eternal-september.org!feeder3.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail From: Anton Shepelev Newsgroups: comp.databases.ms-sqlserver Subject: Re: Make a backup only if necessary Date: Wed, 5 Jun 2024 17:15:17 +0300 Organization: A noiseless patient Spider Lines: 38 Message-ID: <20240605171517.0b0e856d919630ebaf31cfb7@g{oogle}mail.com> References: <20240527125929.d701039c2aec3196dc3d4731@g{oogle}mail.com> <20240528124112.80ab65ec4a358da57093539b@g{oogle}mail.com> <20240529005233.d0405d2f6f826fd0e8ae0624@gmail.moc> <20240531135309.a107b0e4fc99f9659bed7d8c@g{oogle}mail.com> <20240602144320.325085f5f455d2f954cced20@gmail.moc> MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Injection-Date: Wed, 05 Jun 2024 16:15:20 +0200 (CEST) Injection-Info: dont-email.me; posting-host="528714bd3cd16f47e34b19250212c03f"; logging-data="1062769"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18afta89DgcUB5u8OkM/wzR8Jw676S3Dlg=" Cancel-Lock: sha1:T/2+R5E5Y5MOV5Jkp5vlIW3TlNE= X-Newsreader: Sylpheed 3.7.0 (GTK+ 2.24.30; i686-pc-mingw32) Xref: csiph.com comp.databases.ms-sqlserver:2257 Erland Sommarskog: > Since the log is truncated on checkpoint, the log could > look empty. Indeed. I wish I knew how to track LSNs more reliably. > Then again, if you track LSN:s you should be safe. Do you mean your suggestion to use `RESTORE HEADERONLY'? If so, I have already remarked that its FirstLSN LastLSN fields are also available from the msdb.backupset table. Unfortunately, tracking them produces a lot of false positives, because of all the event types, I need only those that refer to data changes, that is: LOP_INSERT_ROWS, LOP_MODIFY_ROW, LOP_DELETE_ROWS Other events should not trigger a backup. > > I believe my situation is not unique, when there are > > many infrequently used databases and no requirement of a > > 15-minute backup granularity, so that keeping the simple > > recovery model and doing a backup /only/ when data has > > changed safes a log of resources. > > Log of resources, hehe. :-) Of course, so that nothing slips by. > Then again, following the normal routines saves quite a > bit of human resources. At the expense of increased boredom, and customisation. -- () ascii ribbon campaign -- against html e-mail /\ www.asciiribbon.org -- against proprietary attachments