One db backup not working correctly - others are fine

[swhitten]

My client has a SQL server with several apps running on it. The backup is not working properly for only one of the DBs on the server; all the others seem to be fine.

The user reports that when he backs up the trouble DB on the server, it generates a very small .bak file that does not contain all the info. This is true whether he manually kicks off the back up or runs it through a maintenance plan / job.

When he backs it up from a client machine somewhere else on the network, the backup seems to run fine.

Any ideas?

Thanks!

 

[swhitten]

Update . . . The backup creates the same sized bak file, regardless of whether you back it up from the server or a client. The db is about 16 gb; the resulting backup is about 65 mb. Is that a reasonabl backup file size for a 16 gb db?

The database is a Goldmine database that stores all the values for all the field labels. The user has historically backed it up on the server, then copied the backup file over to a laptop when creating a new install of Goldmine on the laptop. then he restores the backup on the laptop and sets up synching. In theory, when sync'd, the gmbase database should be exactly the same on the server as it is on the laptop. He recently followed his procedure, but when he restored the server backup of gmbase on the laptop, not all the data was there. He sync'd, and all the data came in. He says when you create a backup of the gmbase db on the laptop, it creates a 249 mb bak. So the laptop backup is considerably larger than the server backup.

We know all the data is in the gmbase db on the server, because users who run directly off the server rather than off a sync'ed laptop are not experiencing any problems.

But it sounds like there may be a problem with the backup of the gmbase db on the server.

Any ideas? What would cause a backup to only backup part of its data?

 

[mrdenny]

There is no way to backup only part of a database unless there are several file groups and the backup command is only backing up one file group.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Have you tried restoring the backup?

Do that on a test server to see if it is corrupted. It could be that the database is only as large as it is because it has lots of free space in it and has never been shrunk. Or, it could be that the drive(s) you're trying to backup to have some sort of file corruption.

If the backup is corrupted, try backing up to different drives to see if it's a drive issue. Also run some DBCC commands to make sure the database itself isn't highly fragmented or has corrupted pages/files.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"