Log Backup General question

Do Log Backups read from the data file or log file or both ? I always
thought that log backups read data from log file but as I was thinking, I
believe it reads from the data file... am i right ?A log backup has interaction with both the data file and the log file.
Most of the information stored in the backup file is from the log file but
it can also get data from the data file. For instance if you had some type
of minimally logged load the information in the log file is only which
extents were affected by the load. When the log backup occurs it must read
the entire extent from the database to put into the log backup.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eDOurz%23fDHA.908@.tk2msftngp13.phx.gbl...
> Do Log Backups read from the data file or log file or both ? I always
> thought that log backups read data from log file but as I was thinking, I
> believe it reads from the data file... am i right ?
>|||So does the log backup contain all the extents ? I always had the
misconception that the log file only contain insert/update and delete
statements only and was always wondering what happens when create
tables/create index/Dbcc shrinkfile/Dbcc dbreindex occur and what the Tlog
file contains and also what the log backup would contain. I would appreciate
if you can in brief tell me what occurs.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23IXoo3%23fDHA.2248@.TK2MSFTNGP09.phx.gbl...
> A log backup has interaction with both the data file and the log file.
> Most of the information stored in the backup file is from the log file but
> it can also get data from the data file. For instance if you had some
type
> of minimally logged load the information in the log file is only which
> extents were affected by the load. When the log backup occurs it must
read
> the entire extent from the database to put into the log backup.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eDOurz%23fDHA.908@.tk2msftngp13.phx.gbl...
> > Do Log Backups read from the data file or log file or both ? I always
> > thought that log backups read data from log file but as I was thinking,
I
> > believe it reads from the data file... am i right ?
> >
> >
>|||I don't know the actual format the log uses and quite frankly I don't care
since it does the job. But it will include any DDL and DML that is needed
to correctly redo or roll back the said transaction. In some cases this is
the full DML with the data involved such as an INSERT or UPDATE with all the
values. In the case of a TRUNCATE Table it just logs the Truncate command
as that can be replayed as is. When it comes to Extents this is usually
only logged when there is a Bulklogged operation like I mentioned or
something that happens to affect the entire extent. Otherwise it gets the
data from the DML statement. If you issued an update or Delete statement
that happened to affect all the rows ina page it would still log them on a
row by row basis into the log. Log entries are sequential and it doesn't
know if the actions your taking on a row by row basis will eventually affect
the whole page or even extent. But something like a minimally logged Bulk
Insert is a different story and it will log the extent that has been touched
(not the data). When you back up the log it will then grab that extents
data. As for the other row by row operations, it already has the data it
needs. I suggest (again<g>) that you get a hold of Inside SQL Server 2000.
I am pretty sure it covers most if not all of this.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u2uGT7%23fDHA.3284@.tk2msftngp13.phx.gbl...
> So does the log backup contain all the extents ? I always had the
> misconception that the log file only contain insert/update and delete
> statements only and was always wondering what happens when create
> tables/create index/Dbcc shrinkfile/Dbcc dbreindex occur and what the Tlog
> file contains and also what the log backup would contain. I would
appreciate
> if you can in brief tell me what occurs.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23IXoo3%23fDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > A log backup has interaction with both the data file and the log file.
> > Most of the information stored in the backup file is from the log file
but
> > it can also get data from the data file. For instance if you had some
> type
> > of minimally logged load the information in the log file is only which
> > extents were affected by the load. When the log backup occurs it must
> read
> > the entire extent from the database to put into the log backup.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:eDOurz%23fDHA.908@.tk2msftngp13.phx.gbl...
> > > Do Log Backups read from the data file or log file or both ? I always
> > > thought that log backups read data from log file but as I was
thinking,
> I
> > > believe it reads from the data file... am i right ?
> > >
> > >
> >
> >
>|||> I suggest (again<g>) that you get a hold of Inside SQL Server 2000.
> I am pretty sure it covers most if not all of this.
Or, for gory details about transaction handling, logging, locking etc, "Transaction Processing
Concepts and Techniques" by Gray and Reuter. Some 1000 pages rather heavy reading but not SQL Server
specific, though.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uGRtVREgDHA.556@.TK2MSFTNGP11.phx.gbl...
> I don't know the actual format the log uses and quite frankly I don't care
> since it does the job. But it will include any DDL and DML that is needed
> to correctly redo or roll back the said transaction. In some cases this is
> the full DML with the data involved such as an INSERT or UPDATE with all the
> values. In the case of a TRUNCATE Table it just logs the Truncate command
> as that can be replayed as is. When it comes to Extents this is usually
> only logged when there is a Bulklogged operation like I mentioned or
> something that happens to affect the entire extent. Otherwise it gets the
> data from the DML statement. If you issued an update or Delete statement
> that happened to affect all the rows ina page it would still log them on a
> row by row basis into the log. Log entries are sequential and it doesn't
> know if the actions your taking on a row by row basis will eventually affect
> the whole page or even extent. But something like a minimally logged Bulk
> Insert is a different story and it will log the extent that has been touched
> (not the data). When you back up the log it will then grab that extents
> data. As for the other row by row operations, it already has the data it
> needs. I suggest (again<g>) that you get a hold of Inside SQL Server 2000.
> I am pretty sure it covers most if not all of this.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:u2uGT7%23fDHA.3284@.tk2msftngp13.phx.gbl...
> > So does the log backup contain all the extents ? I always had the
> > misconception that the log file only contain insert/update and delete
> > statements only and was always wondering what happens when create
> > tables/create index/Dbcc shrinkfile/Dbcc dbreindex occur and what the Tlog
> > file contains and also what the log backup would contain. I would
> appreciate
> > if you can in brief tell me what occurs.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%23IXoo3%23fDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > > A log backup has interaction with both the data file and the log file.
> > > Most of the information stored in the backup file is from the log file
> but
> > > it can also get data from the data file. For instance if you had some
> > type
> > > of minimally logged load the information in the log file is only which
> > > extents were affected by the load. When the log backup occurs it must
> > read
> > > the entire extent from the database to put into the log backup.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:eDOurz%23fDHA.908@.tk2msftngp13.phx.gbl...
> > > > Do Log Backups read from the data file or log file or both ? I always
> > > > thought that log backups read data from log file but as I was
> thinking,
> > I
> > > > believe it reads from the data file... am i right ?
> > > >
> > > >
> > >
> > >
> >
> >
>