Backups failing
-
I've created a backup job which overwrites my previous backup from the week before.
However my job is failing with the following message:
The number of retained days or expiration specified for the backup media has not passed yet.
Expiration date: 2036-10-13 01:47:51
I'm not sure why this message is occuring and how to fix it.
I use the INIT and SKIP options within my backup script.
No expiration date has been set which i have confirmed by checking the header of the backup file.
Does anyone have any ideas how to resolve this.
-
Can you post your backup script?
-SQLBill
-
Hi Bill,
Thanks for the quick reply. Here is the script. Real simple script nothing fancy. It should just overwrite the previous backup. But it just fails for this one database.
Backup database pubs to disk = 'C:\Pubs.bak'
with name = 'Database Backup - Monday',
description = 'Database Backup on Monday',
init,
skip,
stats = 10
-
From SQL Server Books Online:
EXPIREDATE = { date | @date_var }
Specifies the date when the backup set expires and can be overwritten. If supplied as a variable (@date_var), this date is specified as either a string constant (@date_var = date), as a variable of character string data type (except for the ntext or text data types), a smalldatetime, or datetime variable, and must follow the configured system datetime format.
RETAINDAYS = { days | @days_var }
Specifies the number of days that must elapse before this backup media set can be overwritten. If supplied as a variable (@days_var), it must be specified as an integer.
Important If EXPIREDATE or RETAINDAYS is not specified, expiration is determined by the media retention configuration setting of sp_configure. These options only prevent SQL Server from overwriting a file. Tapes can be erased using other methods, and disk files can be deleted through the operating system. For more information about expiration verification, see SKIP and FORMAT in this topic.
Delete the file specified by "to disk =" then run your backup. SQL Enterprise Manager defaults to "Append to Media" which would cause it to not attempt to "init" the backup device before creating the backup. In the SQL you specified you are telling SQL Server to "init" or erase the backup device before creating a new backup. My guess is that the backup device (file) you are using was created with an EXPIRATION date (specifically 2036-10-13 01:47:51) and since the date hasn't expired SQL Server is being good about warning you and not simply overwritting the file.
Or you could just wait until 2036-10-13 01:47:52 and then backup your database.
-
I have done a Restore Headeronly on the file which confirms that the Expiration date is NULL.
Having checked media_retention in sp_configure this value is set to 0.
So i am slighlty stumnped how without a backup expiration date why such an error message is occuring.
Any ideas?
-
1.Try backing upto different disk.
2. The problem may be with the media header check BOL FORMAT clause.
3. 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.
Minaz
"More Green More Oxygen !! Plant a tree today"
-
Have you deleted or renamed the existing backup file prior to running your SQL statement?
When you restored the header did you restore the headers for all backup sets in the file or only the first one?
How many backup sets are in the file?
-
I would suggest trying:
1. change the disk location
or
2. delete the existing backup
Do one of those then re-run the backup to see if the issue is resolved. This may have just been a 'fluke'.
-SQLBill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply