In SQL Server the backup file is around 190 gb, but when restore the log needs around 2 TB space

Posted on

Question :

From the production the database is backed up automatically every day full backup and every 1 hour for transactional backup.

A test server was prepared and I want to restore the latest full backup to the new test server; the full backup size (*.bak file) is around 190 GB, but it keeps showing an error that the log needs around 2 TB for restore – what can be a solution for this?

Answer :

backup size (*.bak file) is around 190 GB, but it keeps showing an error that the log needs around 2 TB for restore

A backup file contains only the allocated pages in the database files plus the log records that were created during the backup process, and may be compressed.

Restoring a backup, however, recreates the target database will all its files the same size they were on the source system.

Please verify the log file size on the production server. It is probably 2TB. Maybe at some point it grew. So, even if it is empty bow because of log backup, it’s still a 2TB file on disk, and you will need to have that space available for the physical file on the new server.

If your test server is VM, you could add large disk and restore the log file there and shrink it after restore. Then move the log to a smaller disk and remove the large disk.

Other option is to shrink log file before full backup, if you’re sure that it does not need to be 2TB.

Leave a Reply

Your email address will not be published. Required fields are marked *