August 6, 2009 at 1:35 pm
Hi,
I have a full back setup for each DB on my SQL which runs on a daily basis every night. As I was checking today I noticed that the last backup done was on 8/1/09 which is weird since I havent changed any settings. so I went to do manual backup first for each DB since it hasnt backed up anything for the past 5 days and as I was doing that i picked our first database which is about 5gb and the whole thing took 5 seconds which usually takes about 2 minutes for that big file and I got the msg at the end that the backup was succesfull which I knew wasnt correct at all, so I went to check the backup folder and look at the file to see whats going on and the DB was chopped from 5gb to 500 mb or so where the backup is usually around 5gb the normal size . I tried the same thing on smaller DB's that are around 500mb each and same thing where the progess bar didnt even show up and boom backup succesfull and same thing , 500mb turned into 150mb or so .. whats going on here ? help . Thanks
lee
August 6, 2009 at 2:18 pm
A couple of things to check:
1) Did you perform a full backup, or was it a transaction log or differential backup? Either of those will be a lot smaller than a full backup.
2) Did someone recently purge a lot of data from this system? Having less data in the database would account for much small backups also.
Now, what I would do is find out why the backups stopped. Was there an error? Or did someone disable the jobs? Or something else? You need to identify why the backups stopped so you can make sure the problem is corrected.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 6, 2009 at 2:43 pm
Jeffrey Williams (8/6/2009)
A couple of things to check:1) Did you perform a full backup, or was it a transaction log or differential backup? Either of those will be a lot smaller than a full backup.
Answer: Its a Full complete back that overwrites the original backup files .. I've had it setup like this for the past 2 years and no problems at all .
2) Did someone recently purge a lot of data from this system? Having less data in the database would account for much small backups also.
Answer: No data purged , Our DB grows daily we never purge anything.
Now, what I would do is find out why the backups stopped. Was there an error? Or did someone disable the jobs? Or something else? You need to identify why the backups stopped so you can make sure the problem is corrected.
I'm the only admin working on this so no disabling for sure but here are the error logs from 8/2/09 which is basically when the backup process failed. and after I did the manual back today i received the same error in the sql server error log files so it looks like its processing a little bit of the database and its dropping off . because the progress bar goes up one bar and then stops but the weird things is that its not giving an error but instead a msg saying that the backup has been successful !!
below is a copy and paste of the files if someone can explain what it means:
BACKUP failed to complete the command BACKUP DATABASE [CB_MAIN] TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\CB_MAIN\CB_MAIN_db_200908022330.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
BackupDiskFile::OpenMedia: Backup device 'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\CB_MAIN\CB_MAIN_db_200908022330.BAK' failed to open. Operating system error = 2(The system cannot find the file specified.).
P.S. I know it says file not found or something but all DB's are operational without any problems.
August 6, 2009 at 4:08 pm
That error message is stating that it could not open the backup file, not the database file. That can be caused be several things, for example it could be that the file exists - but is locked by another process, or it could be a problem with the path to the file.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 6, 2009 at 6:15 pm
could be but I can't think of some process having the backup files locked because i was able to delete a few of them as a test .. but I'm gonna try to restart the server tonight and try again .. and see if that might release anything that could be holding these files from being backed up .
I will keep you updated. thanks
August 7, 2009 at 7:32 am
Jeffrey Williams (8/6/2009)
That error message is stating that it could not open the backup file, not the database file. That can be caused be several things, for example it could be that the file exists - but is locked by another process, or it could be a problem with the path to the file.
Problem solved. I stopped all the tasks and restarted the PC , cleaned up the backup folders and backup worked fine. it seems that something was having these files locked as you mentioned after reading the log files. I also remembered that i was defragmenting the HD around that time frame and it kinda stopped responding and had to end task it but again I'm not sure if that is what had these files locked.
thanks for everyone's suggestions.
lee
August 7, 2009 at 10:39 am
We have found that virus scanners can sometimes cause this sort of problem..
James
--
James Moore
Red Gate Software Ltd
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply