Posted by Erland Sommarskog on 10/01/38 11:18

Matt (matt_marshall@manning-napier.com) writes:
> I run SQL Server 2000 and use thier database maintenance plans to
> backup my databases. My questions is what happens if a change is being
> made to a database table while a backup is running? Should I be locking
> the databases before the backup begins?

No! The Backup routine is designed to give you a transaction-consistent
backup.

> Scenario:
>
> 1) Database Plan begins backup at 7:00 PM.
> 2) At 7:01 PM a web user updates their password while the backup is
> taking place.
> 3) at 7:15 PM the backup is complete.
>
> In this scenario, will the change/update made in step (2) be contained
> within the backup?

I think so, but to be honest, I have not memorized the fine print of
BACKUP, so I don't know for sure. Actually, it does not really matter if
that password change makes it to the backup or not. (Trivial proof: assume
the user changes is password at 19:16.)

What is important, is that while the backup is running, there is a
transaction running that updates the salaries of all employees with
2%. In the backup all employees all must either have the old salary
or all have the new salary. If you were to backup the database file
through the file system this could happen.

> On Another note, can anyone point me in the right direction for "Best
> Practices" on establishing a Recovery Mode for a database? I have been
> told to set it to "Simple" so the logs do consume a lot of disc space
> and performance. The issue with this is I am not sure if I should be
> maintaining a full transaction log. Our database only changes on a
> monthly basis. The only "on going" updates that may take place is a
> user changing their password.

The main determine factor whether to use full or bulk-logged recovery
on one hand or simple recovery on the other is: what happens at a crash?
Are you content with restoring the most recent backup, or you need an
up-to-the point recovery to minimize data loss?

From your description, it sounds that simple is alright, assuming that
you take a full backup after the monthly update. If there is a crash,
all you could lose is a few newly changed passwords.

> We also have other databases that change often throughout the day. I
> assume after a transaction log has been truncated, the amount of disk
> space it consumes is minimal? So please any information or a kick in the
> right direction would be much appreciated. Thanks.

First, ir you truncate the transaction log without backing it up,
you lose your ability to the up-to-the-point recovery. Only do this
in an emergency.

Second, just truncating the transaction log does not conserve disk
space, unless the database is set to autoshrink (which is not a good
think to do). You will have to skrink it with DBCC SHRINKFILE. However,
it's little point in shrinking it, if it is to grow again. Better
leave it as it is, unless it has become horrendesouly big.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]