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

Since there is not an API way to do this that I am aware of, we have used this script to leverage a T-SQL query to clear the database.

To delete each table (and maintain your EF migration histories if you want)

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

To remove the foreign keys first if you need to

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 my testing, this worked without issues (except I did not have the where clause in the DROP TABLE while query since I don't use Code First or EF migrations).