March 29, 2004 at 9:27 am
Hi All,
I backed up a SQL Server 2K database that has few records. This database was configured with two filegroups for a size of 30 GB and its full backup data file is about 42 MB. When I tried to restore the database in a development server, which has only 4 GB of free space, I got the following error messages:
Server: Msg 3257, Level 16, State 1, Line 1
There is insufficient free space on disk volume 'd:\' to create the database. The database requires 31652970496 additional free bytes, while only 4188737536 bytes are available.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
It seems that the system must have 30GB or more free space in order to successfully restore this database. If this is true, 1) is there any workaround to restore this (basically emtpy) atabase with the 4 GB free space situation? and 2) is it a better practice to configure a database with a smaller initial size?
Thanks in advance for any inputs!
Yichang
March 29, 2004 at 10:35 am
You can script the entire database with all it objects and then execute the script in the dev server. But the script will aso save the location and size of the original datafiles. So you should change then before executing the script.
Also with DTS you can copy all the objects to a new database in the dwv server, wich must already exists. So you should create it before running DTS.
March 30, 2004 at 1:30 am
If your database files took up 30GB of disk space when you made the backup, then you will need 30GB of spare disk space when you do the restore. This applies even if the database files were 99% empty.
You may be lucky and find a 3rd party restore tool that can overcome this restriction, but I do not know of one.
Wishlist: If any tool vendors can provide this functionality it would be a good selling point!
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 30, 2004 at 2:04 am
If you still have access to the source database, perform a Shrink Database operation on the database before taking another backup.
You could also consider changing the recovery mode to Simple which will reduce the size of the transaction log file (Not recommended to leave a production system in Simple mode though).
March 30, 2004 at 6:54 am
Good suggestion, assuming the database was created small and grew to the current size. Otherwise, shrinking the files won't buy you much... (you can only shrink down to the original "starting" size)
March 30, 2004 at 9:05 am
I used DTS to "restore" the database on development environment.
The source database was installed and configured by a third party vendor. I wonder why the database size was set to 30 GB, instead of a smaller size, for example, 2 GB. For management point of view, the database could start with a smaller size because it can grow automatically as needed. People in this shop prefer restore from full backup and size configuration does matter in this case.
Thank you very much for your helpful suggestions and discussion!
March 31, 2004 at 9:39 am
use dbcc shrinkfile(fileid,size in mb). this will shrinkfile the file regardless of its orginial defined size. fileid is in sysfiles.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply