Sunday, February 19, 2012

.BAK file double in size

Very strange. My .bak file for a SQL2000 databae has doubled in size. The
database .mdf file has not increased in size. My .bak file is .25g and my
..mdb file is 15g.
The only change I can determine is that a week ago we started a replication
publication on this database. But it is really strange the the .bak file has
grown an not the .mdf file.
Do make sure the file was not corrupt, I did restore it and it restored
okay. I actually restored to a 2005 server (because that is where I had some
room). I restored fine as I said. So then I backed up the database there and
the .bak file is back down to normal size around 11g.
Any ideas. Since we save 4 copies of the .bak on the server it is taking up
a lot of space, but I mainly concerned with the strangeness of it all.
Thanks for your help
Deb
Do not worry about it. The backup file may also have information from the
transaction log. So, if you were able to restore it then it is fine.
Hope this helps,
Ben Nevarez
"DebHerman" wrote:

> Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> database .mdf file has not increased in size. My .bak file is .25g and my
> .mdb file is 15g.
> The only change I can determine is that a week ago we started a replication
> publication on this database. But it is really strange the the .bak file has
> grown an not the .mdf file.
> Do make sure the file was not corrupt, I did restore it and it restored
> okay. I actually restored to a 2005 server (because that is where I had some
> room). I restored fine as I said. So then I backed up the database there and
> the .bak file is back down to normal size around 11g.
> Any ideas. Since we save 4 copies of the .bak on the server it is taking up
> a lot of space, but I mainly concerned with the strangeness of it all.
> Thanks for your help
> Deb
|||Did you back up to the same file without using INIT? This will append a
second backup to the original backup file. As Erland posted in another
thread today, you can check how many backups are in the BAK file by RESTORE
... HEADERONLY and simply counting the rows.
"DebHerman" <DebHerman@.discussions.microsoft.com> wrote in message
news:C84C48BD-E6DE-4736-B12B-0F5E2DCC12B3@.microsoft.com...
> Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> database .mdf file has not increased in size. My .bak file is .25g and my
> .mdb file is 15g.
> The only change I can determine is that a week ago we started a
> replication
> publication on this database. But it is really strange the the .bak file
> has
> grown an not the .mdf file.
> Do make sure the file was not corrupt, I did restore it and it restored
> okay. I actually restored to a 2005 server (because that is where I had
> some
> room). I restored fine as I said. So then I backed up the database there
> and
> the .bak file is back down to normal size around 11g.
> Any ideas. Since we save 4 copies of the .bak on the server it is taking
> up
> a lot of space, but I mainly concerned with the strangeness of it all.
> Thanks for your help
> Deb
|||Can you tell me the scenarion where it would have that much information from
the transaction log?
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Do not worry about it. The backup file may also have information from the
> transaction log. So, if you were able to restore it then it is fine.
> Hope this helps,
> Ben Nevarez
>
>
> "DebHerman" wrote:
|||From 'Full Database Backups' on BOL.
"A full database backup backs up the whole database. This includes part of
the transaction log so that the full database backup can be recovered. Full
database backups represent the database at the time the backup finished."
"Enough of the transaction log is included in the backup to let you recover
the database to the time when the backup finished."
Hope this helps,
Ben Nevarez
"DebHerman" wrote:
[vbcol=seagreen]
> Can you tell me the scenarion where it would have that much information from
> the transaction log?
> "Ben Nevarez" wrote:
|||I understand that transactions that happen during the back up are stored in
the back up file, but nothing "should" be happening at the time of the
backup. The transaction log file is actually backed up just prior to the full
database back up and it is really small.
But my suspicion is that a snapshot is running at the same time as the back
up since we just started replication. Would a snapshot create transactions.
If so this is probably my problem. I adjusted the timing last night but not
quite enough. The snapshot didn't quite finish before the backup up of the
transaction log. But it did finish before the backup of the database.
Confusing. I'm not sure a full snapshot should be running each night but
that is a whole other discussion.
I need to determine what is causing this. We store 4 days worth .bak on the
server and these are backed up to tape each night, so 4 12g is 48g a space
that makes the network backup people cranky.<g>
Thanks for any help?
"Ben Nevarez" wrote:
[vbcol=seagreen]
> From 'Full Database Backups' on BOL.
> "A full database backup backs up the whole database. This includes part of
> the transaction log so that the full database backup can be recovered. Full
> database backups represent the database at the time the backup finished."
> "Enough of the transaction log is included in the backup to let you recover
> the database to the time when the backup finished."
> Hope this helps,
> Ben Nevarez
>
>
> "DebHerman" wrote:

No comments:

Post a Comment