database became read only - 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

General SQL Server Forums

Data Corruption Issues

database became read only

Author

Topic

saurav gautam
Starting Member

15 Posts

Posted - 2011-11-24 : 05:52:48

Hi
Mother board of one of my computer(where sql server database file was stored) got damaged. So i took the hard disk and connected it to another computer.. I attached the database to that next computer's sql server......

But the problem is that the database became 'READ-ONLY'...

I created another 'READ-WRITE' database exactly identical to the read=only database....

Now i want ot move all the data from old database to the new one... How do i do this... I tried to create back-up and restore but i was unsuccessful............please help

saurav123

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-24 : 06:04:13

Could try ssis to copy the objects?
What went wrong with the backkup and restore? I suspect the database might be corrupt and you are in emergency mode.
Might need to bcp all the data out that you can and bcp into objects in the new database.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

saurav gautam
Starting Member

15 Posts

Posted - 2011-11-24 : 06:11:03

I want to use loop that gets data one-by-one from currupt database and insert the data/record on another database but i dont know sql code for that.. My algorithm is

STEP 1: for N=1 to 'NUMBER_OF RECORDS"
STEP 2: GET DATA FROM N'TH ROW FROM CURRUPT DATABASE( AND STORE AS VARIABLES)
STEP 3: INSERT THE DATA TO NEW DATABASE TABLE
STEP 4: NEXT N

saurav123

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-24 : 07:03:40

I would bcp the data out using native format. You can use a view which uses an index to acces the data pages to get round corrpt data pages as long as the index is intact.
create view xxx
as
select *
from tbl
where indexfld <= nnnn
go
First try without the where cluse - that shold use the data pages.
If it fils add the where clause bcp that out - keep trying until you find the max value that works - then use agreater than to get round corrupt data pages.
Then you can bcp into the new database. You might even find you can get all the data from the table by this method - at least you will know what's missing.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robincham
Starting Member

2 Posts

Posted - 2011-11-25 : 05:36:45

hi saurav gautam

Why don't you connect your hard drive to another computer and recover your database in its original format of 'READ-WRITE'. I think this will be easier. You can recover files from corrupted hard drive using third party utility software like Remo Recover software.

You can download the free demo version of this software... I think you must try this..I hope this will work.

Following link can help you
[url]http://www.recoverfiles.org/windows-file-recovery/recover-corrupted-files-from-hard-drive
[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources