Groups | Search | Server Info | Login | Register

Groups > comp.databases.ms-sqlserver > #2250

Re: Make a backup only if necessary

Show key headers only | View raw

Erland Sommarskog to Anton Shepelev:

> > 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.

Bear in mind that it is a /differential/ backup made
/immediately/ after a full backup.  The backup is essitially
empty and 1Mb is the size of boilerplate.

> > 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.

Right.

> Unless you would want to perform some sort of point-in-
> time restore.

Indeed I do.  And I might need to compare the last diff.
backups in order to determine whether the DB has changed in
between.  If it has not, the files will have equal size and
one of them be redundant.

> But normally, you use log backups for that, not diff
> backups.

They are a tad more difficult than full and diff. ones, and
whereas I need at most one backup per day, rather than, say,
one each 15 minutes, I have decided to content myself with a
combination of full and diff. backups.  That way, I can keep
my DBs in the simple recovery model.

> 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.

I believe exactly the same LSNs are available in the
`backupset' table:

  <https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql>

But they are not what I thought there were.  The `last_lsn'
field seems always to be unique for each backup set, even
for two consequtive diff. backups made within a second on an
unused database.

I belive that a non-trivial diff. backup may be detected by
comparing its `first_lsn' to the `first_lsn' of the previous
backup.  They compare equal in all my redundant diff.
backups.  For example, the query:

  SELECT type, first_lsn, last_lsn
  FROM msdb.dbo.backupset
  WHERE database_name = 'my_db'
  ORDER BY backup_set_id

Ends with:

  type  first_lsn           last_lsn
  --------------------------------------------
  I     362000000066900034  362000000068500001
  D     362000000068700037  362000000070400001
  I     363000000050400089  363000000054200001
  I     363000000054200001  363000000054500001
  I     363000000054500001  363000000054800001
  I     363000000054800001  363000000055100001
  I     363000000055100001  363000000055400001

Thanks you for the hint, Erland.

I still fail, however, to understand why `first_lsn' changes
for each subsequent diff. backup.  It is described as the
"log sequence number of the first or oldest log record in
the backup set." Since the last five diff. backups were
generated with respect to the same full backup, I expected
them to have the same first_lsn.  Why do they not?

-- 
()  ascii ribbon campaign -- against html e-mail
/\  www.asciiribbon.org   -- against proprietary attachments

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Make a backup only if necessary Anton Shepelev <anton.txt@g{oogle}mail.com> - 2024-05-27 12:59 +0300
  Re: Make a backup only if necessary Anton Shepelev <anton.txt@g{oogle}mail.com> - 2024-05-27 13:53 +0300
  Re: Make a backup only if necessary Erland Sommarskog <esquel@sommarskog.se> - 2024-05-27 22:43 +0200
    Re: Make a backup only if necessary Anton Shepelev <anton.txt@g{oogle}mail.com> - 2024-05-28 12:41 +0300
      Re: Make a backup only if necessary Erland Sommarskog <esquel@sommarskog.se> - 2024-05-28 20:11 +0200
        Re: Make a backup only if necessary Anton Shepelev <anton.txt@gmail.moc> - 2024-05-29 00:52 +0300
          Re: Make a backup only if necessary Anton Shepelev <anton.txt@g{oogle}mail.com> - 2024-05-31 13:53 +0300
            Re: Make a backup only if necessary Erland Sommarskog <esquel@sommarskog.se> - 2024-06-01 11:34 +0200
              Re: Make a backup only if necessary Anton Shepelev <anton.txt@gmail.moc> - 2024-06-02 14:43 +0300
                Re: Make a backup only if necessary Erland Sommarskog <esquel@sommarskog.se> - 2024-06-04 20:22 +0200
                Re: Make a backup only if necessary Anton Shepelev <anton.txt@g{oogle}mail.com> - 2024-06-05 17:15 +0300
                Re: Make a backup only if necessary Anton Shepelev <anton.txt@g{oogle}mail.com> - 2024-06-05 19:37 +0300

csiph-web