Backup Problem

Question

Backup Problem

jbrewer

Default port

Points: 1453
More actions
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.

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

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

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 1

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

jbrewer Default port Points: 1453 More actions · Answer 2

jbrewer

Default port

Points: 1453

April 11, 2006 at 5:31 am

#631888

Yes, it is the correct database.

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

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 !!!**

jbrewer Default port Points: 1453 More actions · Answer 4

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.

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

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 !!!**

jbrewer Default port Points: 1453 More actions · Answer 6

jbrewer

Default port

Points: 1453

April 12, 2006 at 6:22 am

#632059

Thank You, I will try that.

Jason Hall Hall of Fame Points: 3906 More actions · Answer 7

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.