| Author |
Topic |
|
Del511
Starting Member
8 Posts |
 Posted - 2010-11-08 : 15:47:35
|
Hello All!
I have a corrupt db that has irreparable stat errors in sysindexes. I am trying to use the SQL 2000 scripting tool to recreate the tables and stored procedures along with the permissions. I have been semi-successful creating the tables. I say this because in the original db there are 1226 items in "Tables" and when I use the scripting tool and run it on a newly created db I end up with 1500 items in 'tables" and the stored procedures fail to create. I must be doing something wrong. The script itself takes 63 pages and I know no one wants to look at that; therefore, I would like someone to help me use the tool the right way and show me how to get the data from the original db into the new one. After this I will upgrade the db to SQL 2005, no more sysindexes! Thanks!
-Blessings :-) |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-08 : 16:34:58
|
In many cases those 'irreparable' stats errors can be fixed. If you want to try, run the following and post the results.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
sysindexes is still in SQL 2005, it's just a view over other system tables. There are still system tables holding all the index-related info
--
Gail Shaw
SQL Server MVP |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-08 : 16:37:09
|
you don't have backups?
sql2005 isn't going to make sysindexes go away exactly. it is replaced with sys.indexes and there is still a compatibility view called sysindexes, but sys.indexes can become corrupt in the same way that sql 2000 sysindexes can.
are you sure you don't just have a couple of indexes that can be dropped, then recreated?
perhaps you might post the output of DBCC CHECKDB here
 -- and Gail beat me to it  |
|
|
|
Del511
Starting Member
8 Posts |
Posted - 2010-11-08 : 16:58:26
|
Hi again!
Gail told me to try that in a previous post and it did not work:
SELECT object_name(id) as TableName, name as IndexName, IndexProperty(id, name, 'IsStatistics') AS IsColumnStatistics
FROM sysindexes
WHERE (id = 540633069 and indid = 10)
drop statistics desktop_tasks._WA_Sys_S_TASK_CD_203967ED
After that I got:
(1 row(s) affected)
Server: Msg 3701, Level 11, State 6, Line 5
Cannot drop the statistics 'desktop_tasks._WA_Sys_S_TASK_CD_203967ED', because it does not exist in the system catalog.
So, I followed Gail's advice and am trying to re-create the database. I'm having a terrible time but I am learning a lot during this ordeal. Russll, no backups exist without the corruption. This has been going on for a long time.
-Blessings :-) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-09 : 02:37:04
|
Ah, ok. I forget all the corruption questions I deal with.
67 pages? Are you scripting the data as well? If so, don't. Export that (bcp)
--
Gail Shaw
SQL Server MVP |
|
|
|
Del511
Starting Member
8 Posts |
Posted - 2010-11-09 : 11:30:44
|
You guys are the bomb! <-- That's a compliment. LOL Is there an article with steps that I can read? This is what I have done: Using the "Generate SQL Scripts" tool
On the "General" tab --> Objects to script --> Selected the following:
1. All tables
2. All stored procedures
On the "Formatting" tab --> Selected the following:
1. Generate the CREATE <objects> command for each object
2. Generate scripts for all dependent objects
On the "Options" tab --> Selected the following:
Security Scripting Options
1. Script database users and database roles
2. Script SQL Server logins (Windows and SQL Server logins)
3. Script object-level permissions
Table Scripting Options
1. Script indexes
2. script full-text indexes
3. script triggers
4. Script primary keys, foreign keys, defaults, and check constraints
Then I build the script and run it against a newly created db. I need help getting it to work properly. If the tables and stored procedures were re-created then I was going to use DTS to import the data or use a bunch of insert statements. I'm a systems engineer with the belief that if I can read then I can do but I'm getting a little discouraged. I hate asking but can someone please give me verbose instructions on how to do this? Please? :-)
-Blessings :-) |
|
|
|
Del511
Starting Member
8 Posts |
Posted - 2010-11-12 : 13:40:38
|
Thank you! I did as you suggested and all is good! Thanks agin!
-Blessings :-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Help needed re-creating a db