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

John Sheppard (spam@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, esquel@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

On Feb 26, 3:04*am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> John Sheppard (s...@nospam.com) 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, esq...@sommarskog.se
>
> 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

"xman" <xman@xsqlsoftware.com> wrote in message
news:a5d9c513-804d-4eb4-90c6-e7fe3696c2ed@n75g2000hsh.googlegroups.com...
On Feb 26, 3:04 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> John Sheppard (s...@nospam.com) 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, esq...@sommarskog.se
>
> 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" <nospam@spamspam.com> wrote in message
news:fq69ch059m@news1.newsguy.com...
>
> "xman" <xman@xsqlsoftware.com> wrote in message
> news:a5d9c513-804d-4eb4-90c6-e7fe3696c2ed@n75g2000hsh.googlegroups.com...
> On Feb 26, 3:04 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
>> John Sheppard (s...@nospam.com) 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, esq...@sommarskog.se
>>
>> 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" <nospam@spamspam.com> wrote in message
news:fq69ch059m@news1.newsguy.com...
>
> "xman" <xman@xsqlsoftware.com> wrote in message
> news:a5d9c513-804d-4eb4-90c6-e7fe3696c2ed@n75g2000hsh.googlegroups.com...
> On Feb 26, 3:04 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
>> John Sheppard (s...@nospam.com) 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, esq...@sommarskog.se
>>
>> 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" <nospam@spamspam.com> wrote in message
news:fq69ch059m@news1.newsguy.com...
>
> "xman" <xman@xsqlsoftware.com> wrote in message
> news:a5d9c513-804d-4eb4-90c6-e7fe3696c2ed@n75g2000hsh.googlegroups.com...
> On Feb 26, 3:04 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
>> John Sheppard (s...@nospam.com) 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, esq...@sommarskog.se
>>
>> 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...

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 (spam@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, esquel@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

"Mork69" <mleach@bigfoot.com> wrote in message
news:8c6075cd-48dd-4a74-bdc5-7088ed5bad4c@h25g2000hsf.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