Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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
share|improve this answer
    
I used a slightly different method, but your thesis was correct. I took the database offline and then put it online. IF EXISTS (SELECT name FROM sys.databases WHERE name = N'eventManagement') BEGIN ALTER DATABASE [eventManagement] SET OFFLINE WITH ROLLBACK IMMEDIATE; ALTER DATABASE [eventManagement] SET ONLINE; DROP DATABASE [eventManagement]; END GO –  Justin Dearing Oct 8 '10 at 18:09
add comment

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.

share|improve this answer
    
Brent, Thanks for the insight as to why. That argument against such a feature makes sense. However, I must respectfully submit that the security risk can be mitigated on SQL Server 2k and greater. If you run SQL server as a limited access user, and strictly set filesystem permissions so that user only has write access to the database folder, the risk would be zero. I'm probably opening pandora's box here, but I think I might open a feature request for this. At the very least it would be an interesting discussion. –  Justin Dearing Oct 8 '10 at 17:58
    
I hear ya, but not everybody runs SQL Server as a limited access user and sets filesystem permissions correctly. –  Brent Ozar Oct 8 '10 at 18:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.