sql state: 42000 modify file encountered os error

sunilj20 · June 18th, 2004, 02:05 AM

Dear All,

I am trying to restore a database backup file. The machine is Win 2000
Server, with SQL server 2000 installed.

When i restore the database, i get the following error :

"[ms sql dmo] [odbc] sql state: 42000 modify file encountered os error
112 (not enough space on the disk) while attempting the physical file
could not adjust the page allocation for file 'totaldb_data' restoring
db abnormally."

I have ample space about 11 GB on both the drives,
the data folder for the database is in D:\ which has space in it. I
have also truncated the log files. The the actual database, is not
more than 700KB to 750KB as the auto shrink option is set 'True'.

still the restore results in this error.

Please let me know, what could be causing the error.

Thanx in advance

Regards
Sunil J

happygv · June 18th, 2004, 12:35 PM

Hi Sunil,

Hope you are trying to restore the backup file that was backed up from another system. There could be a possiblity of drive mismatch between the two servers.

Eg: Source server from where you backed up the DB call it as ServerA, would have the db files stored in drive E. But the destination server call it as ServerB, would have drive E with lesser space OR assigned to CD drive. So in that case while restoring you may get this error.

Check for that. If so, you can restore the database like this with a WITH MOVE option in Sql Query Analyser and specify the drives that you have enough space and want it to be restored to.

Code:

RESTORE DATABASE TestDB 
    FROM DISK = 'c:\Northwind.bak'
    WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
    MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO

Hope that explains.
Cheers!

_________________________
-Vijay G

Strive for Perfection