Backup Size - Larger than db size

I have a database which I am backing up nightly ( after a log truncate, dbcc
checkdb ) and the .bak size is a lot larger than the actual database.
I am looking at the table space, and I think it might be the reserved space
also being backed up, I thought that only actual data was backed up.
Any pointers would be appreciated. Any more info require pls let me know.Are you perhaps by mistake doing append to the backup device? You can see wh
at backups are on a
backup device using RESTORE HEADERONLY.
Backup will copy all extents which are in use, so even if only one page are
used on an extent, the
whole extent will be included in the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message news:eSbUf.14129$814.11725@.newsfe5-win.ntl
i.net...
>I have a database which I am backing up nightly ( after a log truncate, dbc
c checkdb ) and the .bak
>size is a lot larger than the actual database.
> I am looking at the table space, and I think it might be the reserved spac
e also being backed up,
> I thought that only actual data was backed up.
> Any pointers would be appreciated. Any more info require pls let me know.
>
>

Backup Size - Larger than db size

I have a database which I am backing up nightly ( after a log truncate, dbcc
checkdb ) and the .bak size is a lot larger than the actual database.
I am looking at the table space, and I think it might be the reserved space
also being backed up, I thought that only actual data was backed up.
Any pointers would be appreciated. Any more info require pls let me know.Are you perhaps by mistake doing append to the backup device? You can see what backups are on a
backup device using RESTORE HEADERONLY.
Backup will copy all extents which are in use, so even if only one page are used on an extent, the
whole extent will be included in the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message news:eSbUf.14129$814.11725@.newsfe5-win.ntli.net...
>I have a database which I am backing up nightly ( after a log truncate, dbcc checkdb ) and the .bak
>size is a lot larger than the actual database.
> I am looking at the table space, and I think it might be the reserved space also being backed up,
> I thought that only actual data was backed up.
> Any pointers would be appreciated. Any more info require pls let me know.
>
>

Backup Size - Larger than db size

I have a database which I am backing up nightly ( after a log truncate, dbcc
checkdb ) and the .bak size is a lot larger than the actual database.
I am looking at the table space, and I think it might be the reserved space
also being backed up, I thought that only actual data was backed up.
Any pointers would be appreciated. Any more info require pls let me know.
Are you perhaps by mistake doing append to the backup device? You can see what backups are on a
backup device using RESTORE HEADERONLY.
Backup will copy all extents which are in use, so even if only one page are used on an extent, the
whole extent will be included in the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message news:eSbUf.14129$814.11725@.newsfe5-win.ntli.net...
>I have a database which I am backing up nightly ( after a log truncate, dbcc checkdb ) and the .bak
>size is a lot larger than the actual database.
> I am looking at the table space, and I think it might be the reserved space also being backed up,
> I thought that only actual data was backed up.
> Any pointers would be appreciated. Any more info require pls let me know.
>
>
sql

Backup log With Truncate Only (Log Shipping)

SQL Server 2000 Standard Edition running on Win2k
I've setup log shipping according to an article I found here.
http://www.sql-server-performance.c...og_shipping.asp
Which truncates the log before doing the database backup then does the backu
p
and
subsequent log backups without truncating. Of course the truncate causes an
error
to be shown in the event viewer.
Since books describes the default behavior of log backups to be an automatic
truncate
after backup my initial thought is to drop the log backup before the db back
up
and just let
the truncate happen automatically with the normal log shipping backups.
Remove This -- BACKUP LOG database_name WITH TRUNCATE_ONLY
Remove This -- WAITFOR DELAY '00:00:05'
BACKUP DATABASE database_name TO database_name_backup_device WITH INIT
Remove the NO_TRUNCATE and let the log truncate automatically.
BACKUP LOG database_name TO log_backup_device WITH INIT, NO_TRUNCATE
They do mention in the article that you don't want to truncate the log after
you do
your normal log shipping backups because you might need them later. Would yo
u
possibly
need them later? I archive all my log and db backups each time they run.Brad <seveni7@.yahoo.com> wrote:

quote:

---

>SQL Server 2000 Standard Edition running on Win2k
>I've setup log shipping according to an article I found here.
>http://www.sql-server-performance.c...og_shipping.asp
>Which truncates the log before doing the database backup then does the back
up
>and
>subsequent log backups without truncating. Of course the truncate causes an
>error
>to be shown in the event viewer.
>Since books describes the default behavior of log backups to be an automati
c
>truncate
>after backup my initial thought is to drop the log backup before the db bac
kup
>and just let
>the truncate happen automatically with the normal log shipping backups.
>

---

My bad!
Found it on MS Knowledge Base Article - 818202
Just a warning.

Backup log With Truncate Only (Log Shipping)

SQL Server 2000 Standard Edition running on Win2k
I've setup log shipping according to an article I found here.
http://www.sql-server-performance.com/sql_server_log_shipping.asp
Which truncates the log before doing the database backup then does the backup
and
subsequent log backups without truncating. Of course the truncate causes an
error
to be shown in the event viewer.
Since books describes the default behavior of log backups to be an automatic
truncate
after backup my initial thought is to drop the log backup before the db backup
and just let
the truncate happen automatically with the normal log shipping backups.
Remove This -- BACKUP LOG database_name WITH TRUNCATE_ONLY
Remove This -- WAITFOR DELAY '00:00:05'
BACKUP DATABASE database_name TO database_name_backup_device WITH INIT
Remove the NO_TRUNCATE and let the log truncate automatically.
BACKUP LOG database_name TO log_backup_device WITH INIT, NO_TRUNCATE
They do mention in the article that you don't want to truncate the log after
you do
your normal log shipping backups because you might need them later. Would you
possibly
need them later? I archive all my log and db backups each time they run.Brad <seveni7@.yahoo.com> wrote:
>SQL Server 2000 Standard Edition running on Win2k
>I've setup log shipping according to an article I found here.
>http://www.sql-server-performance.com/sql_server_log_shipping.asp
>Which truncates the log before doing the database backup then does the backup
>and
>subsequent log backups without truncating. Of course the truncate causes an
>error
>to be shown in the event viewer.
>Since books describes the default behavior of log backups to be an automatic
>truncate
>after backup my initial thought is to drop the log backup before the db backup
>and just let
>the truncate happen automatically with the normal log shipping backups.
>
My bad!
Found it on MS Knowledge Base Article - 818202
Just a warning.

Backup log - will it truncate?

According to BOL
<--
BACKUP LOG
Specifies a backup of the transaction log only. The log is backed up
from the last successfully executed LOG backup to the current end of
the log. Once the log is backed up, the space may be truncated when no
longer required by replication or active transactions.
--
Does this mean I could do something else to truncate the log or is it
saying that the backup log command may truncate the log if it feels
like it?

I want ot get a log from a client site onto my server for analysis but
I want ot make absolute certain that my backing up the log on their
server won't truncate it there.Trevor

A backup of the transaction log will truncate the transaction log. All
completed transactions will be removed.

What are you planing to do with the transaction log? There is not a lot
you can do with out a matching database. As far as I am aware,
Lumigent's Log reader is the only product that can read the transaction
log.

Regards

John

Trevor Best wrote:
> According to BOL
> <--
> BACKUP LOG
> Specifies a backup of the transaction log only. The log is backed up
> from the last successfully executed LOG backup to the current end of
> the log. Once the log is backed up, the space may be truncated when no
> longer required by replication or active transactions.
> -->
> Does this mean I could do something else to truncate the log or is it
> saying that the backup log command may truncate the log if it feels
> like it?
> I want ot get a log from a client site onto my server for analysis but
> I want ot make absolute certain that my backing up the log on their
> server won't truncate it there.|||johnbandettini@.yahoo.co.uk wrote:
> Trevor
> A backup of the transaction log will truncate the transaction log. All
> completed transactions will be removed.
> What are you planing to do with the transaction log? There is not a lot
> you can do with out a matching database. As far as I am aware,
> Lumigent's Log reader is the only product that can read the transaction
> log.

Hi John, thanks for the reply.

I do intend to view the log, I'm using SQLLog Rescue from
www.red-gate.com. I can connect to their server over a VPN but it took
hours to read the log and got a general network error during the
process. What I want to do is restore the database on my server c/w
logs so I can view them here.

I know I can grab the data files and attach them on my end but I would
have to wait until tonight when people are off line to free up the
files.|||On 9 Jun 2006 02:35:01 -0700, Trevor Best wrote:

(snip)
>I want ot get a log from a client site onto my server for analysis but
>I want ot make absolute certain that my backing up the log on their
>server won't truncate it there.

Hi Trevor,

BACKUP LOG <databasename>
TO DISK = 'x:\y\z\logbackup.bak'
WITH COPY_ONLY;

--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:5hmi82pfl02iqnhelegm0v515dbdm1qpid@.4ax.com...
> On 9 Jun 2006 02:35:01 -0700, Trevor Best wrote:
> (snip)
> >I want ot get a log from a client site onto my server for analysis but
> >I want ot make absolute certain that my backing up the log on their
> >server won't truncate it there.
> Hi Trevor,
> BACKUP LOG <databasename>
> TO DISK = 'x:\y\z\logbackup.bak'
> WITH COPY_ONLY;

Is this a SQL 2005 specific command? I've never seen it before.

Very handy.

> --
> Hugo Kornelis, SQL Server MVP|||Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:5hmi82pfl02iqnhelegm0v515dbdm1qpid@.4ax.com...
>> BACKUP LOG <databasename>
>> TO DISK = 'x:\y\z\logbackup.bak'
>> WITH COPY_ONLY;
> Is this a SQL 2005 specific command? I've never seen it before.

Yes, that's a new addition (which I neither I had noticed until Hugo
posted about it). It appears to be a "NO_TRUNCATE light". That is,
NO_TRUNCATE also waives the requirement that the database should be
accessible etc and is mainly intended for emergency situations.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Sat, 10 Jun 2006 12:51:23 +0000 (UTC), Erland Sommarskog wrote:

>Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
>> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
>> news:5hmi82pfl02iqnhelegm0v515dbdm1qpid@.4ax.com...
>>> BACKUP LOG <databasename>
>>> TO DISK = 'x:\y\z\logbackup.bak'
>>> WITH COPY_ONLY;
>>
>> Is this a SQL 2005 specific command? I've never seen it before.
>Yes, that's a new addition (which I neither I had noticed until Hugo
>posted about it). It appears to be a "NO_TRUNCATE light". That is,
>NO_TRUNCATE also waives the requirement that the database should be
>accessible etc and is mainly intended for emergency situations.

Hi Erland,

That's right. The major advantage of COPY_ONLY over NO_TRUNCATE is (IMO)
that this option also works on full and incremental backups. It's a
great way to quickly get a backup of a DB to do some tests or
trouble-shooting on without disrupting the backup schema.

--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:9juo821f71b7ah782onh5lbotre5qrgie1@.4ax.com...
> On Sat, 10 Jun 2006 12:51:23 +0000 (UTC), Erland Sommarskog wrote:
> >Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> >> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> >> news:5hmi82pfl02iqnhelegm0v515dbdm1qpid@.4ax.com...
> >>> BACKUP LOG <databasename>
> >>> TO DISK = 'x:\y\z\logbackup.bak'
> >>> WITH COPY_ONLY;
> >>
> >> Is this a SQL 2005 specific command? I've never seen it before.
> >Yes, that's a new addition (which I neither I had noticed until Hugo
> >posted about it). It appears to be a "NO_TRUNCATE light". That is,
> >NO_TRUNCATE also waives the requirement that the database should be
> >accessible etc and is mainly intended for emergency situations.
> Hi Erland,
> That's right. The major advantage of COPY_ONLY over NO_TRUNCATE is (IMO)
> that this option also works on full and incremental backups. It's a
> great way to quickly get a backup of a DB to do some tests or
> trouble-shooting on without disrupting the backup schema.

Yeah, that's why I was thinking it would be so useful.

I'll have to keep it in mind for when we upgrade to SQL 2005.

(Still have to have THAT particular debate with the finance folks who pay
the bills. :-)

> --
> Hugo Kornelis, SQL Server MVP