Rename SQL Azure database?

Question

How can i rename the database in sql Azure?

I have tried Alter database old_name {MODIFY NAME = new_name} but not worked.

Is this feature available in SQL Azure or not?

Acarpous

try ALTER DATABASE oldName MODIFY NAME = newName but make sure master Databse is selected. – Befoul

In SQL DB V12 you don't need to connect to master to do this – Candidate

Answer 1

Please check that you've connected to master database and you not trying to rename system database.

Please find more info here: https://msdn.microsoft.com/en-US/library/ms345378.aspx

Friction

@ArunRana what db you are connecting to? – Friction

I am connected with SQL Azure database in R2 client of SQL server – Acarpous

when you trying to execute sql script what database is used? Please ensure that you are using master db. – Friction

Alter database [APMV2.4] { MODIFY NAME [APMV2.2_Test] } – Acarpous

Please try this: USE master; ALTER DATABASE APMV2.4 Modify Name = APMV2,2_TEST; – Friction

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '.4'. – Acarpous

Msg 40510, Level 15, State 2, Line 1 Statement 'ALTER DATABASE' is not supported in this version of SQL Server. – Acarpous

This is just incorrect. Are you sure that you are connecting to right database? – Friction

Yes i was wrong, i have change name from sql azure portal with login to master database and use above script, but it was unsuccessful when i was trying with R2 client. thanks – Acarpous

To get this to work recap: 1) Login via web based management tool 2) Run query like: ALTER DATABASE [Blah] MODIFY NAME = [Blah-test] – Cass

that doesn't work. I get: "Msg 40508, Level 16, State 1, Line 1 USE statement is not supported to switch between databases. Use a new connection to connect to a different Database.". if I don't have the "use master" then I get "Msg 5001, Level 16, State 1, Line 1 User must be in the master database." – Beltane

ah. Brian MacKay points to how to log into the master database in the comment below. +1 for him – Beltane

Answer 2

Just so people don't have to search through the comments to find this... Use:

ALTER DATABASE [dbname] MODIFY NAME = [newdbname]

(Make sure you include the square brackets around both database names.)

Axinomancy

You do have to be logged into the master database for this to work... And it isn't super obvious how to do that. From the management portal, I went into one of my databases and clicked manage, which opened up the SQL manager. From there, I couldn't change which database to log into... I had to actually log in and click log out, and only then was I able to type master as the database I wanted. Yikes. But the script works fine. :) – Alpaca

Instead of logging in/out - you can just use the URL https://<YourDBServer>.database.windows.net/?langid=en-us and type 'master'. If you click the link in the portal it adds an URL parameter of #$database=<WhateverWasSelected> which pre-loads the field and makes it read-only. – Subatomic

You don't need to connect to master database in SQl DB V12. You can rename it by connecting to the database. SQL DB V12 supports more alter statements, you can check here: sqlindepth.com/alter-database-in-sql-v12 – Candidate

I ran this command and it worked, however I still got the following error: Msg 42019, Level 16, State 2, Line 1 ALTER DATABASE operation failed. Internal service error. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results if any should be discarded – Melindamelinde

If you get the "Internal service error" references above by Ogglas, make sure to run the query on the master database. – Accountable

I got the internal service error when running this in the Azure Portal SQL tool. But it renamed the database anyway – Crowfoot

Answer 3

You can also connect with SQL Server Management Studio and rename it in Object Explorer. I just did so and the Azure Portal reflected the change immediately.

Do this by clicking on the database name (as the rename option from the dropdown will be greyed out)

Shawn

Thanks zacharydl, this is the easiest way if you have SSMS. – Landside

Just tried this and didn't work. ALTER DATABASE... did the trick. – Haematogenous

Rename is greyed out Sql Server Management Studio 13 – Votary

While rename is greyed out you can still click on the database name and you should be able to over type it (have just added this info to the answer). – Denbighshire

Worked fine for me! – Unfavorable

Answer 4

Connect with SQL Server Management Studio to your Azure database server, right-click on the master database and select 'New Query'. In the New Query window that will open type ALTER DATABASE [dbname] MODIFY NAME = [newdbname].

Viceregent

Or you could just Right click and choose 'Rename'! – Lir

Answer 5

It's Very simple for now - Connect to DB via SQL Management Studio and Just rename as you generally doing for DB [Press F2 on DB name]. It will allow you to do this and it will immediately reflect the same.

Oogenesis

I get "Database "x" on server "y" is not currently available when I try that. – Doykos

Answer 6

I can confirm the

ALTER DATABASE [oldname] MODIFY NAME = [newname];

works without connecting to master first BUT if you are renaming a restored Azure database; don't miss the space before the final hyphen

ALTER DATABASE [oldname_2017-04-23T09 -17Z] MODIFY NAME = [newname];

And be prepared for a confusing error message in the Visual Studio 2017 Message window when executing the ALTER command

Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Evoke

Answer 7

You can easily do it from SQL Server Management Studio, Even from the community edition.

Vietnamese

Answer 8

The following query worked for me.

USE master;
GO

ALTER DATABASE old_name
MODIFY NAME = new_name;
GO

Cantone

That doesn't work for me. It shows an error: Login failed for user '<token-identified principal>'. – Angst