Post reply

Backup Problem

  • April 10, 2006 at 6:55 am

    #111624

    When I back up my database in SQL Server 2000 Enterprise Manager through the Maintenance Plan, my backup is 6.9 Gb.  The actual size of the database is 6.8 Gb.  When I backup my database by using the following script, my backup is 19.5 Gb. BACKUP DATABASE xyz to xyzbd with init.  What am I doing wrong in the script?  I have another database that I also tested backing it up both ways and the sizes with the Maintenance Plan and the script are very close in size. 

  • April 11, 2006 at 2:38 am

    #631863

    Hi,

    If you select the device and proceed as if you were doing a full restore (Don't actually restore) you will see a 'view contents' button. Click on this and check to see if the correct DB backup(s) are in there.

     

    Regards

    Eoin

  • April 11, 2006 at 5:31 am

    #631888

    Yes, it is the correct database.

  • April 11, 2006 at 6:20 am

    #631891

    sounds like despite your "init" clause the backup is not getting overwritten...

    check these 2 conditions to see if that's what's happening:

    BOL:

    All backup sets on the media have not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.

The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME clause.







    **ASCII stupid question, get a stupid ANSI !!!**

  • April 11, 2006 at 7:11 am

    #631903

    This problem did start happening on April 2nd during the time change.  It does not appear that the init is working properly but this is the first time that this has happened.  This is a script that has been running for a couple of years and just now started having the problem.  I do not specify the EXPIREDATE or RETAINDAYS in the script, it is very simple.  This script runs weekly during Maintenance.  I backup daily with through a Maintenance plan written in Enterprise Manager and it works great.  It is the correct backup media.  I checked the backup device and there is no Expiration date on it.

  • April 11, 2006 at 3:15 pm

    #631964

    jbrewer - since you want the backup overwritten rerun it using the "with format" option instead...

    re: retaindays..here's where you'd find the default..

    "By default, the expiration date is determined by the media retention option set with sp_configure."







    **ASCII stupid question, get a stupid ANSI !!!**

  • April 12, 2006 at 6:22 am

    #632059

    Thank You, I will try that.

  • April 14, 2006 at 9:34 am

    #632651

    Try doing a RESTORE HEADERONLY FROM ... and see if there are multiple backups in the file.  There will be one row in the result set for each backup.  If there are multiple rows than you know the init is not working.

    If there is only one row and therefore only one backup in the file you may want to check your transaction log size.  If the transaction log has grown considerably it will add to your backup size.

Viewing 8 posts - 1 through 7 (of 7 total)

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