SQL Server data recovery problem

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

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Server data recovery problem

Author

Topic

latharam
Starting Member

1 Post

Posted - 2003-03-15 : 08:12:25

I'm trying to recover a database that has one master data file (.mdf) and one log data file (.ldf). I got the .mdf file from a standard OS backup tape. But sp_detach_db wasn't run on the database before the .mdf backup, so I don't have the .ldf file. I know that the stored procedure sp_attach_single_file_db can recreate the log file in some cases, and I've tried to use it to simply reattach the database, but I get the following error

QUERY FOR MDF RECOVERY
EXEC SP_ADD_DATA_FILE_RECOVER_SUSPECT_DB 'TESTDB','PRIMARY','TESTDB_DATA','F:\New\TESTDB_DATA.MDF','1MB'

ERROR

Server: Msg 823, Level 24, State 6, Line 1
I/O error 38(Reached end of file.) detected during read at offset 0000000000000000 in file 'F:\New\TESTDB_Log.LDF'.
ALTER DATABASE TESTDB ADD FILE(NAME = [TESTDB_DATA], FILENAME = 'F:\New\TESTDB_DATA.MDF', SIZE = 1MB ) TO FILEGROUP [PRIMARY]

Connection Broken

QUERY FOR LDF RECOVERY
EXEC SP_ADD_log_FILE_RECOVER_SUSPECT_DB 'TESTDB','TESTDB_LOG','F:\New\TESTDB_LOG.LDF','1MB'

Server: Msg 823, Level 24, State 6, Line 1
I/O error 38(Reached end of file.) detected during read at offset 0000000000000000 in file 'F:\New\TESTDB_Log.LDF'.
ALTER DATABASE TESTDB ADD LOG FILE(NAME = [TESTDB_LOG], FILENAME = 'F:\New\TESTDB_LOG.LDF', SIZE = 1MB )

Connection Broken

thanx in advance

Edited by - merkin on 03/16/2003 03:04:07

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-16 : 01:48:31

This thread needs to go into the Administration forum.

You can not use the mdf file unless it was detached from the server. The detach stored procedure releases the file from SQL Server so that it can be copied. So, the mdf file is not valid unless it was detached at the time that it was put on the tape. So what you have on tape is not a valid file. You will need to restore the database from your backups and not from this mdf file. You do have a backup, don't you?

EDIT: I believe that if the MSSQLSERVER service was stopped at the time that the mdf file was put on tape, then the file should be able to be attached to the server. But I doubt that this is the case because most people copy the files to tape while the services are running.

Tara

Edited by - tduggan on 03/16/2003 01:55:22

Argyle
Yak Posting Veteran

53 Posts

Posted - 2003-03-16 : 10:31:29

The SPs you are using don't seem to be SQL system SPs. If you only have the mdf file the following should work perfectly fine:

1.
Copy the mdf file in to your mssql data directory. If your old mdf file (and maybe ldf file) still exists in the data directory then either move them or rename the copy you want to restore, from mydatabase_data.mdf to mydatabase_data_test.mdf or something.

2.
Open query analyzer and run for example:
EXEC sp_attach_single_file_db @dbname = 'mydatabase_test', @physname = 'D:\MSSQL\Data\mydatabase_data_test.MDF'

Make sure no database already exists named mydatabase_test

/Argyle

Edited by - argyle on 03/16/2003 10:33:40

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-17 : 12:13:49

quote:

The SPs you are using don't seem to be SQL system SPs.

What do you mean? Latharam used sp_attach_single_file_db, which is exactly what you have told him to run.

Tara

Argyle
Yak Posting Veteran

53 Posts

Posted - 2003-03-17 : 15:08:21

In his post I see these:
EXEC SP_ADD_DATA_FILE_RECOVER_SUSPECT_DB
EXEC SP_ADD_log_FILE_RECOVER_SUSPECT_DB

These do not seem to be system SPs.

/Argyle

chadmat
The Chadinator

1974 Posts

Posted - 2003-03-17 : 15:30:32

He said that was the error message. Those are System SPs that are used internally by sp_attach_single_file_db.

Tara, You are correct about being able to use the .mdf if the services are stopped. I was about to post saying so, but then I saw your edit

-Chad

Argyle
Yak Posting Veteran

53 Posts

Posted - 2003-03-17 : 16:50:18

I see. One learns something new every day. Then check for permission problems (read-only dir/file etc.) or possible hardware error on the server. You could try and restore the file from tape to another server and see if it works better. If not the file is most likely damaged.

/Argyle

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-18 : 13:50:39

It's not that the file is damaged, it's just that the file isn't complete because (most likely) SQL Server had control of the file. The mdf and ldf files should not be backed up to tape unless they are copied to tape when the MSSQLSERVER service was shutdown or the database was detached. It is useless to copy the files to tape in this situation since the files aren't even useable and it is using precious file space on the tape.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources