Database-Update to recreate database on Azure

I’ve been working on an Entity Framework Code First solution using an Azure database. The migrations work like a charm, but often I’d like to recreate the database from scratch and re-seed from scratch. DropAndRecreate does not work due to permission issues, so the next best thing is to drop all tables in the database and use Database-Update from the Package Manager Console to recreate the database.

I use the following scripts to drop every constraint and then every table:


while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY' and CONSTRAINT_SCHEMA != 'sys'))
begin
declare @sql nvarchar(2000)
SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
exec (@sql)
PRINT @sql
end


while(exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA != 'sys'))
begin
declare @sql nvarchar(2000)
SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA != 'sys'
exec (@sql)
PRINT @sql
end

After which I use the Package Command Console to run:


Update-Database -ConnectionStringName AzureDevTest

Since the migration history table is missing it will run all the migrations.

Unable to drop database role in SQL Server

I just ran into an issue where I was unable to delete a database role, somehow my custom database role had become the owner of the db_datareader role.

Deletion of the role was not possible due to the following error:

Error: 15138 The database principal owns a schema in the database, and cannot be dropped.

The solution is straightforward:

First find out which schema is being owned by the rolename:

SELECT name FROM sys.schemas WHERE principal_id = USER_ID( ‘<rolename here>‘ )

Then change ownership using:

ALTER AUTHORIZATION ON SCHEMA::<schemaname here> TO dbo

After which you can drop the role

DROP ROLE <rolename here>