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

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

Backup and Restore with STOPAT

Hi
I want to implement the following simple scenario as a test prior the real
implementation to see if these scenario is plausible. I want to test the
STOPAT option of the RESTORE LOG command.
1. First I want to full backup the database (the "Recovery model"
option is set to "Full") using the following code:
BACKUP DATABASE atsTables
TO DISK = 'c:\temp\20060318_1236_atsTables.dat'
WITH
DESCRIPTION = 'atsTables Backup1'
2. After backup I will insert a new row in one table of the db with the
following command (clock time: 12:37:xx):
INSERT INTO Classes(Title)
VALUES('Test1')
3. After at least one minute from the first insert I will insert
another row to the db (now the time is: 12:38:xx)
INSERT INTO Classes(Title)
VALUES('Test2')
4. At 12:39:xx I will backup the Transaction protocol with the
following command:
BACKUP LOG atsTables
TO DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
5. Now I will restore the db:
RESTORE DATABASE atsTables
FROM DISK = 'c:\temp\20060318_1236_atsTables.dat'
WITH
NORECOVERY
, REPLACE
6. I will now restore the transaction protocol and stop at 12:37:59
DECLARE @.dtm as DateTime
SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
RESTORE LOG atsTABLES
FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
WITH
Recovery
, STOPAT = @.dtm
7. I expect to have now in the "Classes" table only the "Test1" class
"Title".
USE atsTables
SELECT *
FROM Classes
8. But I get the error that the DB cannot be opened because it is still
restoring
9. Ok, so I don't know for sure what's wrong so I use the RESTORE LOG
command again as before:
DECLARE @.dtm as DateTime
SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
RESTORE LOG atsTABLES
FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
WITH
Recovery
, STOPAT = @.dtm
10. I select again the Classes table:
USE atsTables
SELECT *
FROM Classes
11. Now it works but I get both classes ("Test1" and "Test2") instead of
only the "Test1" class title
The question is:
Why is the STOPAT option of the RESTORE LOG not working as I expected? Is my
scenario wrong? Can someone help me further?
Any answer can help me a lot.
Thanks, George.Hi
Read this article
http://www.karaszi.com/SQLServer/in...veral_times.asp
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:e68eWErSGHA.196@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I want to implement the following simple scenario as a test prior the real
> implementation to see if these scenario is plausible. I want to test the
> STOPAT option of the RESTORE LOG command.
>
> 1. First I want to full backup the database (the "Recovery model"
> option is set to "Full") using the following code:
>
> BACKUP DATABASE atsTables
> TO DISK = 'c:\temp\20060318_1236_atsTables.dat'
> WITH
> DESCRIPTION = 'atsTables Backup1'
>
> 2. After backup I will insert a new row in one table of the db with
> the following command (clock time: 12:37:xx):
>
> INSERT INTO Classes(Title)
> VALUES('Test1')
>
> 3. After at least one minute from the first insert I will insert
> another row to the db (now the time is: 12:38:xx)
>
> INSERT INTO Classes(Title)
> VALUES('Test2')
>
> 4. At 12:39:xx I will backup the Transaction protocol with the
> following command:
>
> BACKUP LOG atsTables
> TO DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
>
> 5. Now I will restore the db:
>
> RESTORE DATABASE atsTables
> FROM DISK = 'c:\temp\20060318_1236_atsTables.dat'
> WITH
> NORECOVERY
> , REPLACE
>
> 6. I will now restore the transaction protocol and stop at 12:37:59
>
> DECLARE @.dtm as DateTime
> SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
>
> RESTORE LOG atsTABLES
> FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
> WITH
> Recovery
> , STOPAT = @.dtm
>
> 7. I expect to have now in the "Classes" table only the "Test1" class
> "Title".
>
> USE atsTables
> SELECT *
> FROM Classes
>
> 8. But I get the error that the DB cannot be opened because it is
> still restoring
> 9. Ok, so I don't know for sure what's wrong so I use the RESTORE LOG
> command again as before:
>
> DECLARE @.dtm as DateTime
> SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
>
> RESTORE LOG atsTABLES
> FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
> WITH
> Recovery
> , STOPAT = @.dtm
>
> 10. I select again the Classes table:
>
> USE atsTables
> SELECT *
> FROM Classes
>
> 11. Now it works but I get both classes ("Test1" and "Test2") instead of
> only the "Test1" class title
>
> The question is:
> Why is the STOPAT option of the RESTORE LOG not working as I expected? Is
> my scenario wrong? Can someone help me further?
>
> Any answer can help me a lot.
> Thanks, George.
>|||Hi, George
You should verify that the @.dtm variable contains the expected value. I
guess that the conversion of the varchar value to a datetime resulted
in a different value than expected. You should specify the dates in a
language neutral format, for example '20060318 12:37:59' or
'2006-03-18T12:37:59'. See the following article for more informations:
http://www.karaszi.com/SQLServer/info_datetime.asp
Razvan|||After reading the article everithing is clear now and works.
Thanks for help.
George.
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:e68eWErSGHA.196@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I want to implement the following simple scenario as a test prior the real
> implementation to see if these scenario is plausible. I want to test the
> STOPAT option of the RESTORE LOG command.
>
> 1. First I want to full backup the database (the "Recovery model"
> option is set to "Full") using the following code:
>
> BACKUP DATABASE atsTables
> TO DISK = 'c:\temp\20060318_1236_atsTables.dat'
> WITH
> DESCRIPTION = 'atsTables Backup1'
>
> 2. After backup I will insert a new row in one table of the db with
> the following command (clock time: 12:37:xx):
>
> INSERT INTO Classes(Title)
> VALUES('Test1')
>
> 3. After at least one minute from the first insert I will insert
> another row to the db (now the time is: 12:38:xx)
>
> INSERT INTO Classes(Title)
> VALUES('Test2')
>
> 4. At 12:39:xx I will backup the Transaction protocol with the
> following command:
>
> BACKUP LOG atsTables
> TO DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
>
> 5. Now I will restore the db:
>
> RESTORE DATABASE atsTables
> FROM DISK = 'c:\temp\20060318_1236_atsTables.dat'
> WITH
> NORECOVERY
> , REPLACE
>
> 6. I will now restore the transaction protocol and stop at 12:37:59
>
> DECLARE @.dtm as DateTime
> SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
>
> RESTORE LOG atsTABLES
> FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
> WITH
> Recovery
> , STOPAT = @.dtm
>
> 7. I expect to have now in the "Classes" table only the "Test1" class
> "Title".
>
> USE atsTables
> SELECT *
> FROM Classes
>
> 8. But I get the error that the DB cannot be opened because it is
> still restoring
> 9. Ok, so I don't know for sure what's wrong so I use the RESTORE LOG
> command again as before:
>
> DECLARE @.dtm as DateTime
> SET @.dtm = CONVERT(DateTime, '18.03.2006 12:37:59')
>
> RESTORE LOG atsTABLES
> FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat'
> WITH
> Recovery
> , STOPAT = @.dtm
>
> 10. I select again the Classes table:
>
> USE atsTables
> SELECT *
> FROM Classes
>
> 11. Now it works but I get both classes ("Test1" and "Test2") instead of
> only the "Test1" class title
>
> The question is:
> Why is the STOPAT option of the RESTORE LOG not working as I expected? Is
> my scenario wrong? Can someone help me further?
>
> Any answer can help me a lot.
> Thanks, George.
>