SQL Backup File Issue

Backup and Recovery
SQL Server
SQL Server backup
Hello, I am implementing a backup procedure for our SQL databases running Microsoft SQL Server 2000 ? 8.00.2039 on Windows Server 2003 Enterprise Edition. I have done so through a DB maintenance job. I am saving the backup as a file to a custom location on the same server. I also have the job verify the integrity of the backup and the backup jobs to write history reports. The backups get verified with no errors and logs are written when the jobs complete. Something seems to be wrong though because according to the logs, the jobs are only taking a couple of seconds and the backup file size is only a few MB's when the database is several GB's. This is the case for all of about a dozen databases I am trying to back up. The file sizes vary from under a hundred MB's to the largest that is 10GB. The databases are all set to the Full recovery model and I am performing complete backups. Transactional logging is also setup and I am attempting to back them up completely as well. I have even tried creating a permanent backup device pointing to a file location and manually selecting to backup the DB's to that device but get the same result. Everything else checks out and these databases are used constantly with no issues. The backups are also scheduled late night so as not to interfere with the user environment. I am under the impression that the backups should reflect a similar size to the actual DB. Am I just being ignorant here or does it sound like something is wrong?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Yes, you do have a problem. My main database is about 4 GB and takes fifteen to twenty minutes to backup. Time is a function of Processor speed, available memory, and storage capabilities.
MSSQL has some known ‘gotcha’ settings. The most perplexing is the default selection in the backup properties to append instead of overwrite. Append as implemented is NOT an incrememtal backup it only adds new stuff. Incremental should remove items as they leave the database.
My procedure is to open “Enterprise Manager” – open local – open databases – right-click the database in question, and from the contect menu select all tasks – backup database. The ensuing dialog box shold be verified and selection made as follows – Looking at the correct databse. Selected overwrite NOT append. If this is the first time you need to add a item as the to be stored location. Be sure the correct item is selected. If you loaded the database from a backup, and then deleted the file, the item will still be there but the backup fails (sometime silently)as file/location not found. I always run the ‘verify data’ option. On new stups I run the backup manually before scheduling. Then I can verify a restore to the test server or if you only have one restore to a different name. Remember to select overwrite when you go back to set scheduling.

Good luck.

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Jason11999
    Thank you for your reply Howard2nd. I thought something didn't seem right. Now the question is; how do I fix this? I don't think I setup anything incorrectly and also did try to backup the database off of the local database listing as you said you do via SQL Enterprise manager, however the job runs and completes immediately with no errors but with a backup file of only about 1MB, just like when run via the DB mantaince jobs. I'm totally lost at this point since I have very little expirence with SQL and have never seen this before. I would definitely apprieciate any help that anyone could offer me. It's even more strange, because I am having this happen on more then one server and for every instance on servers that have more then one.
    0 pointsBadges:
    Follow Howard2nd's response carfully. Something is wrong!! Make sure you have enough disk space for the backup to start with. Watch the job in profiler as well. Take your time and verify everything. As Howard pointed out, make sure you use the override option. the default is to append. William
    0 pointsBadges:
  • DBAndrew
    Have you checked that the user performing the backup (i.e. the user that SQLAgent runs under) has adequate permissions to write to the destination directory?
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: