Database backup question

Hi
This might be a very simple and stupid question, so please bear with
me...:-).
Will the Logfile of a database be truncated when I just run a "BACKUP
Database xx to xx" statement?
BOL clearly says what's happening if I just backup the log, but it's not
clear when I do full database backup (..or maybe it's just me that doesn't
see it...).
Regards
Steen
No, the log is not emptied when you do backup database. That would break the sequence of log backups, meaning
that you could not recover from a missing database backup file (doing the one before that and all subsequent
log backups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:%23RM4bqiSEHA.3608@.TK2MSFTNGP11.phx.gbl...
> Hi
> This might be a very simple and stupid question, so please bear with
> me...:-).
> Will the Logfile of a database be truncated when I just run a "BACKUP
> Database xx to xx" statement?
> BOL clearly says what's happening if I just backup the log, but it's not
> clear when I do full database backup (..or maybe it's just me that doesn't
> see it...).
> Regards
> Steen
>
|||In addition to Tibor's comments, ONLY a log backup truncates the log... Log
truncation means that the inactive part of the is cleaned up and the disk
space can be re-used.. Neither database nor differential backups truncate
the log...
Also log truncation does NOT mean that the physical file gets any
smaller... To reduce the size of the log file one would use dbcc
shrinkfile.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23RM4bqiSEHA.3608@.TK2MSFTNGP11.phx.gbl...
> Hi
> This might be a very simple and stupid question, so please bear with
> me...:-).
> Will the Logfile of a database be truncated when I just run a "BACKUP
> Database xx to xx" statement?
> BOL clearly says what's happening if I just backup the log, but it's not
> clear when I do full database backup (..or maybe it's just me that doesn't
> see it...).
> Regards
> Steen
>
|||Thanks Tibor...
I didn't really thought about the case that the database file could be
missing...:-).
I was actually just thinking that when I do the database backup, I have the
full backup at that point - and therefore doesn't need anything from the
log.
Our current setup is that the database is being backed up every night. After
the database backup is done, the log is being truncated. I was just
wondering if the truncate log job was needed or if it was a part of the
database bakup.
Regards
Steen
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:uTLbQ4iSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> No, the log is not emptied when you do backup database. That would break
the sequence of log backups, meaning
> that you could not recover from a missing database backup file (doing the
one before that and all subsequent
> log backups).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23RM4bqiSEHA.3608@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
doesn't
>
|||Steen,
Note that I was referring to a database *backup* file is missing. Example:
Backup Db (1)
Backup Log (2)
Backup Log (3)
Backup Log (4)
Backup Db (5)
Backup Log (6)
Backup Log (7)
You now want to do restore, but realize that (5) is corrupt. If backup database did indeed truncate the tlog
you could at most do: 1,2,3,4. but, because backup db doesn't truncate the log, you can do 1,2,3,4,6,7.
If you are saying that you do something like BACKUP LOG WITH TRUNCATE ONLY or NO_LOG after the db backup, you
are in the same boat, you can only get as far as (4). I suggest that if you do log backups, don't truncate it
explicitly using NO_LOG or TRUNCATE_ONLY. If you don't do log backups, just set the database to simple
recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:OWfieEjSEHA.3552@.TK2MSFTNGP09.phx.gbl...
> Thanks Tibor...
> I didn't really thought about the case that the database file could be
> missing...:-).
> I was actually just thinking that when I do the database backup, I have the
> full backup at that point - and therefore doesn't need anything from the
> log.
> Our current setup is that the database is being backed up every night. After
> the database backup is done, the log is being truncated. I was just
> wondering if the truncate log job was needed or if it was a part of the
> database bakup.
>
> Regards
> Steen
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
> en meddelelse news:uTLbQ4iSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> the sequence of log backups, meaning
> one before that and all subsequent
> news:%23RM4bqiSEHA.3608@.TK2MSFTNGP11.phx.gbl...
> doesn't
>
|||Hi Wayne
Thanks for your comments. I was aware of those things - I just wasn't sure
if the backup database command alone would truncate the log.
Regards
Steen
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> skrev i en meddelelse
news:OM$e%239iSEHA.1448@.TK2MSFTNGP09.phx.gbl...
> In addition to Tibor's comments, ONLY a log backup truncates the log...
Log[vbcol=seagreen]
> truncation means that the inactive part of the is cleaned up and the disk
> space can be re-used.. Neither database nor differential backups truncate
> the log...
> Also log truncation does NOT mean that the physical file gets any
> smaller... To reduce the size of the log file one would use dbcc
> shrinkfile.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:%23RM4bqiSEHA.3608@.TK2MSFTNGP11.phx.gbl...
doesn't
>
|||Hi Tibor
I agree in what you are saying - that's also my understanding of the ideal
backup scenario. I'm just trying so find out if what we are doing isn't good
enough.
We are running a "BACKUP DATABASE" every night. To my understanding, that's
a full database backup which means that if something fails, I can restore
that backup and we ar back to the point when the backup is done. Apparently
that's good enough for the company so far.
In this scenario I'd think that it's ok to truncate the log just after the
backup is done. If something goes wrong during the day, we can restore the
backup from last night and then recover up to the point of failure by using
the existing log. Will this proceudre be good enough, or is it better to
backup the log e.g. every 2 hours or so? (of course if we don't pay
attention to downtime and time to recover etc.).
I have to say that I'm new to the company, so the current backup procedures
has been set up by others. I haven't worked that much with the backup part
of SQL server in the past, so that might be why my questions are a bit
stupid.
Regards
Steen
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:eR6ikNjSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> Steen,
> Note that I was referring to a database *backup* file is missing. Example:
> Backup Db (1)
> Backup Log (2)
> Backup Log (3)
> Backup Log (4)
> Backup Db (5)
> Backup Log (6)
> Backup Log (7)
> You now want to do restore, but realize that (5) is corrupt. If backup
database did indeed truncate the tlog
> you could at most do: 1,2,3,4. but, because backup db doesn't truncate the
log, you can do 1,2,3,4,6,7.
> If you are saying that you do something like BACKUP LOG WITH TRUNCATE ONLY
or NO_LOG after the db backup, you
> are in the same boat, you can only get as far as (4). I suggest that if
you do log backups, don't truncate it
> explicitly using NO_LOG or TRUNCATE_ONLY. If you don't do log backups,
just set the database to simple
> recovery mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:OWfieEjSEHA.3552@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
the[vbcol=seagreen]
After[vbcol=seagreen]
i[vbcol=seagreen]
break[vbcol=seagreen]
the[vbcol=seagreen]
"BACKUP[vbcol=seagreen]
not
>
|||Steen,

> We are running a "BACKUP DATABASE" every night. To my understanding, that's
> a full database backup which means that if something fails, I can restore
> that backup and we ar back to the point when the backup is done.
Yep. And if this is all you need, just set the database to simple recovery mode, and the log will be emptied
automatically.

> In this scenario I'd think that it's ok to truncate the log just after the
> backup is done. If something goes wrong during the day, we can restore the
> backup from last night and then recover up to the point of failure by using
> the existing log.
No. You will not be able to "re-connect" the log to the database backup, as you truncated the log after the
database backup. You will not have an unbroken chain of log records.

> I have to say that I'm new to the company, so the current backup procedures
> has been set up by others. I haven't worked that much with the backup part
> of SQL server in the past, so that might be why my questions are a bit
> stupid.
Not at all. Better to ask than be sorry when that smelly thing hits the fan. I recommend that you do db backup
every night and log backup every hour. This is my "standard" setup, and possibly modified depending on my
customers requirements.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:%23vstWfjSEHA.2236@.TK2MSFTNGP09.phx.gbl...
> Hi Tibor
> I agree in what you are saying - that's also my understanding of the ideal
> backup scenario. I'm just trying so find out if what we are doing isn't good
> enough.
> We are running a "BACKUP DATABASE" every night. To my understanding, that's
> a full database backup which means that if something fails, I can restore
> that backup and we ar back to the point when the backup is done. Apparently
> that's good enough for the company so far.
> In this scenario I'd think that it's ok to truncate the log just after the
> backup is done. If something goes wrong during the day, we can restore the
> backup from last night and then recover up to the point of failure by using
> the existing log. Will this proceudre be good enough, or is it better to
> backup the log e.g. every 2 hours or so? (of course if we don't pay
> attention to downtime and time to recover etc.).
> I have to say that I'm new to the company, so the current backup procedures
> has been set up by others. I haven't worked that much with the backup part
> of SQL server in the past, so that might be why my questions are a bit
> stupid.
> Regards
> Steen
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
> en meddelelse news:eR6ikNjSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> database did indeed truncate the tlog
> log, you can do 1,2,3,4,6,7.
> or NO_LOG after the db backup, you
> you do log backups, don't truncate it
> just set the database to simple
> news:OWfieEjSEHA.3552@.TK2MSFTNGP09.phx.gbl...
> the
> After
> i
> break
> the
> "BACKUP
> not
>
|||Thanks a lot for the advises....
If I'd stick to the current scenario and only backup the database, would it
then the better to truncate the log just before the backup? In that case, I
assume I'd have an unbroken log sequence to use if something fails after the
backup. Otherwise, when (or how) will it be possible to truncate the log in
this sceanrio?
If I set up the backup to backup of the log on an hourly basis, how do you
guys then recommend to set it up? Do you create backupfiles/devices for each
time the backup is ran and then let it overwrite it self the next day at the
same time? I'm thinking how I can set it up so I have all the log files
available as long as they might be needed, and then being
deleted/overwritten automatically.
Could it be a solution to use "expire date" for the backup and then let them
be overwritten when they are expired?
Regards
Steen
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:%23JGgjXkSEHA.3844@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Steen,
that's[vbcol=seagreen]
restore
> Yep. And if this is all you need, just set the database to simple recovery
mode, and the log will be emptied[vbcol=seagreen]
> automatically.
>
the[vbcol=seagreen]
the[vbcol=seagreen]
using
> No. You will not be able to "re-connect" the log to the database backup,
as you truncated the log after the[vbcol=seagreen]
> database backup. You will not have an unbroken chain of log records.
>
procedures[vbcol=seagreen]
part
> Not at all. Better to ask than be sorry when that smelly thing hits the
fan. I recommend that you do db backup
> every night and log backup every hour. This is my "standard" setup, and
possibly modified depending on my
> customers requirements.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23vstWfjSEHA.2236@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
ideal[vbcol=seagreen]
good[vbcol=seagreen]
that's[vbcol=seagreen]
restore[vbcol=seagreen]
Apparently[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
using[vbcol=seagreen]
procedures[vbcol=seagreen]
part[vbcol=seagreen]
i[vbcol=seagreen]
Example:[vbcol=seagreen]
the[vbcol=seagreen]
ONLY[vbcol=seagreen]
if[vbcol=seagreen]
be[vbcol=seagreen]
have[vbcol=seagreen]
the[vbcol=seagreen]
night.[vbcol=seagreen]
the[vbcol=seagreen]
skrev[vbcol=seagreen]
(doing[vbcol=seagreen]
with[vbcol=seagreen]
it's[vbcol=seagreen]
that
>
|||> If I'd stick to the current scenario and only backup the database,
The question here is: Why! Why wouldn't you backup the transaction log as well. I never set up a backup
strategy where I do db backup, db is in full recovery and I don't do log backup, expecting to "grab" the log
in case anything happens. It is way to easy to also do log backup and you have your safety markers every n
minutes/hours as well as "grabbing the active log".

> If I'd stick to the current scenario and only backup the database, would it
> then the better to truncate the log just before the backup? In that case, I
> assume I'd have an unbroken log sequence to use if something fails after the
> backup.
Assuming now that you are allergic to doing log backups, and don't want to db in simple recovery mode: Yes.
When something fails, do a log backup, restore the db backup and then restore the log backup you just took
(possibly using the STOPAT parameter). I still do not recommend this strategy, though.

> If I set up the backup to backup of the log on an hourly basis, how do you
> guys then recommend to set it up? Do you create backupfiles/devices for each
> time the backup is ran and then let it overwrite it self the next day at the
> same time? I'm thinking how I can set it up so I have all the log files
> available as long as they might be needed, and then being
> deleted/overwritten automatically.
Being one of the developers of Db Maint, I use Db Maint for this (www.dbmaint.com). In version 4, we decided
to create a new backup device each time a backup is to be performed, name the backup device with db name, type
of backup and timestamp. This way, we never do append. And each time we run, we delete old backup devices,
based on user settings.
You can achieve the same using TSQL code. Or use Maint Wizard. Etc...

> Could it be a solution to use "expire date" for the backup and then let them
> be overwritten when they are expired?
No, Expire date doesn't work that way. Read about and test it to understand what it does, but it won't help
you here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:%237lRM7FTEHA.332@.TK2MSFTNGP11.phx.gbl...
> Thanks a lot for the advises....
> If I'd stick to the current scenario and only backup the database, would it
> then the better to truncate the log just before the backup? In that case, I
> assume I'd have an unbroken log sequence to use if something fails after the
> backup. Otherwise, when (or how) will it be possible to truncate the log in
> this sceanrio?
> If I set up the backup to backup of the log on an hourly basis, how do you
> guys then recommend to set it up? Do you create backupfiles/devices for each
> time the backup is ran and then let it overwrite it self the next day at the
> same time? I'm thinking how I can set it up so I have all the log files
> available as long as they might be needed, and then being
> deleted/overwritten automatically.
> Could it be a solution to use "expire date" for the backup and then let them
> be overwritten when they are expired?
> Regards
> Steen
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
> en meddelelse news:%23JGgjXkSEHA.3844@.TK2MSFTNGP11.phx.gbl...
> that's
> restore
> mode, and the log will be emptied
> the
> the
> using
> as you truncated the log after the
> procedures
> part
> fan. I recommend that you do db backup
> possibly modified depending on my
> news:%23vstWfjSEHA.2236@.TK2MSFTNGP09.phx.gbl...
> ideal
> good
> that's
> restore
> Apparently
> the
> the
> using
> procedures
> part
> i
> Example:
> the
> ONLY
> if
> be
> have
> the
> night.
> the
> skrev
> (doing
> with
> it's
> that
>