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

bvn4nwqk  于 2022-11-17  发布在  其他
关注(0)|答案(8)|浏览(200)

我有一个在本地工作的ASP .NETMVC 5项目,每当我需要清空数据库时,我只需在其上打开一个新的查询,将可用的数据库下拉菜单更改为master,然后关闭本地数据库上的连接并运行查询“dropdatabase [name]"。然后我构建项目,进入软件包管理器控制台并运行“Update-Database”。2这似乎重建了一个新的本地数据库并运行了configuration.cs文件中的种子方法。
问题是,当我需要在实时环境中测试东西以便更好地测试API等时,我将部署到Azure网站和附带的Azure DB,这很好,也很容易做到。我在发布向导中勾选了“执行代码优先迁移”,大多数时候它都能正常工作,我可以运行和调试我的实时版本。有时候我需要清空数据库,然后从头开始,但我真正找到的唯一方法是进入Azure门户,删除数据库,然后用相同的名称重新创建它。这需要Azure处理一些时间,所以这是一个缓慢的测试周期。

是否有一种快速方法可以将Azure SQL DB删除/重置为其全新、空、原始状态,然后使用“执行代码优先迁移”重新发布,以重新创建表并重新植入数据?

我看到过一些关于在创建数据库后创建初始迁移的讨论,然后尝试使用Powershell执行某种回滚操作以恢复到初始状态,但我没有运气让它工作。并且我想同时删除所有数据。可能我只是语法错误或者没有找到足够好的教程。虽然我可以在Azure DB上运行查询以“drop database [x]”,它会像您所期望的那样删除SQL Azure DB示例,您需要返回到门户中重新创建它。有时,初始状态不好,因为模型已经更新,所以这可能没有任何用处。
我觉得应该有一些更容易更快的方法来测试现场环境中的变化,因为MS提供了所有这些伟大的工具和快捷方式,但他们只是在这个开发阶段放弃了球,还是我错过了什么?

kx5bkwkv

kx5bkwkv1#

因为据我所知,没有一种API方法可以做到这一点,所以我们使用了this script to leverage a T-SQL query to clear the database
删除每个表(并根据需要维护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查询中使用where子句,因为我没有使用Code First或EF迁移)。

luaexgnf

luaexgnf2#

由于接受的答案在Azure上对我不起作用,因此仅添加到答案中。使用以下脚本删除所有表并基本重置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

遗憾的是,我找不到这个代码的源代码了,因为我把它保存在我的一个仓库里。我希望它能帮助一些人。

rqqzpn5f

rqqzpn5f3#

只是为了添加我的变体到混合...这一个也考虑到视图和外部表。它不会吐在外部表上,这些表必须用DROP EXTERNAL TABLE单独删除。这导致原始版本永远旋转。

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
zed5wv10

zed5wv104#

我通常
1.打开SQL Server Management Studio或从Visual Studio中打开SQL Server对象资源管理器
1.我连接到Azure SQL Server(即:yourserver.database.windows.net,其中选择了SQL Server身份验证选项的用户名和密码)(另请记住,您需要在Azure门户中添加防火墙例外,以便以这种方式从PC连接到数据库)
1.右键单击数据库并删除。
就这么简单。
然后,由于您提到了代码优先迁移方法,只需在Azure SQL Server中再次运行迁移(例如,在发布时,确保选中为给定SQL Server连接字符串应用迁移的选项)
我通常会删除远程数据库,然后使用命令重新部署应用程序以重新运行迁移。这将再次创建包含新表的数据库。数据库的种子代码在我的启动代码中,因此,如果DB中没有值,则在任何时候初始化应用程序时都会种子化数据库。
这也适用于AspNet核心Mvc(MVC6)

bcs8qyzn

bcs8qyzn5#

Azure SQL本质上是SQL服务器。因此,您可以使用MS SQL Server Management Studio https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
使用管理员帐户在Management Studio中打开数据库,然后执行删除表SQL命令或使用GUI右键单击菜单执行任何所需操作,就像使用本地数据库一样。
类似的事情可以在VisualStudioSQLServer对象资源管理器中完成。只需右键单击根“添加SQLServer”。

wnrlj8wa

wnrlj8wa6#

除了@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
sd2nnvve

sd2nnvve7#

如果有人想拥有这样一个脚本,但要按模式过滤,那么就可以了:

/* 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具有schema2.table2的FK时,如果我们要删除schema2,则我的脚本也会删除该FK(即使它是不同的模式)
脚本还会删除序列

aiazj4mn

aiazj4mn8#

或者,您可以在C#中卸除数据库,然后使用ExecuteNonQuery建立新的数据库。

queryString = "DROP DATABASE TestDB2";

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

其中connection是到Azure SQL Database主数据库的连接。
或者,要创建数据库:

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

相关问题