From your reply to my question, then it does appear to be too much backup history. For a one off cleardown, something along these lines will do it:
USE msdb
GO
DECLARE @days DATETIME
SET @days = DATEADD(day,-30,current_timestamp)
EXEC sp_delete_backuphistory @days
GO
That example gets rid of everything over 30 days old, so you may need to change the -30
for a value appropriate for your needs. But remember that you'll then have to build up any restores for backups over 30 days old from hand (may or may not be a problem for you, but thought I'd better mention it).
Going forward you can schedule that to run via a scheduled job to keep you small.
I found the answer by opening the project with Visual Studio 2012.
Open the SSIS project in Visual Studio, and select the .conmgr file in the Solution Explorer pane. Use the context menu and select the "View Code" menu option. This will show the XML within the Connection Manager file.
In the XML, there was this tag:
<DTS:PropertyExpression
DTS:Name="ServerName">@[$Project::my_server_name]</DTS:PropertyExpression>
The ServerName property was set via an expression!
This fact is not easily discernible via the Solution Explorer tree itself. When the conmgr file is selected, the Properties pane only displays the File Name and Full Path properties.
When you open a package that uses the Connection Manager, and select that Connection Manager within the package editor pane, the properties pane shows many other properties, including Expressions. The ServerName property will be visible in the Expressions list only if it has been set.
There are two ways to resolve this issue:
- Within the Visual Studio project, open a package. Select the Connection Manager from the package editor pane, and delete the Property Expression from the Properties pane.
- Remove the PropertyExpression tag from the .conmgr file with a text editor.
Either way, once the ServerName property is no longer set by an expression within the connection manager definition file, it is exposed as a configurable parameter in the Configure dialog box in SSMS 2012.
Best Answer
Azure support pointed me to this article which worked to cure the problem. Apparently the issue was multiple old entries in the msdb backup sets. All left over from when I was experimenting with Azure SQL MI Migration Assistant. Interestingly, the database where the Properties dialog always worked also has multiple old entries in the backup sets. So it's still a head scratcher!