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
SteenNo, 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...
> > 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
> >
> >
>|||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...
> > 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
> > >
> > >
> >
> >
>|||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
> 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
> >
> >
>|||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...
> > 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...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>|||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...
> > 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...
> > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||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...
> 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...
> > > 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...
> > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> 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...
> > 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...
> > > > 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...
> > > > > > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi
It's not because I'm allergic to do log backups - I just want to be sure
that I understand the different scenarios...:-). Now when you have helped me
a big step forward in backup knowledge, I'm quite sure I'll set it up as you
suggest. I just don't want to set up a backup strategy just because someone
has said "that's the best way" - I prefer to understand how it's working so
I based on various inputs can set up the strategy that I feel is the best
for my purpose.
I really appreciate your inputs and answers - it has really helped me a lot
and I now feel much more capable of designing a usefull backup plan.
I'll try to read a bit more about "expire date" to get some knowledge about
what it's usefull for.
Regards
Steen
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:%23iUsbbHTEHA.2908@.TK2MSFTNGP10.phx.gbl...
> > 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...
> > > 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...
> > > > > 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...
> > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||That mindset will hopefully lead you to a good implementation, Steen. And, if you still run into something you
don't think makes sense, you can always post here... :-)
Good luck.
--
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:%231l6t2HTEHA.3768@.TK2MSFTNGP11.phx.gbl...
> Hi
> It's not because I'm allergic to do log backups - I just want to be sure
> that I understand the different scenarios...:-). Now when you have helped me
> a big step forward in backup knowledge, I'm quite sure I'll set it up as you
> suggest. I just don't want to set up a backup strategy just because someone
> has said "that's the best way" - I prefer to understand how it's working so
> I based on various inputs can set up the strategy that I feel is the best
> for my purpose.
> I really appreciate your inputs and answers - it has really helped me a lot
> and I now feel much more capable of designing a usefull backup plan.
> I'll try to read a bit more about "expire date" to get some knowledge about
> what it's usefull for.
> Regards
> Steen
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
> en meddelelse news:%23iUsbbHTEHA.2908@.TK2MSFTNGP10.phx.gbl...
> > > 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...
> > > > 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...
> > > > > > 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...
> > > > > > > > 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
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>sql