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?