Backup Restoring - Insufficient free space

Question

Backup Restoring - Insufficient free space

Minnu

SSCertifiable

Points: 6451
More actions
September 21, 2016 at 12:53 am

#321588

Hi Team,
Restoring SQL Server database from one server to another.
Actual Database size is: 14.3 GB
But while restoring getting below error.
The database required 127533776896 additional free bytes, while only 47082643456 bytes are available.
I have all the drives with free space of 45 GB only, how to restore the .bak.
Please suggest
September 27, 2016 at 1:17 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 1

Minnu (9/21/2016)
Hi Team,
Restoring SQL Server database from one server to another.
Actual Database size is: 14.3 GB
But while restoring getting below error.
The database required 127533776896 additional free bytes, while only 47082643456 bytes are available.
I have all the drives with free space of 45 GB only, how to restore the .bak.
Please suggest

According to the error message you will need 118 Gb for the restore, you should check the database size on the originating server.

😎

This query will output the total size of the database, the size of the data files and the size of the log files.

DECLARE @DB_NAME SYSNAME = N'DatabaseName';

SELECT

SUM(SMF.size * 8) / POWER(2,10) AS TOTAL_SIZE

,SUM(CASE WHEN SMF.type <> 1 THEN SMF.size * 8 ELSE 0 END) / POWER(2,10) AS DATA_SIZE

,SUM(CASE WHEN SMF.type = 1 THEN SMF.size * 8 ELSE 0 END) / POWER(2,10) AS LOG_SIZE

FROM sys.master_files SMF

WHERE SMF.database_id = DB_ID(@DB_NAME);

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

I've always thought that it would be good if you could shrink the file sizes as part of the restore. Unfortunately, you can't, and so you need the same free space as the files take up on the originating server. If there's free space in the files, you could restore to a server that does have enough space, shrink, back up the database again, and restore on the target.

John

joeroshan SSChampion Points: 10377 More actions · Answer 3

Restore filelistonly from disk='Backup path here'

The above will give you the size of files going to be restored in bytes.

As John mentioned there is no way you can restore without the space allocated even if the files have lot of unused space.

Use following script to look at freespace available and look at size of existing databases to cleanup some space if possible.

http://www.sqlservercentral.com/scripts/Server+Space/145274/

If you have unused space in source database you can try shrinking the database and take a backup once again.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

Minnu (9/21/2016)
Hi Team,
Restoring SQL Server database from one server to another.
Actual Database size is: 14.3 GB
But while restoring getting below error.
The database required 127533776896 additional free bytes, while only 47082643456 bytes are available.
I have all the drives with free space of 45 GB only, how to restore the .bak.
Please suggest

Simple... go back to the source and see why things are so big. For example, could it be that the log file is huge because of no transaction log backups? There are a dozen other possibilities.

If you can't resolve the size of the database at the source (which might be why you're doing a restore to begin with), then you need to find or buy more disk space, plain and simple.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)