Home / server / Questions / 189112
Accepted
Justin Dearing
Asked: 2010-10-09 08:27:47 +0800 CST

CREATE DATABASE equivilant of RESTORE DATABASE WITH REPLACE

  • 772

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?

sql-server

2 Answers

  1. There's not an equivalent statement for CREATE DATABASE because you don't want someone overwriting a non-database file. SQL Server doesn't know what that file is left over from - it could be an OS file, for that matter. Allowing people to create databases and overwrite OS files would be a security vulnerability.

    • 2
  2. 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
    • 0