I worked on an interesting issue today where a user couldn’t restore a backup. Here is what this customer did:
- backed up a database from an on-premises server (2008 R2)
- copied the file to an Azure VM
- tried to restore the backup on the Azure VM (2008 R2 with exact same build#)
But he got the following error:
Msg 3241, Level 16, State 0, Line 4
The media family on device ‘c:\temp\test.bak’ is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 4
RESTORE HEADERONLY is terminating abnormally.
We verified that he could restore the same backup on the local machine (on-premises). Initially I thought the file must have been corrupt during transferring. We used different method to transfer file and zipped the file. The behavior is the same. When we backed up a database from the same Azure VM and tried to restore, it was successful.
Click through for Jack’s findings as well as a couple workarounds.
Upon executing the first procedure, we get the error message back to the front end, but after checking balance, we find that money withdrawn from the account, but in the case of the second procedure, the same error returned to the front end but money still there.
Now we begin to scratch our head trying to figure out why we lost the money even though we got errors in both cases. The truth behind is the fact that RAISERROR does not stop the execution of code if it is outside of TRY CATCH block. To get same behavior out of RAISERROR, we would need to rewrite procedure to look something like following example.
There are some nuanced differences between THROW and RAISERROR, so it’s valuable to know how both work.
Believe it or not, there’s not an immediately obvious “Oh, you had an error in your Automation script, here’s how you alert someone” setting in the Azure portal. Now, you could simply put error handling in your PowerShell script. In fact, it’s probably not at all a bad idea to do that as well. However, what you would not get setting things up that way is a mechanism for managing the alerts, history, additional possible responses (like firing off another Runbook, although there is way to do that from the PowerShell too). Instead, what I want is way to manage alerts through the Azure fabric.
If you do a search, there is an Azure Alert service. However, it didn’t seem to be really what I was looking for. Further, I found it extremely difficult (OK, I couldn’t make it work) to connect the alerts directly to the Jobs related to my Runbooks. Instead, after quite a bit of research, what I found is a combination of Azure Log Analytics with the Operations Management Suite (OMS) will do exactly what I’m looking for.
Click through to read how to set this up.
One such problem is when you use the New-Cluster command to add all your nodes in one go.New-Cluster -Name magrathea -node server5,server6,server7 -staticaddress 192.168.1.70
Simple right? Well no. In this instance I ran into the following error:New-Cluster : There was an error adding node 'server7' to the cluster the node cannot be contacted. Ensure that the node is powered on and is connected to the network.
Read on for an example of piecemeal debugging. Mark’s advice is to keep things simple, as in this case at least, you can’t count on the error messages coming back to be completely accurate.
Sometimes the users of SQL Backup Master may face the following error while executing the database backup job:
Msg 3201, Level 16, State 1, Line 1
Job Execution Error: Cannot open backup device ‘’ Operating System error 3 (The system cannot find the path specified.)
Now, there can be the various possible reasons behind the occurrence of this error. Therefore, in the following sections, all possible reason with their respective solutions are discussed. A user can refer them to solve this SQL Server operating system error 3(the system cannot find the path specified.)
Click through for solutions to several potential causes of this error.
Windows PSR “Problem Steps Recorder”. It’s a nifty tool that helps you trouble shoot a computer problem by recording step by step what the user is doing.
Go to Start
Steps Recorder or Problem Steps Recorder depending on Windows version
Select Start Record (App will popup)
That’s a new one on me; click through to check it out.
Compressing your backups has very few downsides. It’s usually faster (the additional time for compression is less than the time saved by reduced IO) and of course, the backups are smaller. I have run into a few issues, however.
On one occasion there was 75gb free on a drive, the last full backup was only 50gb and the database had not grown significantly in size. Interestingly there was an error when we tried to run a backup. Not enough disk space.
Read on for the explanation as well as the solution.
Stupid question… what’s the schema of a table with sys.sp_readerrorlog output? Well you might be surprised if you’re used to using nvarchar(max) or nvarchar(2048).
There’s a datetime (modern datetime2(3)) obviously. ProcessInfo is either “Server” or “spidxxxy” where xxx is an int (max of 11 characters including minus) and y is an optional single character suffix. But as for the text…
Let’s try to max it out!
Moral of the story: keep those error messages as short as possible while still being meaningful.
Now ignoring the fact that you already know what is wrong, this tells me that there is either something wrong with the
$sqlvariable or the syntax statement. Maybe even something else though!
This is not helpful and I’m going to have a bad time.
Shane also has an interesting side note around error colors.
T-SQL and ADO.NET data access code must work in concert with one another to ensure SQL errors are detected in application code. The T-SQL constructs used in multi-statement batches can affect if and how when errors are reported by ADO.NET during batch execution. I’ll start by citing core T-SQL error handling objectives, which can be summarized as:
1) Ensure a multi-statement T-SQL batch doesn’t continue after an error occurs.
2) Rollback transaction after errors.
3) Raise error so that the client application is aware a problem occurred.
Read the whole thing.