"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.

Solution 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.

Solution 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://docs.microsoft.com/en-us/azure/sql-database/sql-database-automated-backups and https://docs.microsoft.com/en-us/azure/sql-database/sql-database-export.

Solution 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.

Comments

[jarlh]

Are you trying to back-up a MySQL database using SSMS?

[MortenMoulder]

@jarlh Yes. I assume that since SSMS works for browsing tables, grabbing data, running queries, etc. it should also work for backing up.

[a_horse_with_no_name]

Since when does SSMS work with anything else then SQL Server?

[MortenMoulder]

Maybe I'm confused. Maybe it is a SQL server.

[Gareth Lyons]

Given you seem to be missing loads of tabs in object explorer, does the account you're connected with have the required permissions to backup a database on that instance?

[MortenMoulder]

@GarethLyons Yes I am logged in with the user I created on Azure. Full access to the server and database.

[Gareth Lyons]

The guide you linked is for SQL Server, I don't think you can backup Azure databases in that manner (SQL Azure <> SQL Server). This might help azure.microsoft.com/en-gb/blog/… but I'm no Azure admin.

[MortenMoulder]

@GarethLyons I do not use SQL Azure. I simply host my SQL Server on Azure. Essentially the same thing as hosting it on a standalone Windows server.

[MortenMoulder]

Yeah I've gone that route as well. Seems like I can't get an SQL file out.

[Thangadurai]

I don't think so. I have a database in this format 'AppName.Module' and I take backup of that database regularly without any issue.

[TadGhostal]

Perhaps try creating a database named 1.2.3.4. as in step 2? I, too, have databases with periods in their name. Having a period isn't an issue, but it seems the specific pattern of periods is the issue. I can't tell you what specific pattern causes the problem, other than something like 1.2.3.4. Others with similar 4 part version names like 5.6.7.8, or 1.1.1.1 causes the problem, but test_1.1.1.1 is fine. I'll change the assumption to be clearer.