Incomplete Backups

Question

Post reply

Incomplete Backups

James Mace

Ten Centuries

Points: 1022
More actions
May 21, 2014 at 6:33 am

#286850

I have a SQL Server 2008 and a management plan that backs up all user databases. Recently, the backups are a fraction of what the good backup is. For instance, I have a database that I just backed manually and it is ~1749 kB, but my backup fired from the management plan is ~194 kB. When I look back through the logs plan executed successfully.
Any help would be greatly appreciated.
Jim

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Is the maintenance plan doing compressed backups?

Is the maintenance plan doing differential backups?

Is the maintenance plan striping the backup over multiple files?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

There are two ways:

One you can run RESTORE VERIFYONLY command to check and verify the backup.

Second you can restore the backup in test environment.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

James Mace Ten Centuries Points: 1022 More actions · Answer 3

RESTORE VERIFY FROM DISK = '<backup_file_location>' returns:

The backup set on file 1 is valid.

This is a full backup

We are not backing up to multiple files.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

GilaMonster (5/21/2014)
Is the maintenance plan doing compressed backups?
Is the maintenance plan doing differential backups?
~~Is the maintenance plan striping the backup over multiple files?~~

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

James Mace Ten Centuries Points: 1022 More actions · Answer 5

I really don't know what you mean, so here it is:

ON compression

These are FULL backups

NO we are not backing up to multiple files,

Our database use two files (Data and TransactionLog)

Ken McKelvey SSCoach Points: 18843 More actions · Answer 6

Run:

RESTORE HEADERONLY FROM DISK = '<backup_file_location>';

and see if the backups are compressed.

sql-lover SSCoach Points: 18530 More actions · Answer 7

James Mace (5/21/2014)
I really don't know what you mean, so here it is:
ON compression
These are FULL backups
NO we are not backing up to multiple files,
Our database use two files (Data and TransactionLog)

Have you tried to restore that backup on a Dev environment? If not, do it. Inspect the database and run a DBCC command.

After all, backups are useless if you don't frequently test those with an actual restore.

I do also recommend you to implement Ola's maintenance solution.[/url] It includes backups and provides error log and script capabilities, plus is free!

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 8

Maybe there's been a recent data removal. That would shrink the size of the backups.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

srikantmeister SSChasing Mays Points: 657 More actions · Answer 9

1. check is it really a full backup?

2. check for compression option in maintenance plan

3. check for table data in last in case of nothing solve the purpose(previous backup after restoring on test environment with current table)

James Mace Ten Centuries Points: 1022 More actions · Answer 10

I asked our development team about this issue and they added 2 tables for document management unbeknownst to the DBAs; Communication is key. Thanks for all your help.