Help needed re-creating a db - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Help needed re-creating a db

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

Posted - 2010-11-12 : 13:52:24

you had better make sure when you do all of this work that you don't have a problem with the disk, or the disk is too, or tempdb is hosed, or master

Otherwise, you'll just be back at square 1 after alot of wasted time

MOO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 13:52:50

did you check the event logs?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources