Drop and Recreate

Question

Drop and Recreate

Steve Jones - SSC Editor

SSC Guru

Points: 736222
More actions
December 13, 2014 at 11:20 am

#301880

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

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

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

shoestringdba SSCertifiable Points: 6226 More actions · Answer 1

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.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 3

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.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 4

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

chrisn-585491 SSCoach Points: 16006 More actions · Answer 5

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

Eric M Russell SSC Guru Points: 125561 More actions · Answer 6

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

daxz SSC Veteran Points: 264 More actions · Answer 7

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.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 8

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

shoestringdba SSCertifiable Points: 6226 More actions · Answer 9

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.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

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.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 11

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

andrew gothard SSChampion Points: 12314 More actions · Answer 12

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.

andrew gothard SSChampion Points: 12314 More actions · Answer 13

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.

Gary Varga SSC Guru Points: 82166 More actions · Answer 14

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!!!