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 databases and access rights

Hi,

I am creating a way of working in order to "copy" databases from a master SQL Server 2000 database to the developers local machines.

I want to create a master SQL Server at our office location. Whenever our developers works on site the master is used. To be able to work locally, a backup has been created of the master and is distributed to the developers. On their local machines, they restore the database to be able to develop locally.

I have created a backup on my laptop using on my domain account (not the account that created the database, but I can use it when developing). I moved the backup file to another computer and restored it using my domain account on that computer. I could not see all of the tables, users or stored procedures, only those which type are System, not User. When i log in to the local administrators account I can see them all. When I log back into my domain account I can see all of the tables.

Anyone knows why?

/M

SQL Enterprise Manager doesn't refresh objects very well. You actually have to force it to refresh sometimes by hitting F5 and even that doesn't always work. Something to try in this case is a quick select statement in query analyzer of a table that you know should be there. Typically it will either show the results or give you the error associated with permissions.

It also sounds like it could be db permissions problems. Typically, SEM connects to the local server via the sa login. So when you login as yourself, you are probably connecting to the server as yourself in SEM. Also keep in mind that objects are owned by their creators and unless permissions are specifically granted for other users/roles or the current user is an admin, you won't be able to work with these objects. So if you created objects under one login (such as sa) you may not be able to see them under another login (such as your domain account). sp_changeobjectowner is a way to modify the owner for each object. sp_MScheck_uid_owns_anything will help determine if the user owns anything; just use the id of the user as the only parameter.

Another option would be to grant all permissions to the public role for all objects as they are created. This is not advised because you are not using SQL Security like it should. But roles are a good thing in environments like this, but these take some setup and administration time.