Backup databases and Checkpoint

A simple question.
I have a database that's being used by several developers. Prior to making
a backup
using the backup command, should I issue a checkpoint command to write
memory
pages to disk? My database is in simple recovery mode. I understand that
uncommitted
changes are not backed up (as I would expect).
If I don't need to checkpoint, then under what circumstance is it used?No need to issue a checkpoint prior to making a backup regardless of the
recovery mode of the database. If there is any uncommitted transaction, SQL
Server will undo any changes (i.e. rollback the transaction) when it recover
s
the database upon restore. And if there are dirty pages of a committed
transaction (i.e. the data page does not reflect the state of the committed
transaction), SQL Server will roll forward these pages during a recovery.
A checkpoint flushes all dirty pages (including both data and log) of a
database to disk, and is parimarily used to shorten the recovery time when
the database is recovered, particularly during the SQL instance startup.
Linchi
"Dodo Lurker" wrote:

> A simple question.
> I have a database that's being used by several developers. Prior to makin
g
> a backup
> using the backup command, should I issue a checkpoint command to write
> memory
> pages to disk? My database is in simple recovery mode. I understand th
at
> uncommitted
> changes are not backed up (as I would expect).
> If I don't need to checkpoint, then under what circumstance is it used?
>
>|||Thank you
Say I just want to issue the checkpoint so any "dirty" pages are written to
disk that may have been
part of a committed transaction. You're saying that the backup will have
those in-memory changes applied to the backup?
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:7E8E6183-B338-45B8-8543-984660531A42@.microsoft.com...
> No need to issue a checkpoint prior to making a backup regardless of the
> recovery mode of the database. If there is any uncommitted transaction,
SQL
> Server will undo any changes (i.e. rollback the transaction) when it
recovers
> the database upon restore. And if there are dirty pages of a committed
> transaction (i.e. the data page does not reflect the state of the
committed[vbcol=seagreen]
> transaction), SQL Server will roll forward these pages during a recovery.
> A checkpoint flushes all dirty pages (including both data and log) of a
> database to disk, and is parimarily used to shorten the recovery time when
> the database is recovered, particularly during the SQL instance startup.
> Linchi
> "Dodo Lurker" wrote:
>
making[vbcol=seagreen]
that[vbcol=seagreen]|||> Say I just want to issue the checkpoint so any "dirty" pages are written to">
> disk that may have been
> part of a committed transaction. You're saying that the backup will have
> those in-memory changes applied to the backup?
Yes. And, as I remember, SQL Server does a checkpoint as a part of the datab
ase backup (at the
starting point of the backup). The important point about backup is that a da
tabase backup will
include the committed transactions and all in-flight transactions will be ro
lled backup (if you do
the restore of that backup using the RECOVERY option).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dodo Lurker" <none@.noemailplease> wrote in message
news:sJidnWEYmrDr32HZnZ2dnUVZ_r2dnZ2d@.co
mcast.com...
> Thank you
> Say I just want to issue the checkpoint so any "dirty" pages are written t
o
> disk that may have been
> part of a committed transaction. You're saying that the backup will have
> those in-memory changes applied to the backup?
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:7E8E6183-B338-45B8-8543-984660531A42@.microsoft.com...
> SQL
> recovers
> committed
> making
> that
>|||If you read the "Checkpoint" information in the Books Online, you will see
this:
"Before a database backup, the Database Engine automatically performs a
checkpoint so that all changes to the database pages are contained in the
backup. "
HTH
Kalen Delaney, SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:y9adnb3C0uOACmbZnZ2dnUVZ_sednZ2d@.co
mcast.com...
>A simple question.
> I have a database that's being used by several developers. Prior to
> making
> a backup
> using the backup command, should I issue a checkpoint command to write
> memory
> pages to disk? My database is in simple recovery mode. I understand
> that
> uncommitted
> changes are not backed up (as I would expect).
> If I don't need to checkpoint, then under what circumstance is it used?
>sql