"Back Up" not appearing in SQL Server Management Studio 2016 or 17

Question

I am trying to backup my entire SQL Server database, so I can restore it in case I mess something up (about to revamp my entire Umbraco site). However, according to Microsoft's guide (and others' as well), there should be a task called Back Up. However, there is not.

That is for SSMS 17 (version 14). SSMS 2016 (version 13) shows the exact same thing.

Answer 1

To backup a SQL Azure database you need to select the "Export Data-tier Application..." option.

This will create a .bacpac file which you can then restore to either another SQL Azure database or an on-premises SQL Server.

See the Microsoft Documentation here for more details.

Answer 2

In Azure SQL DB, backups occur automatically. If you want to export a database, you can export to a BACPAC (make sure active transactions are not occurring during the export). See https://learn.microsoft.com/en-us/azure/sql-database/sql-database-automated-backups and https://learn.microsoft.com/en-us/azure/sql-database/sql-database-export.

Answer 3

For me the problem was the database name, of all things. I know it sounds weird, but while researching this issue I discovered a bug in SSMS that causes the context menu to change (including not having backup/restore options) based simply on the database name.

I'm using 18.4, but the bug likely exists in earlier versions as well.

I've reported the bug here, but I'll summarize the steps to reproduce the issue here for convenience:

1. In SSMS, Right-click the Databases node, choose New Database...
2. Name it 1.2.3.4, click OK
3. Right-click on the newly-created database. There is no option to Back up (or restore)
4. Click on the name (so that you can rename it)
5. Rename the 1.2.3.4 database 1234 (remove the periods)
6. Perform step 3 again - this time you'll see the expected Back up... and Restore options.

It appears that the context menu for SSMS is incorrectly assuming the database type because of some pattern in the name involving periods. As for the specific pattern, I don't know. I do know that periods in general aren't a problem, but names like 1.2.3.4 are problematic. Test_1.2.3.4 is fine. I'll leave it someone actually debugging the problem to figure it out.

Hopefully this will help someone else that comes along looking for answers.