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?

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

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

Solution 3

Just to add my variant to the mix... This one also takes into account Views and External Tables. It will not barf on External Tables which have to be removed separately with DROP EXTERNAL TABLE. This caused the original version to spin forever.

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


while(exists(select 1 from INFORMATION_SCHEMA.TABLES 
    where TABLE_NAME != 'database_firewall_rules' 
    AND TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME NOT IN (select name from sys.external_tables)))
begin
 declare @sql1 nvarchar(2000)
 SELECT TOP 1 @sql1=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_NAME != 'database_firewall_rules'
    AND TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME NOT IN (select name from sys.external_tables)
exec (@sql1)
 PRINT @sql1
end

Solution 4

I usually

1. open SQL Server Management Studio or from Visual Studio open SQL Server Object Explorer
2. I connect to the Azure SQL Server (i.e: yourserver.database.windows.net with your username and password for SQL Server authentication option selected) (also remember that you would need to add a firewall exception in Azure portal to connect from your PC to the database in this way)
3. Right click on the Database and delete.

As simple as that.

Then, since you mentioned that you have a code first migrations approach, simply run the migrations again in the Azure SQL Server (for example when publishing make sure you check the option to apply migrations for the given sql server connection string)

I usually delete the remote database and then re-deploy the application with the command to re-run the migrations. This will create again the database with the new tables. The code to seed the database is in my Startup code so it's seeded any time the app is initialized if there is no values in DB.

This is also valid for AspNet Core Mvc (MVC6)

Solution 5

Azure SQL is essentially SQL server. So you can use MS SQL Server Management Studio https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Open the database in Management Studio using your admin account, then execute drop table SQL commands or use the GUI right click menu to do whatever you want, just like using a local database.

Similar thing can be done in the Visual Studio SQL Server Object Explorer. Just right click the root "Add SQL server".

View more solutions

Comments

[Ivan]

This doesn't seem to work on Azure, it deletes like 1/8th the tables then spins forever, and when I hit stop I see a bunch of errors that it has no permission to delete some special tables. The foreign key one seemed to run, but didn't really help with the other issues.

[Ivan]

How would I do that and will Azure let me create a new database from C#?

[Tommy]

@Ivan - I found the issue, the WHERE clause was incorrect, you also have to filter by TABLE_TYPE. Check the updates and let me know if you are still having a problem?

[dorsz]

It's working like a charm ran on sql server in Azure!

[Dane Vinson]

Thanks, that was exactly what I was looking for. Connect to the Azure server with SSMS, new query against my target database, execute the above and bingo, fresh SQL database.

[Skorunka František]

The above does not work if you do not use the default "dbo" schema.

[Skorunka František]

I think this does not work for Azure SQL, at leas did not work for me. I had to create DB in Azure Portal again.

[Skorunka František]

Strange, I tried few minutes ago. Deleted DB in MSSMS, then tried to run migration and get an user "cat'n access provided database" error, or similar.

[diegosasw]

I am using Asp.Net Core and EF Core and I apply migrations through Visual Studio when publishing (by checking the option apply migration). Maybe you're using EF 6? If so maybe you have to run Add-Migration InitialCreate to specify that the database does not exist and you want to create it again. Haven't tried this though.

[chris31389]

I just declared a schema variable and added 'AND CONSTRAINT_SCHEMA = @Schema' in the where clauses

[sensei]

Ok this worked for me on server where i didn't have permissions to drop db. Thanks

[jsgoupil]

Well, I tried to edit this script to add temporal tables deletion, but an admin refused it... This script won't work with SQL 2016 and temporal tables.

[Flater]

The script in this answer throws FK errors. The FK constraints need to be lifted before the PK constraints. A dirty workaround is to execute this script several times as it eventually clears everything up anyway. A better fix is to first drop all FK constraints, which can simple be prepended to the script in this answer.

[Gizmo3399]

@Flater Okay, that is strange since the first step of the script is trying to remove the Foreign Keys before it drops the Primary Keys. Could there a an error with the way the script does this, that makes it miss a few FK's? Or could be just replace the first part of the script with your suggestion instead of prepending it to this script?

[Edd]

Works well on Azure SQL. Thanks for the post Gizmo3399!

[izio]

Care, INFORMATION_SCHEMA.TABLES sometimes returns as rows also View objects, making while looping forever (because they are never deleted). Better use Gizmo answer!

[Tipx]

@izio Gizmo's won't work in any business that follow a good practice of not using dbo for everything. It could easily be adapted, but I just want to point it out so people don't dismiss this answer.

[Evil Pigeon]

Never, ever let any tools automatically create Azure databases for you. The default database is very expensive. Instead, create the database using the Azure portal so you can double check the selected plan. Then you can point your app at it to run migrations, etc.

[Thomas Barnekow]

This worked for me like a charm on an Azure SQL Database. Thanks a lot!