Geek Answers Handbook

Not enough disk space when restoring a small backup

I am trying to restore a database from a 32 MB backup. I have 6 GB available on my drive. When I try to restore the backup, it shows an insufficient error on the disk. How is it possible that a 32 MB backup requires more than 6 GB of disk space?

+4
source share
4 answers

Perhaps because the backup is compressed either because there is a very large log file that should not be part of the backup itself, or because the data file itself has been cleared - the backup consists only of pages that contain data. But during recovery, it is still necessary to expand the data file to its original size, even if most of it is empty.

Show us what the size column says at startup:

RESTORE FILELISTONLY FROM DISK = '[path]\whatever.bak';
+7
source

It is always good to check on which drive you are restoring mdf and ldf, maybe you are creating files on a full drive, this can happen.

Other than that, I would suggest restoring this backup in another place, have SHRINKed files and back up again for a desperate measure.

0
source

Other answers explain the cause of the error message, but do not provide a solution to the problem. I had the same problem and I found this solution.

Connect an external hard drive with a large amount of free space, then move the .mdf and .ldf files to the external hard drive. This script can be used to move files during recovery:

 RESTORE DATABASE [MyDb] FROM DISK = N'C:\Data\Backup\MyDb_FULL.bak' WITH FILE = 1, MOVE N'MyDb' TO N'E:\Data\MyDb.mdf', MOVE N'MyDb_log' TO N'E:\Data\MyDb_log.ldf', NOUNLOAD, REPLACE, STATS = 5

After successfully restoring the database to an external drive, you can compress the files and then move them to the hard drive or to another location.

0
source

I had the same problem, I had a 800 MB database backup file, which required 320 GB of free space on the destination computer to restore.

It turned out that this was only because of the database log file, to make sure that the log file caused the problem, right-click on the proposed database and select the properties, then on the General tab, check the Database Size if it is huge go to the Files tab and go to the file path.

I Shrink command for the database and for files through the interface, but this did not help, the following query eventually saved me:

 ALTER DATABASE DataBase_Name SET RECOVERY SIMPLE; GO DBCC SHRINKFILE(DataBase_Name_log, 200); GO ALTER DATABASE DataBase_Name SET RECOVERY FULL; GO

This means that you must query the database and then create the reverse file again.

NB: As you see in the query above, the database recovery mode must be SHRINKFILE Simple before executing SHRINKFILE .

0
source

More articles:


All Articles