SQL Completely Empty Database

Question

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.

I also need to be able to delete Fulltext Catalogs and users.

Thanks for any help.

Running SQL Server 2005

Answer 1

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.

Answer 2

Sounds like a job for Drop Database

Answer 3

Brand new containing nothing? Drop the database and recreate it if you have permissions to do this.

Answer 4

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

Answer 5

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

...

Answer 6

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.

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

Answer 7

Red Gate's SQL Compare will do it for you, it is a great tool.