How to determine SQL Server DB backup file size?

[sqlguy1]

Is there a way to know how much disk space a SQL Server 2008 database backup file (*.bak) should take up? I am running the backup database command via sqlcmd to backup a DB. However, it seems the *.bak file is getting very bloated. The backup gets run every day. Could the backup be appending the DB backup data to *.bak file that was originally in the \Microsoft SQL Server\...\backup folder? It should be overwriting it each time, but I don't think the file should be growing the way it is. Any thoughts? Thanks.

 

[SQLBill]

Yes, it could be appending. Can you provide the backup command you are running? Your backup statement should be using WITH INIT.

Also, is the database size growing by a large amount?

Is the database in Full Recovery mode? If so, are you doing transaction log backups?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[sqlguy1]

Here is the command I am using:

sqlcmd -S.\JWSAPEX -U jwsapex -P jwsapex -Q"backup database JwsData%4 to disk = 'JwsData%4.bak'"
sqlcmd -S.\JWSAPEX -U jwsapex -P jwsapex -Q"backup database JwsAudit%4 to disk = 'JwsAudit%4.bak'"
sqlcmd -S.\JWSAPEX -U jwsapex -P jwsapex -Q"backup database JwsArchive%4 to disk = 'JwsArchive%4.bak'"

In some cases I have added the protocol used in front of the command like this:

sqlcmd -S tcp:.\JWSAPEX ...

Note the %4 I am using here is part of a parameter that uis being passed. I am using these commands in a batch file. The backup is growing significantly enough that it is filling an 8 GB USB drive in some cases. It should be nowhere near that size. At most the JWSData*.bak should only need to backup probably 2 GB at most. the other 2 should be smaller than that.

Any help would be appreciated. Thanks.

 

[SQLBill]

Try changing the commands like this:

sqlcmd -S.\JWSAPEX -U jwsapex -P jwsapex -Q"backup database JwsData%4 to disk = 'JwsData%4.bak' WITH INIT"

Also, you didn't answer my other questions, so here they are again:

Is the database size growing by a large amount?

Is the database in Full Recovery mode? If so, are you doing transaction log backups?

SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875