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).

Answer 2

Just to add to the answers since the accepted answer did not work for me on Azure. Use the below script to Delete all the tables and basically reset the azure database. It firstly deletes all constraints and then drops all of the tables.

As @Skorunka František commented this script assumes you use the default [dbo] schema. Although you could replace it with your own schema name.

/* Azure friendly */ /* Drop all Foreign Key constraints */ DECLARE @name VARCHAR(128) DECLARE @constraint VARCHAR(254) DECLARE @SQL VARCHAR(254)  SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)  WHILE @name is not null BEGIN     SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)     WHILE @constraint IS NOT NULL     BEGIN         SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'         EXEC (@SQL)         PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name         SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)     END SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) END GO  /* Drop all Primary Key constraints */ DECLARE @name VARCHAR(128) DECLARE @constraint VARCHAR(254) DECLARE @SQL VARCHAR(254)  SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)  WHILE @name IS NOT NULL BEGIN     SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)     WHILE @constraint is not null     BEGIN         SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'         EXEC (@SQL)         PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name         SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)     END SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) END GO  /* Drop all tables */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254)  SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])  WHILE @name IS NOT NULL BEGIN     SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'     EXEC (@SQL)     PRINT 'Dropped Table: ' + @name     SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name]) END GO 

Sadly I cannot find the source for this code anymore as i had it saved in one of my repositories. I hope it helps someone.