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.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to determine SQL Server DB backup file size?
- Thread starter sqlguy1
- Start date
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
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
- Thread starter
- #3
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.
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.
Try changing the commands like this:
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
Code:
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
- Status
Similar threads
- Question
- Locked
- Question
