Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TSQLMan
Posting Yak Master
160 Posts |
 Posted - 2004-04-24 : 12:52:02
|
| I recently had the "screens" database for our accounting package become corrupted. I restored the latest backup. When one of the tables came back up, a field that was originally of the typeVARBINARY(3000) came back as the type of image. It is causing us a bit of a problem. How do I change the data type back with a minimum risk of losing data in that column? The data in the column appears to be identical in either data type. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-25 : 00:22:14
|
| You really need to see if you can find an earlier backup with the image stored correctly. The only difference between VARBINARY(3000) and IMAGE is the size. They are both binary fields. You can just create an image column and insert the data. When you try to pull up the images though, any image over 3000 will be corrupted. Make sense?Unfortunately, besides a restore there's no way to fix the problem you have unless all of your images are small enough to fit in the (3000) range.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2004-04-26 : 07:26:34
|
| Thanks, It is odd though that when I restored the database, all of the other tables in the database had this same field, and this particular table is the only one where the data type changed. Thanks again for the help. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-26 : 15:11:51
|
| No miracles...it was changed before the dump....How do you know it's different?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-26 : 15:18:19
|
| As Brett mentioned, the table was changed after the last full backup ran. It doesn't change on its own. What corruption signs did you see? Is it possible that someone deliberately corrupted the database? If so, then that someone could also have been the one that changed the table. It is very easy to corrupt a database if you've got sysadmin. How many people have sysadmin? Any disgruntled employees?Tara |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2004-04-26 : 15:24:53
|
| NO disgruntled employees, and 1 other person has sa rights to the database. When I restore the lastest backup of the screens database to my laptop is comes back as varchar(3000), when I restore it to the production server it comes up as type image. Maybe not a miracle, but during the restore to either the server or my laptop it changed, that much I do know. I think what I will do is re modify my screens on my backup server, and restore it from there.think that might work. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-26 : 16:24:30
|
| Well maybe I will believe in Miracles now....You sure we're talking about the excact same dump file...What's different between the environments?Brett8-) |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2004-04-26 : 18:26:29
|
| One is Windows2000 Server Running SQL 2000 SP3 the other is Windows XP Pro running MSDE 2000 SP3That is all I can think of. I am using the same full DB Backup in both cases. |
|
|
|
|
|
|
|
|
Change of Data Type