Database Backup And Transaction Log - SQL Server 2000

I would like to find the cleanest way to do a backup and not have to backup
the log.
I have a number of DTS jobs that run as batch - loads data into SQL Server.
I know for fact that at a certain time after the batch, no one is in the
database and no jobs are running that affect the database of system
databases. I was thinking of just executing a CHECKPOINT to force any
completed records to disc, backing up the database, and then truncating the
log. Is this the correct order? I became when I saw an article that
did a backup, then checkpoint and then truncate log - in that condition the
backup would never cover the CHECKPOINTED data.
Any suggestions would be helpful. Database has full recovery mode.You will have to backup the log - or take the risk of going with the Simple
recovery model.
If you like, you can backup the log just prior to the start of the DTS jobs.
This will ensure that you have as much space as possible during the DTS run.
If your packages load a lot of data, you can backup the log more frequently
during the run than you would at other times. After the DTS run is done,
that would be a good time to schedule your full backup.
Another thing you may want to consider is to switch your recovery model to
Bulk-logged during the running of the DTS package(s).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"MG" <MG@.discussions.microsoft.com> wrote in message
news:5A388DAD-5C6A-414C-946E-DEBA3922A769@.microsoft.com...
I would like to find the cleanest way to do a backup and not have to backup
the log.
I have a number of DTS jobs that run as batch - loads data into SQL Server.
I know for fact that at a certain time after the batch, no one is in the
database and no jobs are running that affect the database of system
databases. I was thinking of just executing a CHECKPOINT to force any
completed records to disc, backing up the database, and then truncating the
log. Is this the correct order? I became when I saw an article that
did a backup, then checkpoint and then truncate log - in that condition the
backup would never cover the CHECKPOINTED data.
Any suggestions would be helpful. Database has full recovery mode.|||
When you backup the database and the log then you will be able to shrink the
log file. If you do not backup the log then you cannot shrink therefore the
log file could get very big. I have a feeling you already know that.
"MG" <MG@.discussions.microsoft.com> wrote in message
news:5A388DAD-5C6A-414C-946E-DEBA3922A769@.microsoft.com...
>I would like to find the cleanest way to do a backup and not have to backup
> the log.
> I have a number of DTS jobs that run as batch - loads data into SQL
> Server.
> I know for fact that at a certain time after the batch, no one is in the
> database and no jobs are running that affect the database of system
> databases. I was thinking of just executing a CHECKPOINT to force any
> completed records to disc, backing up the database, and then truncating
> the
> log. Is this the correct order? I became when I saw an article
> that
> did a backup, then checkpoint and then truncate log - in that condition
> the
> backup would never cover the CHECKPOINTED data.
> Any suggestions would be helpful. Database has full recovery mode.|||Tom, Thanks for the info but I am not sure why my concept would not work.
Simply CHECKPOINT the trans log to get what is left in the trans log to the
mdf, truncate the log, then do a database backup (without backingup the tran
s
log). Why would this not work or be affective?
"Tom Moreau" wrote:

> You will have to backup the log - or take the risk of going with the Simpl
e
> recovery model.
> If you like, you can backup the log just prior to the start of the DTS job
s.
> This will ensure that you have as much space as possible during the DTS ru
n.
> If your packages load a lot of data, you can backup the log more frequentl
y
> during the run than you would at other times. After the DTS run is done,
> that would be a good time to schedule your full backup.
> Another thing you may want to consider is to switch your recovery model to
> Bulk-logged during the running of the DTS package(s).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "MG" <MG@.discussions.microsoft.com> wrote in message
> news:5A388DAD-5C6A-414C-946E-DEBA3922A769@.microsoft.com...
> I would like to find the cleanest way to do a backup and not have to backu
p
> the log.
> I have a number of DTS jobs that run as batch - loads data into SQL Server
.
> I know for fact that at a certain time after the batch, no one is in the
> database and no jobs are running that affect the database of system
> databases. I was thinking of just executing a CHECKPOINT to force any
> completed records to disc, backing up the database, and then truncating th
e
> log. Is this the correct order? I became when I saw an article th
at
> did a backup, then checkpoint and then truncate log - in that condition th
e
> backup would never cover the CHECKPOINTED data.
> Any suggestions would be helpful. Database has full recovery mode.
>|||Executing the CHECKPOINT command will not in any way affect what will be inc
luded on your backups.
(SQL Server will do an internal CHECKPOINT with the backup execution anyhow,
but that is beside the
point.) Backup database will include all the data in a consistent state. Wha
t haven't been committed
will be restored when you do RESTORE DATABASE.
Also, "truncating the log" isn't considered good practice. Either you do reg
ular log backups, and
the log file will be emptied each time you do a log backup. By truncating th
e log, you break that
chain of log backups. Or, you don't do log backups, and all you have to do i
s to have the database
in simple recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MG" <MG@.discussions.microsoft.com> wrote in message
news:94AD791B-2749-4B4E-BCF6-82A32073D8A0@.microsoft.com...
> Tom, Thanks for the info but I am not sure why my concept would not work.
> Simply CHECKPOINT the trans log to get what is left in the trans log to th
e
> mdf, truncate the log, then do a database backup (without backingup the tr
ans
> log). Why would this not work or be affective?
> "Tom Moreau" wrote:
>|||Tibor,
Thanks for clearing that up. I did not know that when a backup occurs that
SQL does a checkpoint. Now it all fits. Is the Checkpoint done with the
database backup or the trans log backup?
"Tibor Karaszi" wrote:

> Executing the CHECKPOINT command will not in any way affect what will be i
ncluded on your backups.
> (SQL Server will do an internal CHECKPOINT with the backup execution anyho
w, but that is beside the
> point.) Backup database will include all the data in a consistent state. W
hat haven't been committed
> will be restored when you do RESTORE DATABASE.
> Also, "truncating the log" isn't considered good practice. Either you do r
egular log backups, and
> the log file will be emptied each time you do a log backup. By truncating
the log, you break that
> chain of log backups. Or, you don't do log backups, and all you have to do
is to have the database
> in simple recovery mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MG" <MG@.discussions.microsoft.com> wrote in message
> news:94AD791B-2749-4B4E-BCF6-82A32073D8A0@.microsoft.com...
>
>|||I know for sure it is done with a database backup. Whether it is performed w
ith a log backup, I
don't know, but to me it doesn't matter as all I need to know is that the re
levant log records are
recorded in the backup media. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MG" <MG@.discussions.microsoft.com> wrote in message
news:6D13A79A-51F0-4C33-95C2-C992B0ADE61E@.microsoft.com...
> Tibor,
> Thanks for clearing that up. I did not know that when a backup occurs that
> SQL does a checkpoint. Now it all fits. Is the Checkpoint done with the
> database backup or the trans log backup?
> "Tibor Karaszi" wrote:
>|||Tibor,
I agree. thanks
"Tibor Karaszi" wrote:

> I know for sure it is done with a database backup. Whether it is performed
with a log backup, I
> don't know, but to me it doesn't matter as all I need to know is that the
relevant log records are
> recorded in the backup media. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MG" <MG@.discussions.microsoft.com> wrote in message
> news:6D13A79A-51F0-4C33-95C2-C992B0ADE61E@.microsoft.com...
>
>sql