Copy objects Wizard - deleted data??

I have not used the copy objects wizard that much. I used it today to copy 4
views from my dev box to production. It copied the views, but also wiped out
my data in all the tables that the views are built around!!! In
production!!!!

Can someone provide me some insight into why this happened?

Thanks,
ChadChad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
> I have not used the copy objects wizard that much. I used it today to
> copy 4 views from my dev box to production. It copied the views, but
> also wiped out my data in all the tables that the views are built
> around!!! In production!!!!
> Can someone provide me some insight into why this happened?

Extremely nasty. I have not used the wizard in question myself, and I
think you understand why after this experience. It's a bit ironic: the
wizards are there to help, but you can only use them, if you know
exactly what they do, and in such case you may not need them.

Anyway my guess is that the wizard saw reason to recreate the underlying
tables as well; possibly because the defintion in production was different
from your dev box.

The correct way to deploy things in production is through change scripts
that are created from information in the version-control system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, very nasty. Luckily my hosting company (ReadyHosting) was able to
restore everything from their backup and the transaction logs.

Normally, whenever I make DB table changes I save the scripts and put them
in a "To Promote to Prod" directory, then use SQL Analyzer to apply those
changes to prod. But changes to views don't prompt for you to save these
changes as a script.

What specifically do you mean by "the version control system"? (As you can
tell by my question, I know just enough of SQL Server to be dangerous, so
any insight on how to handle version contol is appreciated.)

Thanks,
Chad

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9682EF2F57F5DYazorman@.127.0.0.1...
> Chad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
>> I have not used the copy objects wizard that much. I used it today to
>> copy 4 views from my dev box to production. It copied the views, but
>> also wiped out my data in all the tables that the views are built
>> around!!! In production!!!!
>>
>> Can someone provide me some insight into why this happened?
> Extremely nasty. I have not used the wizard in question myself, and I
> think you understand why after this experience. It's a bit ironic: the
> wizards are there to help, but you can only use them, if you know
> exactly what they do, and in such case you may not need them.
> Anyway my guess is that the wizard saw reason to recreate the underlying
> tables as well; possibly because the defintion in production was different
> from your dev box.
> The correct way to deploy things in production is through change scripts
> that are created from information in the version-control system.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Chad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
> Yes, very nasty. Luckily my hosting company (ReadyHosting) was able to
> restore everything from their backup and the transaction logs.
> Normally, whenever I make DB table changes I save the scripts and put them
> in a "To Promote to Prod" directory, then use SQL Analyzer to apply those
> changes to prod. But changes to views don't prompt for you to save these
> changes as a script.
> What specifically do you mean by "the version control system"? (As you can
> tell by my question, I know just enough of SQL Server to be dangerous, so
> any insight on how to handle version contol is appreciated.)

"version control system" or "source code control" is nothing specific
to SQL Server, but fundamentals of software engineering. In a version
control system, developers adds their files. Later a file may be
checked out, maybe by the same developer, maybe by someone else. The
person who checked out the file, performs some changes to it, and
then checks back in again, after proper testing.

When it's getting time to make a build for an integration test, someone
who is a "build master", "configuration manager" or similar puts some
label on all the most recent versions of files, to create a baseline.
During tests, bugs may be uncovered and fixed. The fixes can be inserted
into that baseline, or a new baseline be created.

Eventually, the thing is put into production and a baseline is created for
this. Now, development of 2.0 starts. However, there may be need to
fix bugs in production as well. Say that version 12 of file foo.cs
was in the shipment baseline. By the time a critical bug in production
is discovered, the file at version 14 for 2.0 development. But you
check out version 12, and fix that, and check it in as 12.1 - you
have now created a branch.

The exact terminology for these various actions are different from
product to product. The most commonly used version-control system
in the Microsoft world is Visual SourceSafe. It performs branching
different that about any other product. VSS has a lot of short-comings
as a version-control system, but it's easy to start with, and it's OK
for smaller teams.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||For a tool that links the drop/create scripts (for ALL database
objects) in any source control system to your development, test and
production databases have a look at DB Ghost (www.dbghost.com).

The approach is to regard the drop/create scripts as the only true
representation of the desired schema i.e. your 'source database'. Once
you have modifed the drop/create scripts DB Ghost will build a brand
new database from them in an extremely fast manner whilst taking care
of any dependencies. This a) verifies that no syntax or dependency
errors have been introduced and b) gives you a real source database to
use as the basis for a compare and upgrade of your actual target i.e.
the test or production database. DB Ghost does this also and creates a
rock solid delta script of the differences that is guaranteed to work
with no hand coded modifications.

What you end up with is a target database that matches a (labelled) set
of scripts under source control. If this approach is used for all
releases then a full audit trail of who changed what, why and when is
maintained in the source control system so it is easy to do reports
such as 'what changed between release X and release Y' or 'who first
changed sproc Z after release X'.

Relying on comments in sprocs etc. is a recipe for disaster in all but
the most disciplined of IT shops. Let your source control system do
the hard work for you and let DB Ghost handle all the SQL Server code.
It's called the DB Ghost Process and it can bring an amazing level of
quality to your deployments and code control in general.|||Erland,

Thanks for the explanation. I do have experience with source control tools
such as VSS and PVCS, but all have been for file/directory based source,
like VB. How do DB developers apply these principles (check in, check out,
etc.) to SQL Server? This is something I've curious about for a while now.

Thanks,
Chad

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96833C7C3BFCYazorman@.127.0.0.1...
> Chad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
>> Yes, very nasty. Luckily my hosting company (ReadyHosting) was able to
>> restore everything from their backup and the transaction logs.
>>
>> Normally, whenever I make DB table changes I save the scripts and put
>> them
>> in a "To Promote to Prod" directory, then use SQL Analyzer to apply those
>> changes to prod. But changes to views don't prompt for you to save these
>> changes as a script.
>>
>> What specifically do you mean by "the version control system"? (As you
>> can
>> tell by my question, I know just enough of SQL Server to be dangerous, so
>> any insight on how to handle version contol is appreciated.)
> "version control system" or "source code control" is nothing specific
> to SQL Server, but fundamentals of software engineering. In a version
> control system, developers adds their files. Later a file may be
> checked out, maybe by the same developer, maybe by someone else. The
> person who checked out the file, performs some changes to it, and
> then checks back in again, after proper testing.
> When it's getting time to make a build for an integration test, someone
> who is a "build master", "configuration manager" or similar puts some
> label on all the most recent versions of files, to create a baseline.
> During tests, bugs may be uncovered and fixed. The fixes can be inserted
> into that baseline, or a new baseline be created.
> Eventually, the thing is put into production and a baseline is created for
> this. Now, development of 2.0 starts. However, there may be need to
> fix bugs in production as well. Say that version 12 of file foo.cs
> was in the shipment baseline. By the time a critical bug in production
> is discovered, the file at version 14 for 2.0 development. But you
> check out version 12, and fix that, and check it in as 12.1 - you
> have now created a branch.
> The exact terminology for these various actions are different from
> product to product. The most commonly used version-control system
> in the Microsoft world is Visual SourceSafe. It performs branching
> different that about any other product. VSS has a lot of short-comings
> as a version-control system, but it's easy to start with, and it's OK
> for smaller teams.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Chad Richardson (chad@.NIXSPAM_chadrichardson.com) writes:
> Thanks for the explanation. I do have experience with source control tools
> such as VSS and PVCS, but all have been for file/directory based source,
> like VB. How do DB developers apply these principles (check in, check out,
> etc.) to SQL Server? This is something I've curious about for a while now.

They use - or at least should use - files. I've seen a whole lot of
questions on version control of SQL objects as this should be something
difficult or special. It isn't. Source code is source code, and should be
handled as such.

I guess people are tricked by tools that permit you to store things in the
database directly, point-and-click GUI:n for creating tables etc. But all
of that is really files.

The one gotcha there is if you use a tool like Query Analyzer for editing
your SQL objects, is that you disrupt the normal procedure which is
1) check out 2) edit 3) save 4) compile 5) test 6) back to 2 until it
works 7) check in. With a tool like QA, 3 is taken out of the chain, which
can lead to that what you check is not what you tested.

In our shop, we avoid this problem by using a third-party editor, Textpad.
It has no special features for SQL - but it is a better editor than QA.
From Textpad 3 and 4 is one key-click, as we can activate a command-line
from Textpad. We have our own load tool for quite a few bells and whistles,
but the tool could be command-line OSQL.

The thing people seem to want to do, is to take the SQL objects from
the database, but this is actually really wrong when you think of it.
If you work in VB, would get the input for the version-control system
by disassembling the object modules?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp