MSSQL SQLCMD and drop-and-create scripts


  • I survived the hour long Uno hand

    So I'm trying to create some drop-and-create scripts for automatically generating a portion of our multi-database setup (just enough to test a given application), which would be run before running a "fill the database with known sample data" script before running a set of automated tests.

    I'm basically trying to drop the entire database and then recreate it. However, I keep getting

    Cannot drop database "[redacted]" because it is currently in use.

    Once I get that error, it's consistently appearing for the same database until I kill the sql process using that database or (sometimes) restart SQL Management Studio; however, it's not always the same database that triggers the error. I am already doing my drops like this:

    IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '[redacted]') BEGIN
    ALTER DATABASE [redacted] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [redacted]
    END
    

    and that doesn't seem to be helping. What am I doing wrong here?



  • does the SET SINGLE_USER command block?

    also, does it need to have a flag set to kill all connections?

    I know from using the UI to do these commands that aforementioned option exists, and that it takes a while to take it offline (set single user) which it might be doing asynchronously which would be another possible reason for failure.

    Am I helping?

    Penis.



  • This post is deleted!

  • I survived the hour long Uno hand


  • I survived the hour long Uno hand

    Maybe I just need a "GO" after the SET SINGLE-USER?

    WRONG.

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near 'IMMEDIATE'.
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near 'END'.
    Msg 1801, Level 16, State 3, Line 1



  • @Yamikuronue said:

    WITH ROLLBACK IMMEDIATE

    this element of your existing command already does everything I suggested.


    Filed Under: I am a waste of skin.



  • is GO correctly set as your command terminus?


  • I survived the hour long Uno hand

    There were suggestions on the web to take it offline, then back online, but then someone said "Don't do that, that's stupid, just set single-user" and then that doesn't work


  • I survived the hour long Uno hand

    PRINT 'CREATING DATABASE [Redacted]'
    IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '[Redacted]') BEGIN
    ALTER DATABASE [Redacted]SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    DROP DATABASE [Redacted]
    END
    GO
    CREATE DATABASE [Redacted]
    GO

    Previous revison:

    PRINT 'CREATING DATABASE [Redacted]'
    IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '[Redacted]') BEGIN
    ALTER DATABASE [Redacted]SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [Redacted]
    END
    GO
    CREATE DATABASE [Redacted]
    GO


  • @Yamikuronue said:

    IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '[redacted]') BEGIN
    ALTER DATABASE [redacted] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    DROP DATABASE [redacted]
    END

    is that what you tried?



  • I am going to ask the obvious and dumb question so don't get mad, but it's not in any of your script samples above.

    You're not using the database you're trying to change mode of or drop, are you?

    Try just adding a USE MASTER above that block of code.

    Edit: unless you have special "stuff" in the database that differs from the defaults, different auto-grow rules or collation or what-not, you could also just leave the database alone and drop/recreate everything in it instead. SQL is kind of designed with the assumption that databases last forever. It has ways of dropping them but as you can see they kind of suck and are weird.



  • Are you connecting directly to the drop database target? I don't use the cli, but maybe there's a windows user lock happening when you connect. Just drop database from master.?


  • I survived the hour long Uno hand

    It looks like we have a winner. I'm used to not using anything by default but I guess it defaults to whatever I used last :( At least that explains why it's a different DB each time.

    Thanks :) this was driving me nuts all day on Friday.



  • glad to have contributed nothing!


    Filed Under: Shame deeper than a lubricated fist.



  • Ninja'd by blakey by like 15 seconds! Mobile typing is hard.



  • I actually typed that above then deleted it because I thought it was so basic it would be insulting to ask.



  • Nice one blakey. I was going to suggest that too, as I got hit by that one in similar circumstances. I actually added a call to kill all existing connections and then wondered why my script kept getting disconnected.

    Live and learn.


  • I survived the hour long Uno hand

    Nah, l legit have no business working with SQL Server, I have no idea what I'm doing :) I get relational databases, but Microsoft of course has to "improve" on everything by adding all kinds of extra shit on top of the basic functionality that I don't understand because my training was done with MySQL (and Access but I try to purge that course from my memory as much as possible)



  • You have no proof sir! (Just don't click the undelete flag! )

    Conceptually, it's still a little odd because generally the sql server is one file and contains the definitions in their entirety, unless you have it set to split databases across locations. So being connected to the database shouldn't matter, it should shift you to master and drop anyway.

    Unless it's another soft safeguard 'did you REALLY mean database, Mister? '

    which, if it's prod makes sense.


  • Discourse touched me in a no-no place

    @blakeyrat's point about dropping the objects in the database rather than dropping the database is a good one though. That's how I've always seen it done before, never dropping the actual database just to recreate it.



  • if in the future you find yourself trying to do the same things with oracle, you will wish for the simplicity and stability of microsoft's setup.



  • QA can be faster and more complete with a complete purge and repopulate, but it's only a valid process in New environments, otherwise you should be testing production processes.


  • I survived the hour long Uno hand

    I'm basically doing purge-and-repopulate to abstract out the data layer so we have a consistent baseline of data for testing. I'm only populating a portion of the database, mostly as an exercise to help identify what portions are required for each application, so I'm dropping the whole schema and recreating only the bits that the application under test needs in a shared database that is used by about a dozen web apps. Ideally we'll eventually adjust our database setup to be able to tell right away who uses what tables



  • Yeah, the only thing that's really attached to the database itself instead of any of its subobjects is: 1) the default collation, 2) the auto-grow rules. AFAIK. Other than that, a database is basically just a namespace for other "stuff" to live in.

    Only thing I'd recommend is after running your script a few times, check the physical storage behind the SQL Server to be sure you aren't getting redacteddatabase1.mdb, redacteddatabase2.mdb, redacteddatabase3.mdb. (That is, make sure the delete command is actually removing the files.)



  • Personally I don't really like that idea, it's your environment so you can do what you want, but you should be testing the alter scripts and such. Drop and recreate makes testing those changes difficult.

    In the other sql thread I posted something that gets all tables and all columns. If it's useful to you I can modify it to get all tables so you can have a mapping session for your groups of official owners.


  • ♿ (Parody)

    @Matches said:

    Personally I don't really like that idea, it's your environment so you can do what you want, but you should be testing the alter scripts and such. Drop and recreate makes testing those changes difficult.

    It's obviously important to check that sort of thing, but it's not obvious that they aren't doing that, too.



  • Correct, but when you drop and recreate things, unless you are taking meticulous care to preserve the original table state it is quite easy to have a working table and a bad script.

    Drop recreate is dicey in a qa environment if you don't know what you're doing (which has already been claimed) - by the same token, stacked alters between releases are dicey too.


  • I survived the hour long Uno hand

    The vast majority of the data in our SQL server db is migrated from our backend database to this web-specific database via a plethora of scripts, which is what I'm bypassing by loading data directly.

    The web CRUD operations all use stored procs which are tested during other testing processes, this is for web functional testing.


  • BINNED

    @blakeyrat said:

    I actually typed that above then deleted it because I thought it was so basic it would be insulting to ask.

    Same here, but I didn't even type it.

    @Yamikuronue said:

    Nah, l legit have no business working with SQL Server, I have no idea what I'm doing

    Actually, from reading your post, you seem to be more qualified that most of the database developer/DBA candidates I've interviewed.


  • Discourse touched me in a no-no place

    @antiquarian said:

    Actually, from reading your post, you seem to be more qualified that most of the database developer/DBA candidates I've interviewed.

    QFT



  • I know your problem was fixed but...

    I think I'm right in saying that SET SINGLE_USER does not guarantee that you will be the single user that can access the DB.

    Personally, I use set offline with rollback immediate followed by drop. No need to put it online again just to nuke it.



  • @dkf said:

    antiquarian:
    Actually, from reading your post, you seem to be more qualified that most of the database developer/DBA candidates I've interviewed.

    QFT

    Me: So on a scale of 1 to 5, how would you rate your SQL Server skills?
    Interviewee: Oh, 5, definitely!
    Me: Okay, here's a sample schema. Can you write a query to list all rows in table A that have column status = XYZ and do not have any matching rows in table B?
    Interviewee: Uhhhh....



  • @RTapeLoadingError said:

    I think I'm right in saying that SET SINGLE_USER does not guarantee that you will be the single user that can access the DB.

    You are, there's a tiny but non-zero chance that the next process to connect to the DB after the mode switch will be someone other than you.



  • @RTapeLoadingError said:

    Personally, I use set offline with rollback immediate followed by drop. No need to put it online again just to nuke it.

    If you drop offline databases, SQL won't erase their .mbd files, from my experience. Not sure why.



  • @Matches said:

    Conceptually, it's still a little odd because generally the sql server is one file and contains the definitions in their entirety, unless you have it set to split databases across locations. So being connected to the database shouldn't matter, it should shift you to master and drop anyway

    You do know that SQL Server has it's own locking system and doesn't rely on the file system locking mechanism in any way, right? Having a database as your current database context puts a shared lock on the database object. Dropping a database requires an exclusive lock.


Log in to reply