Track changes made to SQL Server

**Erland Sommarskog** · Feb 26 '08, 08:15 AM

Re: Track changes made to SQL Server

John Sheppard (spam@nospam.co m) writes:

Does anyone know of a way to track changes to an SQL Server database so
that I can easily run those changes at a later date?
>
That is, I want to make schema changes, and record those changes so that I
can execute them 6 months later on a copy of the orignal database.

Develop scripts and keep them under source control.

If you forgot to that, SQL Compare from Red Gate can be your rescue.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**xman** · Feb 27 '08, 07:35 PM

Re: Track changes made to SQL Server

On Feb 26, 3:04 am, Erland Sommarskog <esq...@sommars kog.sewrote:

John Sheppard (s...@nospam.co m) writes:

Does anyone know of a way to track changes to anSQLServerdata base so
that I can easily run those changes at a later date?

>

That is, I want to make schema changes, and record those changes so thatI
can execute them 6 months later on a copy of the orignal database.

>
Develop scripts and keep them under source control.
>
If you forgot to that,SQLCompare from Red Gate can be your rescue.
>
--
Erland Sommarskog,SQLS erverMVP, esq...@sommarsk og.se
>
Books Online forSQLServer200 5 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer200 0 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

As can xSQL Object from xSQL Software (http://www.xsqlsoftware.com)
and what's even better you may not have to pay a dime for it as it is
free for SQL Server Express and also free for other editions of SQL
Server with some limitations.

JC

**John Sheppard** · Feb 28 '08, 12:35 PM

Re: Track changes made to SQL Server

"xman" <xman@xsqlsoftw are.comwrote in message
news:a5d9c513-804d-4eb4-90c6-e7fe3696c2ed@n7 5g2000hsh.googl egroups.com...
On Feb 26, 3:04 am, Erland Sommarskog <esq...@sommars kog.sewrote:

John Sheppard (s...@nospam.co m) writes:

Does anyone know of a way to track changes to anSQLServerdata base so
that I can easily run those changes at a later date?

>

That is, I want to make schema changes, and record those changes so that
I
can execute them 6 months later on a copy of the orignal database.

>
Develop scripts and keep them under source control.
>
If you forgot to that,SQLCompare from Red Gate can be your rescue.
>
--
Erland Sommarskog,SQLS erverMVP, esq...@sommarsk og.se
>
Books Online forSQLServer200 5
athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer200 0
athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

>As can xSQL Object from xSQL Software (http://www.xsqlsoftware.com)
>and what's even better you may not have to pay a dime for it as it is
>free for SQL Server Express and also free for other editions of SQL
>Server with some limitations.
>
>JC

Hmm, this looks pretty decent, I'll check it on my database tommorow...see
if it drops tables or alters..drop is

Thanks!
John

**John Sheppard** · Feb 28 '08, 11:25 PM

Re: Track changes made to SQL Server

"John Sheppard" <nospam@spamspa m.comwrote in message
news:fq69ch059m @news1.newsguy. com...

>
"xman" <xman@xsqlsoftw are.comwrote in message
news:a5d9c513-804d-4eb4-90c6-e7fe3696c2ed@n7 5g2000hsh.googl egroups.com...
On Feb 26, 3:04 am, Erland Sommarskog <esq...@sommars kog.sewrote:

>John Sheppard (s...@nospam.co m) writes:

Does anyone know of a way to track changes to anSQLServerdata base so
that I can easily run those changes at a later date?

>>

That is, I want to make schema changes, and record those changes so
that I
can execute them 6 months later on a copy of the orignal database.

>>
>Develop scripts and keep them under source control.
>>
>If you forgot to that,SQLCompare from Red Gate can be your rescue.
>>
>--
>Erland Sommarskog,SQLS erverMVP, esq...@sommarsk og.se
>>
>Books Online forSQLServer200 5
>athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
>Books Online forSQLServer200 0
>athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

>

>>As can xSQL Object from xSQL Software (http://www.xsqlsoftware.com)
>>and what's even better you may not have to pay a dime for it as it is
>>free for SQL Server Express and also free for other editions of SQL
>>Server with some limitations.
>>
>>JC

>
Hmm, this looks pretty decent, I'll check it on my database tommorow...see
if it drops tables or alters..drop is
>
Thanks!
John

It's dropping tables and recreating them....I dont understand the use of
synching software that does that, I need the data intact :(

Thanks tho!
John

**John Sheppard** · Feb 28 '08, 11:25 PM

Re: Track changes made to SQL Server

"John Sheppard" <nospam@spamspa m.comwrote in message
news:fq69ch059m @news1.newsguy. com...

>
"xman" <xman@xsqlsoftw are.comwrote in message
news:a5d9c513-804d-4eb4-90c6-e7fe3696c2ed@n7 5g2000hsh.googl egroups.com...
On Feb 26, 3:04 am, Erland Sommarskog <esq...@sommars kog.sewrote:

>John Sheppard (s...@nospam.co m) writes:

Does anyone know of a way to track changes to anSQLServerdata base so
that I can easily run those changes at a later date?

>>

That is, I want to make schema changes, and record those changes so
that I
can execute them 6 months later on a copy of the orignal database.

>>
>Develop scripts and keep them under source control.
>>
>If you forgot to that,SQLCompare from Red Gate can be your rescue.
>>
>--
>Erland Sommarskog,SQLS erverMVP, esq...@sommarsk og.se
>>
>Books Online forSQLServer200 5
>athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
>Books Online forSQLServer200 0
>athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

>

>>As can xSQL Object from xSQL Software (http://www.xsqlsoftware.com)
>>and what's even better you may not have to pay a dime for it as it is
>>free for SQL Server Express and also free for other editions of SQL
>>Server with some limitations.
>>
>>JC

>
Hmm, this looks pretty decent, I'll check it on my database tommorow...see
if it drops tables or alters..drop is
>
Thanks!
John

I tell a lie! It does keep the data intact, I just had to make it sync the
data too...yay! This will do what I want....Thankyo u!

**John Sheppard** · Feb 28 '08, 11:25 PM

Re: Track changes made to SQL Server

"John Sheppard" <nospam@spamspa m.comwrote in message
news:fq69ch059m @news1.newsguy. com...

>
"xman" <xman@xsqlsoftw are.comwrote in message
news:a5d9c513-804d-4eb4-90c6-e7fe3696c2ed@n7 5g2000hsh.googl egroups.com...
On Feb 26, 3:04 am, Erland Sommarskog <esq...@sommars kog.sewrote:

>John Sheppard (s...@nospam.co m) writes:

Does anyone know of a way to track changes to anSQLServerdata base so
that I can easily run those changes at a later date?

>>

That is, I want to make schema changes, and record those changes so
that I
can execute them 6 months later on a copy of the orignal database.

>>
>Develop scripts and keep them under source control.
>>
>If you forgot to that,SQLCompare from Red Gate can be your rescue.
>>
>--
>Erland Sommarskog,SQLS erverMVP, esq...@sommarsk og.se
>>
>Books Online forSQLServer200 5
>athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
>Books Online forSQLServer200 0
>athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

>

>>As can xSQL Object from xSQL Software (http://www.xsqlsoftware.com)
>>and what's even better you may not have to pay a dime for it as it is
>>free for SQL Server Express and also free for other editions of SQL
>>Server with some limitations.
>>
>>JC

>
Hmm, this looks pretty decent, I'll check it on my database tommorow...see
if it drops tables or alters..drop is
>
Thanks!
John

Ahh dang actually no this wont do what I want...:(

I think a transaction logger is gonna have to be the go...

**Mork69** · Feb 29 '08, 08:25 AM

Re: Track changes made to SQL Server

You should seriously consider keeping your scripts under source
control as Erland has suggested, especially if you will end up working
on the database with other developers. It's a great way to keep
everyone's activities in sync.

Check out DB Ghost (http://www.dbghost.com) - we've been doing this
for about 5 year's now and Microsoft, Red Gate and xSQL have now also
realised what we've been saying makes sense and incorporated our
thinking into theor products i.e. the approach has now been validated
across the board so you really should give it some thought.

**Erland Sommarskog** · Feb 29 '08, 10:25 PM

Re: Track changes made to SQL Server

John Sheppard (spam@nospam.co m) writes:

It's dropping tables and recreating them....I dont understand the use of
synching software that does that, I need the data intact :(

There is nothing wrong as such with dropping and recreating tables. For
some changes this is necessary, as ALTER TABLE cannot do everything.

But of course, the tool needs to cater for the data being copied over
to the new table. And of course the tools need to do this safely, and
make sure that indexes, triggers etc are restored. All and all, it's more
complex and risky. But it is a concept that a tool has to master. As it
is for someone who is working a lot with table changes, because you
will run into the situation sooner or later.

I think a transaction logger is gonna have to be the go...

And capture all sorts of junk commands that you issue? Why not just Profiler
or a server-side trace instead?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**John Sheppard** · Mar 3 '08, 08:25 AM

Re: Track changes made to SQL Server

"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns9A53ED9 8F906EYazorman@ 127.0.0.1...

John Sheppard (spam@nospam.co m) writes:

>It's dropping tables and recreating them....I dont understand the use of
>synching software that does that, I need the data intact :(

>
There is nothing wrong as such with dropping and recreating tables. For
some changes this is necessary, as ALTER TABLE cannot do everything.
>
But of course, the tool needs to cater for the data being copied over
to the new table. And of course the tools need to do this safely, and
make sure that indexes, triggers etc are restored. All and all, it's more
complex and risky. But it is a concept that a tool has to master. As it
is for someone who is working a lot with table changes, because you
will run into the situation sooner or later.
>

>I think a transaction logger is gonna have to be the go...

>
And capture all sorts of junk commands that you issue? Why not just
Profiler
or a server-side trace instead?
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

What do you mean profile on a server-side trace?

**John Sheppard** · Mar 3 '08, 08:25 AM

Re: Track changes made to SQL Server

"Mork69" <mleach@bigfoot .comwrote in message
news:8c6075cd-48dd-4a74-bdc5-7088ed5bad4c@h2 5g2000hsf.googl egroups.com...

You should seriously consider keeping your scripts under source
control as Erland has suggested, especially if you will end up working
on the database with other developers. It's a great way to keep
everyone's activities in sync.
>
Check out DB Ghost (http://www.dbghost.com) - we've been doing this
for about 5 year's now and Microsoft, Red Gate and xSQL have now also
realised what we've been saying makes sense and incorporated our
thinking into theor products i.e. the approach has now been validated
across the board so you really should give it some thought.

I'll tackle that when we get to it, for now I'll be the only one doing the
changes and I just want an easy way to create scripts instead of doing it
all manually...

My problem with source control is that it takes alot to learn and get setup
etc...its over kill for us, we only have 2 developers...we have enough
trouble with visual source safe lol (ugg)

Thanks for the suggestion tho, I'll check it out!
John

**Erland Sommarskog** · Mar 3 '08, 10:45 PM

Re: Track changes made to SQL Server

John Sheppard (nospam@spamspa m.com) writes:

What do you mean profile on a server-side trace?

In the SQL Server program group, you find Profiler under Performance
tools. This tool permits you trace a whole bunch of things that goes
on in SQL Server. If you just press OK on all buttons that comes up,
you will see all command batches that are sent to the server. You can
set up filter to trace only a certain connection. And a lot more.

The Profiler is just the front-end. The trace engine itself lives in
SQL Server, and you can access it directly by setting up a server-side
traces. (The easiest way to do that is to set up the trace in Profiler and
then export the trace.) Server-side trace is the recommended for long-term
traces, and also when you trace in loaded production environments, as
it takes less load than a Profiler trace.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**John Sheppard** · Mar 5 '08, 08:35 AM

Re: Track changes made to SQL Server

"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns9A56F06 EF195DYazorman@ 127.0.0.1...

John Sheppard (nospam@spamspa m.com) writes:

>What do you mean profile on a server-side trace?

>
In the SQL Server program group, you find Profiler under Performance
tools. This tool permits you trace a whole bunch of things that goes
on in SQL Server. If you just press OK on all buttons that comes up,
you will see all command batches that are sent to the server. You can
set up filter to trace only a certain connection. And a lot more.
>
The Profiler is just the front-end. The trace engine itself lives in
SQL Server, and you can access it directly by setting up a server-side
traces. (The easiest way to do that is to set up the trace in Profiler and
then export the trace.) Server-side trace is the recommended for long-term
traces, and also when you trace in loaded production environments, as
it takes less load than a Profiler trace.
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

ic! Thats what I want!

Now you're gonna tell me that it doesnt come with SQL Server Express, right?
:) Im pretty sure it doesnt as theyre not there....

I'll look into it

Thanks for your help man, I'll see what I can find...
John

**Erland Sommarskog** · Mar 5 '08, 10:15 PM

Re: Track changes made to SQL Server

John Sheppard (nospam@spamspa m.com) writes:

ic! Thats what I want!
>
Now you're gonna tell me that it doesnt come with SQL Server Express,
right?
>:) Im pretty sure it doesnt as theyre not there....

Correct. Profiler does not come with SQL 2005 Express. But I think the
server-side trace routines does. But admittedly they are a lot more
difficult to use on their own.

Now, the price tag for a license of Developer Edition is around 50 USD,
and since you are doing this for development, that would be sufficient for
you.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**John Sheppard** · Mar 6 '08, 09:15 AM

Re: Track changes made to SQL Server

"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns9A58EB1 6F40F9Yazorman@ 127.0.0.1...

John Sheppard (nospam@spamspa m.com) writes:

>ic! Thats what I want!
>>
>Now you're gonna tell me that it doesnt come with SQL Server Express,
>right?
>>:) Im pretty sure it doesnt as theyre not there....

>
Correct. Profiler does not come with SQL 2005 Express. But I think the
server-side trace routines does. But admittedly they are a lot more
difficult to use on their own.
>
Now, the price tag for a license of Developer Edition is around 50 USD,
and since you are doing this for development, that would be sufficient for
you.
>
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Ic...hmm I think I have a developer edition already...we have an action pack
subscription... .its a full job in itself just wading through all that
liscencing stuff....we have a full enterprise copy of sql server that came
with it....Im pretty sure we're not liscenced to use that tho :)

Im sure Im not the only one that gets frustrated with the ridiculous
complexity of liscencing tho...I seem to do everything BUT write code...

Installing sql express already broke my visual studio once...not keen to
repeat that...k im really frustrated right now...

Thanks man, Ill look into the developer edition when im little more calm,
see where that takes me
John

Track changes made to SQL Server

Track changes made to SQL Server

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment