469,609 Members | 2,250 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

Backup Plans and Locking Database

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?

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

Jul 23 '05 #1
2 1865
Matt (ma***********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Matt (ma***********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Iain Hosking | last post: by
3 posts views Thread by bryja_klaudiusz[at]poczta[dot]fm | last post: by
9 posts views Thread by tech_guru | last post: by
2 posts views Thread by David | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.