Welcome to DBA.SE. Currently your question results in a lot of IFs. It depends.
- Was the database backup compressed?
- Is the disk free space (100 GB) on one disk?
The database could be up to 300 GB in actual size depending on the contents (Binary stuff) and the difference in the data.
- Does your database already contain compressed rows / pages?
All the above questions can give you an generalised idea if you will be able to restore the database backup to your SQL Server.
However, if you don't know the database structure and data distribution, then you will have a hard time guessing. In this case you will have to query the information from the backup file.
Please run the following command to determine what information the backup file contains. Replace the DISK parameter with the name and location of your backup file:
RESTORE FILELISTONLY FROM DISK = 'X:\SQL\BACKUP\StackExchange\StackExchange_backup_2019_11_08_200010_5343451.bak'
Reference: RESTORE Statements - FILELISTONLY (Transact-SQL) (Microsoft | SQL Docs)
This will return a list similar to the following:
LogicalName | PhysicalName | Type | FileGroupName | Size | MaxSize
StackExchange | C:\SQL\SQL_DATA\StackExchange.mdf | D | PRIMARY | 57671680 | 35184372080640
StackExchange_DefRO | C:\SQL\SQL_DATA\StackExchangeRO.ndf | D | DEFAULTRO | 5242880 | 35184372080640
StackExchange_PUBL_SNAP | C:\SQL\SQL_DATA\StackExchange_PUBL_SNAP.ndf | D | PUBL_SNAP_DBASE | 16428040192 | 35184372080640
StackExchange_log | C:\SQL\SQL_LOGS\StackExchange_log.ldf | L | NULL | 21798322176 | 2199023255552
The information provided in the
Size column will give you the size required on disk. In my example output I can confirm that the physical file
C:\SQL\SQL_DATA\StackExchange_PUBL_SNAP.ndf is ~14 GB in size.
Adding up all file sizes in my case, will tell me that I need ~16 GB disk space to restore all
*.ndf files, and an additional ~20 GB for the
The backup itself is 47 MB in my example:
Answering your questions
Is it possible to do this on a machine with only 100GB of free disk space?
It depends on the file size which is documented in the backup file.
Or do I need at least the 60GB for the bak file and the 60GB for the final database in the SQL server?
Yes, if you can access the USB drive and the restore only requires less then 100 GB.
No, if the size of the database (stored in the
size column of the backup file) requires more than 100 GB.
Are there any ways of just copying the bak file into the server?
Possibly. As a temporary workaround you could attach a USB disk to your computer and restore the database to that drive:
RESTORE DATABASE [YOUR_DATABASE] -- the name of the database you will be restoring to
FROM DISK = N'U:\YOUR_DATABASE.bak' -- This is the backup file you are restoring from
WITH FILE = 1,
MOVE N'StackExchange' TO N'U:\YOUR_DATABASE.MDF', -- Locate the logical file to a new pyhsical location
MOVE N'StackExchange_DefRO' TO N'U:\YOUR_DATABASE_DEFRO.NDF', -- Locate the logical file to a new pyhsical location
MOVE N'StackExchange_PUBL_SNAP' TO N'U:\YOUR_DATABASE_PUBL_SNAP.NDF', -- Locate the logical file to a new pyhsical location
MOVE N'StackExchange_log' TO N'U:\YOUR_DATABASE.LDF', -- Locate the logical file to a new pyhsical location
REPLACE, -- Caution: Replaces/overwrites an existing database! Otherwise use a different name at top
STATS = 5
Because the backup file contains the original locations of the physical files of your database, you have to
MOVE them during the restore. Using my example database information from above, I have to move the four physical files during the restore process to their new location on the USB drive (U:).
Don't do this for a productive database! Performance can be degraded!
Reference: RESTORE Statements (Transact-SQL) (Microsoft | SQL Docs)
Thanks for your question: I just realised that I have to clean up the subscription