updating custom data types?

[x6213]

Somehow all of the user defined data types and some of the lengths have been changed to the sql default datatypes in every column in one of our databases. I think it was caused by a conflicting software that shares the sql database. No one wants to take responsibility for this, but and I have the fun job of putting everything back.
Using a backup isn't a solution, since these changes happened months ago and haven't been noticed till now.
So I have to manually fix this ...
The problem is there is over 300 tables with roughly 100 columns in each.
any suggestions or advice?

 

[vongrunt]

> Somehow all of the user defined data types and some of the lengths have been changed to the sql default datatypes in every column in one of our databases.

Strange... once bound, user-defined types are hard to change/remove.

How about some "before/after" examples?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

get the old backup and restore it somewhere
download SQL compare trial version(

http://www.red-gate.com/products/SQL_Compare/index.htm)

run the compare against your restored DB and production DB tables
take a look at the differences and run the scrips or copy and paste and run them in QA



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[x6213]

Vongrunt, the correct one is the one on the right

SQLDenis, since this happened months ago, all current backups are like this.
all I have is a really old sample database(from 2001!) to compare with.

 

[vongrunt]

Hm... many things could do that.

For example, SELECT INTO does not copy UDTs; it generates original datatypes in target table (varchar(40) instead of T_VARCHAR_40)

DTS export/import wizard: Copy tables option will do that, but Copy objects won't.

I've also seen some 3rdparty tools/custom-made scripts that rely on INFORMATION_SCHEMA.COLUMNS.DATA_TYPE column instead of DOMAIN_NAME. Same effect.

Btw. is there any pattern/correlation between affected tables and their creation dates/times (use EM or sysobjects.crdate to check that)?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[x6213]

ok, it looks like the problem might have been caused when someone truncated the log file because of storage space issues a few months back.

I have tried the compare utility and it works great. but I'll have to run 900 sql scripts 300, for each table and three times each (one on a remote test system, one on an image of the server, and one on the actual server)

Is there a way that I can batch run the 300 .sql scripts rather than running one at a time?

 

[vongrunt]

> ok, it looks like the problem might have been caused when someone truncated the log file because of storage space issues a few months back.

Still strange...


> Is there a way that I can batch run the 300 .sql scripts rather than running one at a time?

This is trivial from command prompt, with FOR statement or shell extension a la 4NT. Or you can try something like this:

Open command prompt, go to directory with 300 .SQL scripts. Type:

dir *.sql /b > blah.bat

Open blah.bat with any editor capable of "column mode" selection (a la Textpad, UltraEdit, even VS/InterDev). At the end of every line insert osql stuff:

osql -Sserver -Uuser -Ppassword -ddbname <

The rest of line should have script name and nothing else.

Run blah.bat.

Maybe there are some other (GUI) ways to do that, dunno.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

> At the end of every line insert osql stuff:

Correction: not end, beginning... obviously

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)