Insufficient free space during restore
When restoring a database, add the sizes of the database MDF, NDF, and LDF files at the time of the backup to determine the amount of disk space required to restore the database. Once determined, confirm that the destination of the restore has the free space to contain the database files. Once the database is restored and the files are successfully generated on the SQL server, it is then possible to "shrink" the files (see below for details).
The following error can occur when restoring an SQL 2000 or SQL 2005 database:
An error occurred on a query to database <Databasename>.
There is insufficient free space on disk volume 'C:\' to create the database. The database requires X additional free bytes, while only Y bytes are available.
NOTE: The value of X will be larger than Y.
During backups and restores using the SQL Agent, Backup Exec (tm) will back up or restore the entire database logically, which will be the content of the database and the transaction logs for the database. When Backup Exec performs the restore, SQL will rebuild the database files according to specifications recorded during the original backup. This error will occur when restoring a database to a location that does not have enough free space to contain the files that make up the SQL database.
An SQL database is physically made up of two or more files:
Note: Databasename.NDF is only created when the database in question has more than one filegroup. Each filegroup after the primary filegroup is associated with the corresponding NDF file. By default, there is only one filegroup in a database and hence will not have an NDF file.
The data stored within the database will be different than the allocated size of the database. When creating the database, it is necessary to specify the maximum size that the database can be, as well as the file growth parameters. When this is done, this value is allocated at the time of the database creation.
Note: Backup Exec SQL Agent will back up the data content of the files and the file specification. The allocated sizes are not calculated within the job log.
Example: Database TEST1 currently only contains 1 Gigabyte (GB) of data.
However, during its creation, it was specified that it can grow to a maximum of 10 GB.
After the database creation, the properties of the TEST1.MDF file will show as being 10 GB.
SQL Agent will only back up 1 GB of data (excluding the data content of the associated LDF files).
The databasename.LDF file contains all the transaction logs related to the database itself. The database size within SQL Enterprise Manager does not necessarily equate to the transaction logs associated with the database. The transaction log data does not truncate unless a log backup is performed or query analyzer is used to truncate the log file manually. If not done periodically, the LDF file will continue to grow to meet the requirements of the logs.
Note: When the LDF file grows to meet the expectations of the transactions, and a truncation is then performed, the LDF will not decrease in size and will then contain 'empty data.'
The filenames, locations, and space allocation information can be located by performing the following:
1. Open SQL Enterprise Manager
2. Expand Databases
3. Right-click on the database in question
4. The MDF and LDF file information are under the Data Files tab and Transaction Logs tab. The NDF file information will also be under the Data Files tab.
Please see the SQL books online regarding the DBCC commands DBCC SHRINKDATABASE and DBCC SHRINKFILE to truncate and shrink the MDF and LDF files for the database.