April 11, 2005 at 9:02 am
Hi all. I'm trying to restore a backup and am getting the below message.
"MODIFY FILE encountered operating system error 112(error not found) while attempting to expand the phyisical file. Could not create one or more files. Consider using the WITH MOVE option to identify valid locations. RESTORE DATABASE is terminating abnormally."
This is a bak file I have received from another company. They can restore this backup at their end, but I cannot restore it to my instance of SQL. We're both running SQL server 2000. Is there a problem with the bak file at all? It appears that the log file is the file it's getting trouble expanding as when I click OK to the message, the physical LDF file is deleted from my hard drive.
Thanks in anticipation
Steve Williams
April 11, 2005 at 9:33 am
use RESTORE FILELISTONLY to get the logical file names in the backup file. Then use
RESTORE DATABASE dbname
FROM DISK = 'backupFileName'
WITH MOVE 'FirstlogicalFileName' TO 'NewLocationAndName',
MOVE ''SecondlogicalFileName' TO 'NewLocationAndName',
etc...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 11, 2005 at 9:37 am
To further explain...the backup file contains a list of all the files that comprise the database along with their paths. If you just use RESTORE DATABASE SQL Server assumes that you want the files to go back to the same paths they came from. If the paths are different on the target machine than they were on the source, you get this error. The MOVE option allows you to tell SQL Server where each file should go.
You can do the same thing in EM by editing the file paths, but I always use QA for backups and restores...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 11, 2005 at 9:51 am
I'm just using the restore database from the right click menu against a database in Enterprise Manager. Reading from media, ticing force restore and then changing the physical file paths to the filepaths of the blank database shell I've created ready.
Just tried the below in Query Analyser and got the same error.
RESTORE DATABASE Main1st_2
FROM DISK = 'D:\SQLDATA\adhocbackup.bak'
WITH REPLACE,
MOVE 'Main1st_TestDB_Data' TO 'D:\SQLDATA\Main1st_2_Data.MDF',
MOVE 'Main1st_TestDB_Log' TO 'D:\SQLDATA\Main1st_2_Log.LDF'
Steve Williams
April 11, 2005 at 9:58 am
If the database exists already you have to use the REPLACE option. Or you can delete the database and then run the RESTORE command as is...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 11, 2005 at 10:04 am
While we do get "With Move" errors when using different paths on the source and destination servers when not changing locations appropriately I don't remember seeing errors related to Expanding the Physical File.
Steve, did you check the hard drive space? Did you check all 3 event logs: application, system and security? System log may give you Insufficient Drive Space errors, Security log may tell you that something or someone don't have enough permissions, application log may give you a lot of hints.
Is your new database that you have created in advance have file growth disabled?
As a workaround ask the other company just to send you a database file without log and attach this file with sp_attach_single_file_db
Yelena
Regards,Yelena Varsha
April 11, 2005 at 10:37 am
Thanks Yelena
There is 25GB free on the hard drive. The bak file is only 37MB in size. Nothing of any signifance in the Event Viewer. Only the actual error message.
File growth is set to increase by 10%. THis is normally more than enough when I get larger databases than this to restore.
I'll speak to the other company again.
Steve Williams
April 11, 2005 at 11:18 am
Steve,
May it be that their log file is with no growth? I am not sure at what point the destination database options are overwritten with the database-to-be-restored options.
You may want to ask the other company what is the exact db and log sizes at their server, expand your new database files accordingly and then try to restore.
Best regards,
YV
Regards,Yelena Varsha
April 11, 2005 at 12:36 pm
The RESTORE FILELISTONLY command gives you the size of the files to be restored.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
May 5, 2009 at 9:19 am
HI Guys,
I'm also getting the same error, like WITH MOVE Option do, i've changes the New MDF file path and new Log file path from Enterprice manager, so it'll work just like command WITH MOVE, still while moving/expanding Data file, it gives following error.
[font="Courier New"]"Could not adjust the space allocation for file 'my Data file name'
RESTORE DATABASE is terminating abnormally"[/font]
Hope to see you around with good solution.
Thanks
Viral
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply