From: hvandenberg@xs4all.nl   
      
   On 28-04-2011 17:44, mat wrote:   
   > In article, esquel@sommarskog.se   
   > says...   
   >>   
   >> mat (mat@notarealdotcom.adr) writes:   
   >>> One thing I recall is that the production version of this database was   
   >>> in recovery mode a couple of weeks ago. My copy is a restored backup of   
   >>> that. The production db was damaged by someone at the data center   
   >>> pulling out the wrong network cable.   
   >>>   
   >>> I wonder if the restore my db went into might be related? If a db is   
   >>> damaged and recovered, can it remain unstable?   
   >>   
   >> Yes. More precisely, if a database is victim to a hardware crash, it is very   
   >> likely there is some degree of corruption, and this can certainly cause   
   >> trouble later on.   
   >>   
   >>> I eventually gave up on allowing the recovery to complete so I stopped   
   >>> the sql server instance, renamed the two mdf and ldf, and restored a   
   >>> fresh backup. The restore took much much longer than it used to, and it   
   >>> did the previous time also. I wonder if the restore took so long because   
   >>> of the damaged db/recovery from a couple of weeks ago? Is that possible?   
   >>   
   >> It is conceivable. In any case, you should run DBCC CHECKDB on the   
   >> database when you have restored it. Furthermore, you should run in   
   >> on the production database as well. If you have corruption in there,   
   >> you may be up for trouble in production as well.   
   >   
   > I ran that and after many lines on the tables it ended with   
   >   
   > CHECKDB found 0 allocation errors and 0 consistency errors in database   
   > 'mydb'.   
   >   
   > I guess that means it's a healthy database? Nothing in the check output   
   > looked like a report of problems.   
   >   
   > Is there a way to 'rebuild' a database, as if one was importing from   
   > scratch with a sql script? Even with the production db I have the   
   > ability to take it offline in the evening for hours. Because the db   
   > seems kind of not right, I'd like to at least consider doing this.   
   > Another reason I'd like to do it is because the db has had shrink run on   
   > it many times. Apparently that can cause index fragmentation, and a   
   > performance hit as a result.   
   >   
   > I don't think there is a way using tools included with SSMS to export an   
   > entire db (minus log) to a script and then recreate by executing the   
   > script? Do you have any recommondation for such a utility? The data file   
   > is about 500mb.   
      
   -- Yeah there is.   
      
   -- First thing to do: generate the CREATE TABLE script from SSMS   
   -- Generate a INSERT INTO script for the data   
      
   -- Here' a very simple example that you can adapt to your situation:   
   -- Let's say you have on your production box Table1   
      
   create table Table1 (f1 char(20), f2 int)   
      
   insert into Table1 ( f1, f2) values ('v1', 12)   
   insert into Table1 (f1, f2) values ('v2', 13)   
      
   -- Create Table2 on your production box:   
   create table Table2 (f1 char(20), f2 int)   
      
   -- Generate the "insert into" script for Table2 based on the data of Table1   
      
   -- Make sure that you have output to text by hitting ctrl-t   
      
   SELECT ' insert into Table2 (f1, f2)  values( ''' + ltrim(rtrim(f1)) +   
   ''',' + ltrim(rtrim(cast( f2 as char(30))) ) + ')'  from Table1   
      
   -- Now you can select the generated output and copy it to a query window   
      
   -- One caveat: If you have big tables, you may want to use this trick   
   with 1000 records at a time perhaps!   
      
      
   -- Oh yeah, remember that this example uses two different tables on the   
   same box! Copy the generated script to your dev box and replace "Table2"   
   with "Table1" before executing.   
      
   Hope this helps,   
   Henk   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)