Restore MDF file

Hi all,
I need a urgent help. My server crashed yesterday because of the harddisk
problem. I lost my backup file and only got this *.mdf and *.ldf file.
I tried to restore these using attach database method and also by creating a
database...stop the sql service... overwrite the created database with the m
y
*.mdf and *.ldf file.
First method was showing this message "The File you've specified is not a
valid SQL server database file"
Second method ... Database is in Suspect mode ...If I tried to click on the
database, it will show "Error 823: I/O error (bad page ID) detected during
read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\GTS_MY.mdf"
Pls advise what I have to do to restore the above file...
Million Thanks in advance for any help regarding this...
Regards
SajithHi
Try these methods , I hope it will help you.
create a new database of the same name and log file and location as the old
database and log file
get rid of the old database.
you may be able to right click delete it in this situation or used
sp_removedb
create a new database of the right size and shape with correct log and data
file locations
stop sql
rename the new databases.mdf or delete it if you don't have enough space -
do not touch the .ldf
move back in the old database .mdf file or rename it back again
restart sql server
it should come up suspect
--
1. From a query window, set the status so that you can update the system
tables by running the following query:
use Master
go
sp_configure "allow", 1
go
reconfigure with override
go
2. Then set the status of the DB that is giving you the problem (XXXXX) into
Emergency Mode by running the following query:
update sysdatabases set status = 32768 where name = '<DBName>'
go
checkpoint
go
shutdown with nowait
go
3. Go into the data directory (MSSQL2000\DATA) and rename the log file
associated
the DB in question (XXXX.ldf) to some
temporary name, such as XXXX.TMP.
4. Exit the query window.
5. Then start up SQL Server from a DOS command window by issuing:
sqlservr -c -T3608 -T4022.
6. Bring up another query window and verify that the DB is in emergency mode
by issuing:
select Name, Status from Sysdatabases where name = '<DB_Name>'
7. Verify that the status is 32768. If it is, then issue the query:
dbcc traceon(3604)
dbcc rebuild_log('<DB_Name>','<log_filename>') <-- You will need
the quotation marks
REBUILD_LOG should take less than 5 minutes even on a very large
database. It should complete with the message
DBCC execution completed
8. Take the database out of bypass recovery mode by issuing the command
update sysdatabases set status = 0 where name = '<DBName>'
9. Exit the query window and then shutdown (Ctrl-C in the DOS window) and
restart SQL server. Verify the status of the
database by running DBCC NEWALLOC and DBCC CHECKDB on the database.
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:94C942B9-D3FA-49BC-8468-147071C844F7@.microsoft.com...
> Hi all,
> I need a urgent help. My server crashed yesterday because of the harddisk
> problem. I lost my backup file and only got this *.mdf and *.ldf file.
> I tried to restore these using attach database method and also by creating
> a
> database...stop the sql service... overwrite the created database with the
> my
> *.mdf and *.ldf file.
> First method was showing this message "The File you've specified is not a
> valid SQL server database file"
> Second method ... Database is in Suspect mode ...If I tried to click on
> the
> database, it will show "Error 823: I/O error (bad page ID) detected during
> read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft
> SQL
> Server\MSSQL\Data\GTS_MY.mdf"
> Pls advise what I have to do to restore the above file...
> Million Thanks in advance for any help regarding this...
> Regards
> Sajith|||Please do not multipost, answered in .server.
HTH, Jens Suessmeyer.|||Hi,
First let me thank you for replying to my issue...
I have followed through the steps you have mentioned... I had problem with
7th step. I am pasting the error message from the query analyser
dbcc traceon(3604)
dbcc rebuild_log('GTS_MY','GTS_MY_log')
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Server: Msg 5105, Level 16, State 2, Line 2
Device activation error. The physical file name 'GTS_MY_log' may be incorrec
t.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Pls advise...
Thanks a million...
Regards
Sajith
"Uri Dimant" wrote:

> Hi
> Try these methods , I hope it will help you.
>
> create a new database of the same name and log file and location as the ol
d
> database and log file
> get rid of the old database.
> you may be able to right click delete it in this situation or used
> sp_removedb
> create a new database of the right size and shape with correct log and dat
a
> file locations
> stop sql
> rename the new databases.mdf or delete it if you don't have enough space -
> do not touch the .ldf
> move back in the old database .mdf file or rename it back again
> restart sql server
> it should come up suspect
> --
> 1. From a query window, set the status so that you can update the system
> tables by running the following query:
> use Master
> go
> sp_configure "allow", 1
> go
> reconfigure with override
> go
> 2. Then set the status of the DB that is giving you the problem (XXXXX) in
to
> Emergency Mode by running the following query:
> update sysdatabases set status = 32768 where name = '<DBName>'
> go
> checkpoint
> go
> shutdown with nowait
> go
> 3. Go into the data directory (MSSQL2000\DATA) and rename the log file
> associated
> the DB in question (XXXX.ldf) to some
> temporary name, such as XXXX.TMP.
> 4. Exit the query window.
> 5. Then start up SQL Server from a DOS command window by issuing:
> sqlservr -c -T3608 -T4022.
> 6. Bring up another query window and verify that the DB is in emergency mo
de
> by issuing:
> select Name, Status from Sysdatabases where name = '<DB_Name>'
> 7. Verify that the status is 32768. If it is, then issue the query:
> dbcc traceon(3604)
> dbcc rebuild_log('<DB_Name>','<log_filename>') <-- You will need
> the quotation marks
> REBUILD_LOG should take less than 5 minutes even on a very large
> database. It should complete with the message
> DBCC execution completed
> 8. Take the database out of bypass recovery mode by issuing the command
> update sysdatabases set status = 0 where name = '<DBName>'
> 9. Exit the query window and then shutdown (Ctrl-C in the DOS window) and
> restart SQL server. Verify the status of the
> database by running DBCC NEWALLOC and DBCC CHECKDB on the database.
>
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:94C942B9-D3FA-49BC-8468-147071C844F7@.microsoft.com...
>
>|||Ok , make sure that the 'old' path of the LOG file is the same as a 'new'
one
How many Logs files do you have?
http://support.microsoft.com/defaul...kb;en-us;271223
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:49501D5E-487F-42AC-9566-E64985793726@.microsoft.com...
> Hi,
> First let me thank you for replying to my issue...
> I have followed through the steps you have mentioned... I had problem
> with
> 7th step. I am pasting the error message from the query analyser
> dbcc traceon(3604)
> dbcc rebuild_log('GTS_MY','GTS_MY_log')
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Server: Msg 5105, Level 16, State 2, Line 2
> Device activation error. The physical file name 'GTS_MY_log' may be
> incorrect.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Pls advise...
> Thanks a million...
> Regards
> Sajith
> "Uri Dimant" wrote:
>|||Hi,
Thanks very much for the reply.
you are right. It is not the same location. Earlier it was in
E:\Pro...mssql\data ... Right now our server has got only two drives. c: an
d
d:... I was trying d drive for this... so log files need to be in this
location. ? Is there any way to change this part for restoration... ?
Thanks again...
Regards
sajith
"Uri Dimant" wrote:

> Ok , make sure that the 'old' path of the LOG file is the same as a 'new'
> one
> How many Logs files do you have?
> http://support.microsoft.com/defaul...kb;en-us;271223
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:49501D5E-487F-42AC-9566-E64985793726@.microsoft.com...
>
>|||Please take a look at WITH MOVE option in the BOL
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:B8095D52-E924-4C56-90F6-7C35453329F7@.microsoft.com...
> Hi,
> Thanks very much for the reply.
> you are right. It is not the same location. Earlier it was in
> E:\Pro...mssql\data ... Right now our server has got only two drives. c:
> and
> d:... I was trying d drive for this... so log files need to be in this
> location. ? Is there any way to change this part for restoration... ?
> Thanks again...
> Regards
> sajith
> "Uri Dimant" wrote:
>|||Hi,
managed to restore the files in the same locations as it was earlier(before
the crash). But I still have the same problem in thelast step(during dbcc
rebuiid_Log). I have only one error log file.
pls advise
Thanks a ton for the help you are providing now...
Regards
Sajith
"Uri Dimant" wrote:

> Please take a look at WITH MOVE option in the BOL
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:B8095D52-E924-4C56-90F6-7C35453329F7@.microsoft.com...
>
>|||Try
EXEC sp_attach_single_file_db @.dbname = 'dbname',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\dbname.mdf'
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:08B47B26-BAD2-4FAB-823D-33641DFDE9A8@.microsoft.com...
> Hi,
> managed to restore the files in the same locations as it was
> earlier(before
> the crash). But I still have the same problem in thelast step(during dbcc
> rebuiid_Log). I have only one error log file.
> pls advise
> Thanks a ton for the help you are providing now...
> Regards
> Sajith
>
> "Uri Dimant" wrote:
>|||I am getting the error 823
I/O erro(bad page id)....
Right now i am trying to get the data from old files... i thikn it is
corrupted now... its heavy work for me...
Thanks very much for supporting for my issue...even though it is not
solved.. you have spent very long time for this. I really appreciate ...
Thanks again
Best Regards
Sajith
"Uri Dimant" wrote:

> Try
> EXEC sp_attach_single_file_db @.dbname = 'dbname',
> @.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\dbname.md
f'
>
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:08B47B26-BAD2-4FAB-823D-33641DFDE9A8@.microsoft.com...
>
>