Friday, March 30, 2012

How to attach a db_file with problems?

A database goes "SUSPECT" the client deatach de DB. later it tries to
reatach it but he gets an error. I tried to copy the file to other disk but
I get an error from windows saying that a cyclic error in the disk prevent
the copy. Is there any way to attach a file with problems? just in order to
save at least some of the data?You can create a new database with the same name, using the same data file
and log file names. Then shut down SQL Server and copy your "bad" files
over the top of these new files. When SQL Server starts the database will
once again be suspect. You can then put the database in emergency mode
(status 32768) and recycle SQL Server. At this point you should be able to
at least salvage some of the data, may be all depending on the rason it
went suspect.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
To add on, I had a identical instance where I followed the same procedure as
Rand suggested , after that,
1. The Datbase was able to open in Emergency mode (32768)
2. I was not able to take a backup.
What I did,
1. Create a new database
2. Script out all objects in seperate files
3. Executed the user defined types(UDT) and Table creation script in new
database
4. Run DTS to transfer all data from old to new database ( U can also use
BCP OUT /IN)
5. Executed the Indexe creation script
6. Executed all the other object creation script
After these steps I did a comparison of old and new database and FYI, I lost
few pieces of data which I asked the users to re enter.
Thanks
Hari
MCDBA
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:M3yLU1E6DHA.2768@.cpmsftngxa07.phx.gbl...
> You can create a new database with the same name, using the same data file
> and log file names. Then shut down SQL Server and copy your "bad" files
> over the top of these new files. When SQL Server starts the database will
> once again be suspect. You can then put the database in emergency mode
> (status 32768) and recycle SQL Server. At this point you should be able to
> at least salvage some of the data, may be all depending on the rason it
> went suspect.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>sql

No comments:

Post a Comment