Track changes made to SQL Server

[John Sheppard]

Hello there,

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.

Thank you kindly for any ideas anyone may have
John

[Erland Sommarskog]

John Sheppard (sp**@nospam.com) 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, es****@sommarskog.se

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

[xman]

On Feb 26, 3:04*am, Erland Sommarskog <[e-mail]:

John Sheppard ([e-mail]) writes:

Does anyone know of a way to track changes to anSQLServerdatabase 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,SQLComparefrom Red Gate can be your rescue.

--
Erland Sommarskog,SQLServerMVP, [e-mail]

Books Online forSQLServer2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/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]

"xman" <xm**@xsqlsoftware.comwrote in message
news:a5**********************************@n75g2000 hsh.googlegroups.com...
On Feb 26, 3:04 am, Erland Sommarskog <[e-mail]:

John Sheppard ([e-mail]) writes:

Does anyone know of a way to track changes to anSQLServerdatabase 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,SQLComparefrom Red Gate can be your rescue.

--
Erland Sommarskog,SQLServerMVP, [e-mail]

Books Online forSQLServer2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/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]

"John Sheppard" <no****@spamspam.comwrote in message
news:fq********@news1.newsguy.com...

>
"xman" <xm**@xsqlsoftware.comwrote in message
news:a5**********************************@n75g2000 hsh.googlegroups.com...
On Feb 26, 3:04 am, Erland Sommarskog <[e-mail]:

>John Sheppard ([e-mail]) writes:

Does anyone know of a way to track changes to anSQLServerdatabase 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,SQLComparefrom Red Gate can be your rescue.

--
Erland Sommarskog,SQLServerMVP, [e-mail]

Books Online forSQLServer2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/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]

"John Sheppard" <no****@spamspam.comwrote in message
news:fq********@news1.newsguy.com...

>
"xman" <xm**@xsqlsoftware.comwrote in message
news:a5**********************************@n75g2000 hsh.googlegroups.com...
On Feb 26, 3:04 am, Erland Sommarskog <[e-mail]:

>John Sheppard ([e-mail]) writes:

Does anyone know of a way to track changes to anSQLServerdatabase 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,SQLComparefrom Red Gate can be your rescue.

--
Erland Sommarskog,SQLServerMVP, [e-mail]

Books Online forSQLServer2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/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....Thankyou!

[John Sheppard]

"John Sheppard" <no****@spamspam.comwrote in message
news:fq********@news1.newsguy.com...

>
"xman" <xm**@xsqlsoftware.comwrote in message
news:a5**********************************@n75g2000 hsh.googlegroups.com...
On Feb 26, 3:04 am, Erland Sommarskog <[e-mail]:

>John Sheppard ([e-mail]) writes:

Does anyone know of a way to track changes to anSQLServerdatabase 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,SQLComparefrom Red Gate can be your rescue.

--
Erland Sommarskog,SQLServerMVP, [e-mail]

Books Online forSQLServer2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/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]

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]

John Sheppard (sp**@nospam.com) 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, es****@sommarskog.se

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

[John Sheppard]

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

John Sheppard (sp**@nospam.com) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/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]

"Mork69" <ml****@bigfoot.comwrote in message
news:8c**********************************@h25g2000 hsf.googlegroups.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]

John Sheppard (no****@spamspam.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, es****@sommarskog.se

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

[John Sheppard]

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

John Sheppard (no****@spamspam.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/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]

John Sheppard (no****@spamspam.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, es****@sommarskog.se

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

[John Sheppard]

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

John Sheppard (no****@spamspam.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/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

[vmnvmcxbv]

What are these features you mention that are incorporated in MS SQL? Can you
elaborate

"Mork69" <ml****@bigfoot.comwrote in message
news:8c**********************************@h25g2000 hsf.googlegroups.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.

[John Sheppard]

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

John Sheppard (no****@spamspam.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, es****@sommarskog.se

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

I've installed the developer edition and I dont have a Microsoft SQL
Server -performance tools -profiler in the start menu :(

Why am i having no luck, this is so frustrating...:(

[John Sheppard]

"John Sheppard" <no****@spamspam.comwrote in message
news:fq********@news3.newsguy.com...

>
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

>John Sheppard (no****@spamspam.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/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

Ok Finally I got it all up and working. I think profiler should do the job,
just have to find the correct events to trace

Thank you
John

[John Sheppard]

"John Sheppard" <sp**@nospam.comwrote in message
news:fr*********@news2.newsguy.com...

>
"John Sheppard" <no****@spamspam.comwrote in message
news:fq********@news3.newsguy.com...

>>
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

>>John Sheppard (no****@spamspam.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/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

Ok Finally I got it all up and working. I think profiler should do the
job, just have to find the correct events to trace

Thank you
John

hmm, ok, I've found out about DDL triggers. These are the go...much easier
than profiler...

Having a bit of trouble with those but I'll start a new thread if I get
really stuck...

Here is a good article on it
http://www.sqlteam.com/article/using...schema-changes

Thanks you all very much for your help...
Kind regards
John

[Erland Sommarskog]

John Sheppard (no****@spamspam.com) writes:

hmm, ok, I've found out about DDL triggers. These are the go...much easier
than profiler...

True, you could set them up and siphon off everything into a table,
and you don't have to damn yourself for not running profiler.

I don't remember on the top of my head if the XML returned by the
eventdata() function includes the statement that fired the trigger,
but if it does, it's a walk in the park.

Still, keep in mind that you must have a reference database so that
you can test the script however you compose it!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[John Sheppard]

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

John Sheppard (no****@spamspam.com) writes:

>hmm, ok, I've found out about DDL triggers. These are the go...much
easier
than profiler...

True, you could set them up and siphon off everything into a table,
and you don't have to damn yourself for not running profiler.

I don't remember on the top of my head if the XML returned by the
eventdata() function includes the statement that fired the trigger,
but if it does, it's a walk in the park.

Still, keep in mind that you must have a reference database so that
you can test the script however you compose it!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

They do...I just have to figure out which events to put on the trigger and I
think its all good to go..

Thanks heaps for all your help man...
Much appreciated
John