Path: csiph.com!news.swapon.de!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: Fri, 31 May 2024 13:53:09 +0300 Organization: A noiseless patient Spider Lines: 28 Message-ID: <20240531135309.a107b0e4fc99f9659bed7d8c@g{oogle}mail.com> References: <20240527125929.d701039c2aec3196dc3d4731@g{oogle}mail.com> <20240528124112.80ab65ec4a358da57093539b@g{oogle}mail.com> <20240529005233.d0405d2f6f826fd0e8ae0624@gmail.moc> MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Injection-Date: Fri, 31 May 2024 12:53:11 +0200 (CEST) Injection-Info: dont-email.me; posting-host="ee9bbbfcaa519edb441c803715f4013f"; logging-data="2319634"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/FzHRDn9ejo+xEdW4GTt5zTmsA10Iitus=" Cancel-Lock: sha1:K8HLIuW0yt8Q6aWuJQDofnHWWpQ= X-Newsreader: Sylpheed 3.7.0 (GTK+ 2.24.30; i686-pc-mingw32) Xref: csiph.com comp.databases.ms-sqlserver:2253 I wrote: > Now my system uses LSNs to avoid storing redundant > differential backups For now, I have decided upon detecting changes elibible for backup by the following query: SELECT TOP 1 1 FROM fn_dblog(NULL,NULL) WHERE operation IN ( 'LOP_INSERT_ROWS', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWS') AND AllocUnitName NOT LIKE 'sys.%' The output of `fn_dblog' is cleared after every full and diff backup, probably because the transaction log is cleared, although I don't know why since my recovery model is `simple' anyway, and it should not need a backup to be cleared... The major problem with this approach is that fn_dblog is an undocumented function apt to change or disappear any time. If anybody know how to perform an equivalent test using more stable and reliable method, I shall be grateful if they share it. -- () ascii ribbon campaign -- against html e-mail /\ www.asciiribbon.org -- against proprietary attachments