November 17, 2008 at 1:10 pm
Hello and thanks for the great forum.
Backup jobs are failing with the error below. Searching the forum has been helpful but I’m having some difficulty. This backup worked for months before this. Backup is going to a UNC located on a virtual machine on the same SAN. SQL version is 2005 standard SP2 – 64 bit windows on VMware
My first thought was to change the backup location but the job steps don’t point to the backup location; they just read: DUMP DATABASE My_DB TO My_DB_data_dump WITH INIT
The second of the two steps is for the log: BACKUP LOG My_DB WITH TRUNCATE_ONLY
This must be a real easy question? From where is the job getting the backup location? I’d like to change this first to test the job itself. I've read here that I should backup locally first....
Neither DB properties or the backup option on the DB have been helpful in determining the backup location. I can’t find this UNC path!
Second question has to do with reformatting the media as described here http://www.sqlservercentral.com/Forums/Topic597271-357-1.aspx, and in the error message text.
I am learning as I go and nervous about anything that says “Format” 🙂 -meaning I want to be sure I get the T-Sql syntax correct so as not to botch anything.
Executed as user: NT AUTHORITY\SYSTEM. The backup data at the end of "My_DB_data_dump(\\UNC_path_on_SAN\data_dump.BAK)" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). NOTE: The step was retried the requested number of times (2) without succeeding. The step failed.
November 17, 2008 at 1:17 pm
➡ follow up --
This recent post is some help. http://www.sqlservercentral.com/Forums/Topic602879-357-1.aspx
Is it corrct that the backup location is in the registry? Shouldn't there be another way of editing it?
It is not in the job steps.
Thanks
November 17, 2008 at 3:08 pm
The backup job is backing up to a Backup Device, basically a pointer to a file's disk location. You can see the devices in SSMS under Server Object -> Backup Devices. Or you can sys.sysdevices.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 17, 2008 at 4:19 pm
That was the help I needed. Thanks!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply