Sql-server – CREATE DATABASE equivilant of RESTORE DATABASE WITH REPLACE

sql server

I have a T-SQL script that drops and recreates a database like so:

USE master 
GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'foo')
BEGIN
    ALTER DATABASE [foo]
        SET OFFLINE WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [foo]
END
GO

CREATE DATABASE foo;
GO

-- OTHER DDL statements

Right now my system is in a state where the database is dropped but the LDF/MDF exists so the CREATE DATABASE statement is in the following state:

Msg 5170, Level 16, State 1, Line 2
Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL10.PEER1\MSSQL\DATA\eventManagement.mdf' because it already exists. Change the file path or the file name, and retry the operation.
Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

The restore database command has a REPLACE option in the WITH clause for when this happens. Is there an equivalent in the CREATE DATABASE statement?

Best Answer

I think this is happening because of your set offline statement.

Try something like this:

create database foo 

GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'foo')

BEGIN

    ALTER DATABASE [foo] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE


DROP DATABASE [foo]

END


CREATE DATABASE foo;

GO
Related Topic