如何删除所有表并重置 Azure SQL 数据库

我有一个 ASP.NET MVC 5本地项目，每当我需要吹走数据库，我只是打开一个新的查询对它，改变可用的数据库下拉到主，然后关闭连接对我的本地数据库，并运行查询“下拉数据库[名称]”。然后我构建项目，进入包管理器控制台并运行“ Update-Database”。这似乎重新构建了一个新的本地数据库，并在我的 configuration.cs 文件中运行了種方法。

问题在于，当我需要在一个活动环境中测试一些东西，这样我就可以更好地测试 API，我将把它们部署到一个 Azure 网站和相应的 Azure 数据库中，这非常容易做到。我在发布向导中勾选了“执行代码优先迁移”，大多数时候它都能正常工作，我可以运行和调试我的实时版本。有时候我需要清除数据库，重新从头开始，但我真正找到的唯一方法是进入 Azure 门户网站，删除数据库，然后用相同的名称重新创建数据库。Azure 需要一些时间来处理，因此这是一个缓慢的测试周期。

有没有一种快速的方法可以将 Azure SQL DB 放置/重置到它的新鲜、空白、处女状态，然后用“执行代码优先迁移”重新发布，让它重新创建表并重新播种数据？

我见过一些关于在创建 db 之后创建初始迁移的讨论，然后尝试使用 Powershell 来回滚到初始状态，但是我没有运气让它工作，我想同时删除所有数据。也许我只是语法错误或者没有找到足够好的教程。虽然我可以在 Azure DB 上运行一个查询来“删除数据库[ x ]”，但它实际上会像您预期的那样杀死 SQL Azure DB 实例，您需要返回到门户来重新创建它。有时候初始状态并不好，因为模型已经被更新了，所以这可能无论如何都没有用处。

我觉得应该有一些更简单更快捷的方法来测试活动环境中的更改，因为 MS 提供了所有这些伟大的工具和快捷方式，但是他们只是在这个开发阶段丢掉了球还是我遗漏了什么？

44395 次浏览
小开
最佳答案

由于没有一种 API 方法可以做到这一点，我知道，我们已经使用了 该脚本利用 T-SQL 查询来清除数据库。

删除每个表(如果需要，还可以维护 EF 迁移历史)

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

如果需要，首先删除外键

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

在我的测试中，这没有任何问题(除了我在 DROP TABLE while query 中没有 where 子句，因为我没有使用 Code First 或 EF 迁移)。

小开

或者，您可以删除 C # 中的数据库，并使用 ExecuteNonQuery创建一个新的数据库。

queryString = "DROP DATABASE TestDB2";


SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
command.ExecuteNonQuery();

其中连接是到 Azure SQL 数据库主数据库的连接。

或者，也可以创建数据库:

queryString = "CREATE DATABASE TestDB2 ( EDITION = 'standard' )";
小开

只是为了增加答案，因为接受的答案不适合我的 Azure。 使用下面的脚本删除所有的表，基本上重置天蓝色的数据库。 它首先删除所有约束，然后删除所有表。

正如@Skorunka Franti ek 所评论的，这个脚本假设您使用默认的[ dbo ]模式。尽管您可以用自己的模式名替换它。

/* 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

遗憾的是，我再也找不到这段代码的源代码了，因为我把它保存在了我的一个知识库中。 我希望能帮到别人。

小开

我通常

  1. 打开 SQLServerManagementStudio 或从 VisualStudio 打开 SQLServer 对象资源管理器
  2. 我连接到 Azure SQL Server (即: 选择了用户名和密码进行 SQL Server 身份验证选项的 yourserver.database.windows.net)(还要记住，你需要在 Azure 门户中添加一个防火墙异常才能以这种方式从你的 PC 连接到数据库)
  3. 右键单击数据库并删除。

就这么简单。

然后，既然您提到了代码优先迁移方法，那么只需在 Azure SQL Server 中再次运行迁移(例如，在发布时确保您检查了对给定 SQL 服务器连接字符串应用迁移的选项)

我通常会删除远程数据库，然后使用以下命令重新部署应用程序以重新运行迁移。这将使用新表再次创建数据库。种子数据库的代码在我的启动代码中，所以如果数据库中没有值，那么在应用程序初始化的任何时候都会种子数据库。

这对 AspNet 核心 Mvc (MVC6)也是有效的

小开

Azure SQL 本质上是 SQL 服务器，因此您可以使用 MSSQLServerManagementStudiohttps://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

使用管理员帐户在 ManagementStudio 中打开数据库，然后执行拖放表 SQL 命令或使用 GUI 右键单击菜单执行任何操作，就像使用本地数据库一样。

类似的事情可以在 VisualStudioSQLServer 对象资源管理器中完成。只需右键单击根目录“ AddSQLserver”。

小开

只是添加我的混合变体... 这一个也考虑到视图和外部表。它不会吐在外部表，必须单独删除与 DROP 外部表。这使得原始版本永远地旋转。

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
小开

@ Gizmo3399给出的答案的补充。我们使用时态表和 Entity Framework (EF) Core 5.0.2和 .NET 5。这是我们用来清除数据库的命令。History是我们用于系统版本的时态表的模式。目前需要运行两次才能删除所有内容，除此之外不需要进行任何修改。

/* Azure friendly */
/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
DECLARE @SQL2 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
IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'History') AND @name != '__EFMigrationsHistory'
BEGIN
SELECT @SQL = 'ALTER TABLE [history].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
EXEC (@SQL)
END
SELECT @SQL2 = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
EXEC (@SQL2)
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)
DECLARE @SQL2 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
IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'History') AND @name != '__EFMigrationsHistory'
BEGIN
SELECT @SQL = 'ALTER TABLE [history].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
EXEC (@SQL)
END
SELECT @SQL2 = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
EXEC (@SQL2)
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)
DECLARE @SQL2 VARCHAR(254)
DECLARE @SQL3 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
IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'History') AND @name != '__EFMigrationsHistory'
BEGIN
SELECT @SQL = ('ALTER TABLE [' + RTRIM(@name) +'] SET (SYSTEM_VERSIONING = OFF)');
EXEC (@SQL)
SELECT @SQL2 = 'DROP TABLE [history].[' + RTRIM(@name) +']'
EXEC (@SQL2)
END
SELECT @SQL3 = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL3)
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


IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'History')
BEGIN
DROP SCHEMA History
END


GO
小开

如果有人想要这样一个脚本，但使用模式过滤器 那就这样吧:

/* Azure friendly */


/* Drop all Foreign Key constraints */


/* Arguments */
DECLARE @schema VARCHAR(128) = '<TODO_PROVIDE_SCHEMA>'




/* Variables */
DECLARE @tableName VARCHAR(128)
DECLARE @tableSchema VARCHAR(128)
DECLARE @constraintName VARCHAR(254)
DECLARE @SQL VARCHAR(254)




SELECT TOP 1
@tableName = sourceTable.name,
@tableSchema = SCHEMA_NAME(sourceTable.schema_id),
@constraintName = f.name
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.tables targetTable ON targetTable.OBJECT_ID = fc.referenced_object_id
INNER JOIN sys.tables sourceTable ON f.parent_object_id = sourceTable.object_id
WHERE targetTable.[schema_id] = SCHEMA_ID(@schema)
ORDER BY sourceTable.name


WHILE @tableName is not null
BEGIN


SELECT @SQL = 'ALTER TABLE [' + RTRIM(@tableSchema) + '].[' + RTRIM(@tableName) +'] DROP CONSTRAINT [' + RTRIM(@constraintName) +']'
EXEC (@SQL)
PRINT 'Executed: ' + @SQL
    

SET @tableName = NULL;


SELECT TOP 1
@tableName = sourceTable.name,
@tableSchema = SCHEMA_NAME(sourceTable.schema_id),
@constraintName = f.name
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.tables targetTable ON targetTable.OBJECT_ID = fc.referenced_object_id
INNER JOIN sys.tables sourceTable ON f.parent_object_id = sourceTable.object_id
WHERE targetTable.[schema_id] = SCHEMA_ID(@schema)
ORDER BY sourceTable.name
END
GO
/* Drop all Primary Key constraints */


DECLARE @schema VARCHAR(128) = '<TODO_PROVIDE_SCHEMA>'


DECLARE @tableName VARCHAR(128)
DECLARE @tableSchema VARCHAR(128)
DECLARE @constraintName VARCHAR(254)
DECLARE @constraintSchema VARCHAR(128)
DECLARE @SQL VARCHAR(254)




SELECT
@tableName = t.name,
@tableSchema = SCHEMA_NAME(t.schema_id),
@constraintName = pk.name,
@constraintSchema = SCHEMA_NAME(t.schema_id)
FROM sys.objects pk
JOIN sys.objects t ON (pk.parent_object_id = t.object_id)
WHERE pk.[type] = 'PK'
AND pk.schema_id = SCHEMA_ID(@schema)
ORDER BY t.name


WHILE @tableName is not null
BEGIN
SELECT @SQL = 'ALTER TABLE [' + RTRIM(@tableSchema) + '].[' + RTRIM(@tableName) +'] DROP CONSTRAINT [' + RTRIM(@constraintName) +']'
PRINT 'Executing: ' + @SQL
EXEC (@SQL)


SET @tableName = null;
SELECT
@tableName = t.name,
@tableSchema = SCHEMA_NAME(t.schema_id),
@constraintName = pk.name,
@constraintSchema = SCHEMA_NAME(t.schema_id)
FROM sys.objects pk
JOIN sys.objects t ON (pk.parent_object_id = t.object_id)
WHERE pk.[type] = 'PK'
AND pk.schema_id = SCHEMA_ID(@schema)
ORDER BY t.name
END
GO






/* Drop all tables */


DECLARE @schema VARCHAR(128) = '<TODO_PROVIDE_SCHEMA>'


DECLARE @tableName VARCHAR(128)
DECLARE @tableSchema VARCHAR(128)
DECLARE @SQL VARCHAR(254)


SELECT TOP 1
@tableName = [name],
@tableSchema = SCHEMA_NAME(schema_id)
FROM sys.tables
WHERE [type] = 'U'
AND schema_id = SCHEMA_ID(@schema)


WHILE @tableName IS NOT NULL
BEGIN
SELECT @SQL = 'DROP TABLE [' + RTRIM(@tableSchema) +'].[' + RTRIM(@tableName) +']'
EXEC (@SQL)
PRINT 'Dropped Table: ' + @tableName


SET @tableName = NULL;
SELECT TOP 1
@tableName = [name],
@tableSchema = SCHEMA_NAME(schema_id)
FROM sys.tables
WHERE [type] = 'U'
AND schema_id = SCHEMA_ID(@schema)
END
GO




/* DROP Sequences */


DECLARE @schema VARCHAR(128) = '<TODO_PROVIDE_SCHEMA>'




DECLARE @seqName VARCHAR(128)
DECLARE @seqSchema VARCHAR(128)
DECLARE @SQL VARCHAR(254)




SELECT TOP 1
@seqName = s.name,
@seqSchema = SCHEMA_NAME(s.schema_id)
FROM sys.sequences s
WHERE schema_id = SCHEMA_ID(@schema)
ORDER BY s.name




WHILE @seqName is not null
BEGIN
SELECT @SQL = 'DROP SEQUENCE [' + RTRIM(@seqSchema) + '].[' + RTRIM(@seqName) +']'
PRINT 'Executing: ' + @SQL
EXEC (@SQL)


SET @seqName = null
SELECT TOP 1
@seqName = s.name,
@seqSchema = SCHEMA_NAME(s.schema_id)
FROM sys.sequences s
WHERE schema_id = SCHEMA_ID(@schema)
ORDER BY s.name
END
GO

在执行之前，不要忘记将 <TODO_PROVIDE_SCHEMA>更改为模式

该脚本将找到与目标模式相关的所有 FK 例如，当 schema1.table1有 FK 到 schema2.table2 如果我们想删除 schema2，那么我的脚本也删除 FK (即使这是一个不同的模式)

脚本也删除序列


提交答案