How to drop all tables and reset an Azure SQL Database

Question

I have an ASP.NET MVC 5 project that works local and whenever I need to blow away the DB, I just open a new query on it, change the available database dropdown to master, then close the connection on my local db and run the query "drop database [name]". Then I build the project, go into the package manager console and run "Update-Database". This seems to rebuild a fresh local database and runs the seed method in my configuration.cs file.

The problem is when I need to test things in a live environment so I can test API's and such better, I will do a deploy to an Azure Website and the accompanying Azure DB, which is nice and easy to do. I check off the "Execute code first migrations" in the publish wizard and most of the time it works and I can run and debug my live version. Sometimes I need to blow away that db and start from scratch again, but the only way I've really found to do it is to go into the Azure portal, delete the database, and then re-create it with the same name. This takes some time for Azure to process, so this is a slow testing cycle.

Is there a quick way to just drop/reset a Azure SQL DB to it's fresh, empty, virgin state and then re-publish with "execute code first migrations" to have it re-create the tables and re-seed the data?

I've seen some talk of creating an initial migration after I create the db, and then trying to use the Powershell to do some sort of roll-back to that initial state, but I haven't had luck getting it to work, and I want to delete all the data at the same time. Maybe I've just got the wrong syntax or haven't found a good enough tutorial. While I can run a query on the Azure DB to "drop database [x]" it literally kills the SQL Azure DB instance as you'd expect and you need to go back into the portal to recreate it. Sometimes that initial state is no good as the model has since been updated, so this may not be useful anyway.

I feel like there should be some easier quicker way to test changes on a live environment as there all these great tools and shortcuts provided by MS, but did they just drop the ball here for this phase of development or am I missing something?

Answer 1

There is no way to use an API for this, I understand, it is bit frustating. However, you can refer to this article, which is all about dropping all table and reset Azure SQL Database.

Also, refer the code below, to delete each table (and you can maintain your EF migration histories with this):

while(exists(select 1 from INFORMATION_SCHEMA.TABLES 

             where TABLE_NAME != '__MigrationHistory' 

             AND TABLE_TYPE = 'BASE TABLE'))

begin

 declare @sql nvarchar(2000)

 SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME

 + ']')

 FROM INFORMATION_SCHEMA.TABLES

 WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_TYPE = 'BASE TABLE'

exec (@sql)

 /* you dont need this line, it just shows what was executed */

 PRINT @sql

end

In order to remove the foreign keys, you need to do this:

while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))

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

In case, if it does not work, then I believe your WHERE clause might be incorrect, also you must filter by TABLE_TYPE.

Hope it helps!