4

I have a database backup from a database, say SourceData which needs to be restored on another database, same schema, but different name (SourceData_1) using T-SQL. I don't know why I gets the following error, despite specifying REPLACE option. For example I used the links below:

Restore Database script

http://msdn.microsoft.com/en-us/library/ms190447(v=sql.105).aspx

Based on the posts I found on the internet, first I got the logical names of the backup file using script below:

RESTORE FILELISTONLY
FROM DISK = 'C:\Backups\SourceData_20121227.BAK'

Then used the logical names in the following script:

RESTORE DATABASE SourceData_1 
FROM DISK = 'C:\DEV\SourceData_20121227.BAK'
WITH 
   MOVE 'SourceDate' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SourceData_1.mdf', 
   MOVE 'SourceDate_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SourceData_1_log.ldf', 
   REPLACE
GO

Below is the error I'm getting:

MODIFY FILE encountered operating system error 112 (failed to retrieve text for this error. Reason: 15105) while attempting to expand the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SourceData_1_log.ldf'.
RESTORE DATABASE is terminating abnormally.

I checked the disk space and can confirm it is fine. I assume the target database should exist before running the restore. Is that right?

Any suggestion how to fix this issue?

Thanks.

7
  • Try adding "RECOVERY" so it would be "WITH RECOVERY,..."
    – user507
    Dec 27, 2012 at 5:34
  • I added 'RECOVERY', but still get the same error.
    – Sky
    Dec 27, 2012 at 5:42
  • 1
    How much free space do you have on your C drive? What is the initial size of the data and log file of the database you are restoring (this is in the results from the FILELISTONLY command)?
    – user507
    Dec 27, 2012 at 6:01
  • Any chance the destination filesystem is FAT and not NTFS? Dec 27, 2012 at 6:37
  • 1
    If that new database doesn't exist yet - then don't specify a REPLACE option in your RESTORE
    – marc_s
    Dec 27, 2012 at 11:05

1 Answer 1

1

I believe it has something to do with inadequate disk space. Can you check your application/system event viewer logs if it's throwing an insufficient disk space error? Also figure out the mdf & ldf file sizes of the original database.

If you have created the database already on the 2nd server, check to make sure that file growth is not disabled.

One other option you can try is attaching the mdf file if you can copy that over to the restoring server and see if that works.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.