Drop and Recreate

  • Comments posted to this topic are about the item Drop and Recreate

  • Early on I got in the habit of using Projects/Solutions in SSMS to store SQL scripts (yes, they're under source control), but must admit this article made me realize there are a number of other things I should include as well that I'd be hard-pressed to re-create manually. I could slog my way through it as our environment is small enough, but having scripts would be handy.

    Wayne Sheffield gave a nifty session on SSMS Tips & Tricks at SQLSaturday DC a couple weekends ago covering Projects/Solutions along with some other cool tricks. It's available on his blog if anyone is interested.

    ____________
    Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.

  • Most backup utilities are database centric. If the server must be restored, or one wants to spin up a UAT envrironment that closely mimics production, then restoring the databases is the easy part. Within SSMS there is no obvious way to script out server level DDL like settings and logins.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Eric M Russell (12/15/2014)


    Most backup utilities are database centric. If the server must be restored, or one wants to spin up a UAT envrironment that closely mimics production, then restoring the databases is the easy part. Within SSMS there is no obvious way to script out server level DDL like settings and logins.

    There isn't. I wish that Microsoft, or Red Gate, would link the server to VCS and get all the settings/options stored.

  • Steve Jones - SSC Editor (12/15/2014)


    Eric M Russell (12/15/2014)


    Most backup utilities are database centric. If the server must be restored, or one wants to spin up a UAT envrironment that closely mimics production, then restoring the databases is the easy part. Within SSMS there is no obvious way to script out server level DDL like settings and logins.

    There isn't. I wish that Microsoft, or Red Gate, would link the server to VCS and get all the settings/options stored.

    Even with the new contained databases the server settings and other server level objects should not be taken for granted.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Maybe SSMS needs to be rebuilt to fully support source control and versioning?

  • chrisn-585491 (12/15/2014)


    Maybe SSMS needs to be rebuilt to fully support source control and versioning?

    We have that in Visual Studio. Most of my T-SQL code changes are done in VS/TFS.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Go even one more step with DDL triggers. Log everything happening at DDL level from the servers themselves. This way you track normal releases and the dynamic changes to SQL objects by applications. Also knowing exactly what, when, where, who made the chages can be very useful.

  • The DDL triggers are a great idea. It covers things that are changed after the triggers are in place. What is missed is the base line that was in place before the triggers were installed.

    What is needed is a way to list out all of the settings including trace flags. Even if what was produced was rendered to *gulp* paper it would give you something to work with.

    ATBCharles Kincaid

  • chrisn-585491 (12/15/2014)


    Maybe SSMS needs to be rebuilt to fully support source control and versioning?

    SSMS for 2005 doesn't support it directly, but it's near-trivial to set up source control using Subversion and TortoiseSVN or something similar. Not quite as convenient as using something like SVN/Ankh or whatever, but it is certainly possible.

    I believe later versions of SSMS are based on Visual Studio, so I imagine source control integration is better supported.

    ____________
    Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.

  • I know we have most of what was mentioned in our version control. I'll have to check with our admin about the rest. Thanks for bringing it to my attention.

  • Database developers, at least in my corner of the universe, typically save our stored procedures as scripts which get checked into source control and maintained for subsequent versions. However, when a DBA changes the setting of a server configuration or trace flag, I'm sure most don't script it out and check it into source control or even associate it with a change order.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • chrisn-585491 (12/15/2014)


    Maybe SSMS needs to be rebuilt to fully support source control and versioning?

    Good god yes. Working with TFS and SSMS is just hideous beyond belief

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • lshanahan (12/15/2014)


    chrisn-585491 (12/15/2014)


    Maybe SSMS needs to be rebuilt to fully support source control and versioning?

    SSMS for 2005 doesn't support it directly, but it's near-trivial to set up source control using Subversion and TortoiseSVN or something similar. Not quite as convenient as using something like SVN/Ankh or whatever, but it is certainly possible.

    I believe later versions of SSMS are based on Visual Studio, so I imagine source control integration is better supported.

    Nope. 2008 R2 is still an ordeal that would make Pinhead wince

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • daxz (12/15/2014)


    Go even one more step with DDL triggers. Log everything happening at DDL level from the servers themselves. This way you track normal releases and the dynamic changes to SQL objects by applications. Also knowing exactly what, when, where, who made the chages can be very useful.

    Whilst a nice idea, I don't think that it is practical. Usually in source control we are only interested in versioning each submission otherwise we would be tracking every file changed. I, for one, would not be interested in the 10 intermediate changes to a stored procedure that occurred before the developer deemed it suitable for submission.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply