Post reply

Help needed to make an identical copy of a db in SQL 2000

  • November 10, 2010 at 2:19 pm

    #241079

    Hello all!

    I need to re-create a db that has irreparable corruption in the sysindexes table (stat errors) and no good backups for the last 2 1/2 years. (<--Long story, if interested I will tell :-)) This is what I did so far:

    Created a new db

    Used the Generate SQL script tool on the original (corrupt) db to copy all the tables, stored procedures, etc...

    Ran that script against the new db and it looks as if everything copied over EXCEPT the data

    I tried using DTS to copy the data over and it keeps failing. If I deselect all the security options the tool runs but no data is copied over. Is there a T-SQL way and/or a more stable way to copy the data from the original tables into the new ones?

  • November 10, 2010 at 2:52 pm

    #1249186

    You should have posted the question in the corruption forum...


    Pradeep Singh

  • November 10, 2010 at 4:39 pm

    #1249210

    Oops misread what version of SQL being used.

    Sorry about that.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • November 11, 2010 at 1:57 am

    #1249320

    Hi..

    how many tables are there in corruption db..

    if you are access the query analyzer in corruption db & if its less..

    then you can write a insert query for the following format..

    select ' insert into tablename (col1,col2,col3) values (' +''''+col1+''''+','+''''+col2+''''+','+''''+col3+''''+)'

    Execute the above query ,copy the result and paste it into new db environment and run it..

    Regards

    Balaji.G

  • November 11, 2010 at 2:25 am

    #1249341

    I'm going to make a simple really painful statement.. Your data will not all be recoverable.. Some may but not all. Tables that are not corrupted *may* be able to be read out and saved, ones with corruption may be repaired, but if it is that bad of shape you are going to lose A LOT..

    You have the structure and the code, copy out the data one table at a time..

    I'm sorry for your loss.

  • November 11, 2010 at 2:28 am

    #1249347

    Elliott Whitlow (11/11/2010)

    I'm going to make a simple really painful statement.. Your data will not all be recoverable.. Some may but not all. Tables that are not corrupted *may* be able to be read out and saved, ones with corruption may be repaired, but if it is that bad of shape you are going to lose A LOT..

    In this case, not so (It's a followon from a previous question). The corruption is in sysindexes, is limited to statistics objects but is not repairable.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 11, 2010 at 2:33 am

    #1249354

    Then I stand corrected..

    CEWII

  • November 12, 2010 at 7:50 am

    #1250020

    Victory! Thanks all! 🙂

  • November 12, 2010 at 8:03 am

    #1250031

    djossoud (11/12/2010)

    Victory! Thanks all! 🙂

    What finally worked for you?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply