CODEWITHSUNDEEP
sql-serverazure-sql-databasessms
MortenMoulder
MortenMoulder

Reputation: 6648

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

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.

SSMS 17

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

Upvotes: 34

Views: 25469

Answers (3)

TadGhostal
TadGhostal

Reputation: 151

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.

Upvotes: 1

user3113204
user3113204

Reputation: 91

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.

Upvotes: 2

Andy Sinclair
Andy Sinclair

Reputation: 2293

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.

Upvotes: 71

Related Questions