SQL SQL完全清空数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1105067/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Completely Empty Database
提问by corymathews
I am writing an install script that installs all tables, stored procedures, views, full text indexs, users ect.
我正在编写一个安装脚本，用于安装所有表、存储过程、视图、全文索引、用户等。
It all works fine if the user has all the correct permissions and the script runs from start to finish. However if the script dies somewhere midway through then it cannot just be run again.
如果用户具有所有正确的权限并且脚本从头到尾运行，则一切正常。但是，如果脚本在中途死亡，那么它就不能再次运行。
To accomplish this I want to basically return the database to a "brand new" state where it has nothing.
为了实现这一点，我想基本上将数据库返回到一个“全新”的状态，在那里它什么都没有。
I realize how to drop each table/sp/view.. on their own but I am looking for a more general way to reset the database.
我意识到如何自行删除每个表/sp/view.. 但我正在寻找一种更通用的方法来重置数据库。
I also need to be able to delete Fulltext Catalogs and users.
我还需要能够删除全文目录和用户。
Thanks for any help.
谢谢你的帮助。
Running SQL Server 2005
运行 SQL Server 2005
采纳答案by dxh
Sounds like a job for Drop Database:
听起来像是Drop Database的工作：
-- SQL Server Syntax
DROP DATABASE [ IF EXISTS ] { database_name | database_snapshot_name } [ ,...n ] [;]
回答by Robin Day
Can you run the entire script within a transaction and then call a rollback if it fails?
您能否在事务中运行整个脚本，然后在失败时调用回滚？
A CREATE TABLE can be rolled back just as easily as an INSERT/UPDATE.
CREATE TABLE 可以像 INSERT/UPDATE 一样容易地回滚。
回答by David M
Brand new containing nothing? Drop the database and recreate it if you have permissions to do this.
全新的什么都没有？如果您有权限执行此操作，请删除数据库并重新创建它。
回答by Rod
Drop and recreate DB:
删除并重新创建数据库：
use master
IF EXISTS(select * from sys.databases where name='YourDBName')
DROP DATABASE YourDBName
CREATE database YourDBName
回答by ramnik
If you have your scripting process automated per object, you should be able to wrap the entire process in a transaction and provide error handling for each DDL statment. This also works well when applying schema updates. Please note, this is based upon upgrade scripts that Red-Gate SQL Compare generates.
如果您的脚本过程按对象自动化，您应该能够将整个过程包装在一个事务中，并为每个 DDL 语句提供错误处理。这在应用架构更新时也很有效。请注意，这是基于 Red-Gate SQL Compare 生成的升级脚本。
Header:
标题：
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
Footer:
页脚：
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'Script succeeded'
COMMIT TRANSACTION
END
ELSE BEGIN
PRINT 'Script failed'
END
GO
DROP TABLE #tmpErrors
Wrapper (For each database object):
包装器（对于每个数据库对象）：
/* Insert Data Definition here then append with...*/
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
回答by A-K
Red Gate's SQL Compare will do it for you, it is a great tool.
Red Gate 的 SQL Compare 会为你做这件事，它是一个很棒的工具。
回答by Jonathan
I'm not sure if this is what you're looking for, but for each object, you could test if exist before creating it. Then you could run again the script if it fails in the middle.
我不确定这是否是您要查找的内容，但是对于每个对象，您可以在创建之前测试它是否存在。然后，如果脚本在中间失败，您可以再次运行该脚本。
IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(@Object) AND type = (@Type))
...
...
回答by Bogdan_Ch
You can run the whole script in one transaction, so you will be able to rollback it at any point.
您可以在一个事务中运行整个脚本，因此您可以随时回滚它。
Second option - before installation always create a backup. If it fails, restore the database from backup
第二个选项 - 在安装之前总是创建一个备份。如果失败，从备份中恢复数据库
