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 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
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 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?
>
>|||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
> 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
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?
> >
> >
> >|||> 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 database backup (at the
starting point of the backup). The important point about backup is that a database backup will
include the committed transactions and all in-flight transactions will be rolled 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@.comcast.com...
> 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
>> 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
> 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?
>> >
>> >
>> >
>|||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@.comcast.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?
>