Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Try adding "RECOVERY" so it would be "WITH RECOVERY,..." –  Shawn Melton Dec 27 '12 at 5:34
    
I added 'RECOVERY', but still get the same error. –  Sky Dec 27 '12 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)? –  Shawn Melton Dec 27 '12 at 6:01
    
Any chance the destination filesystem is FAT and not NTFS? –  Rohan Dec 27 '12 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 '12 at 11:05
show 2 more comments

1 Answer 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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