I want to restore a SQL Server database using a backup (.bak) of about 60GB.
Is it possible to do this on a machine with only 100GB of free disk space?
Or do I need at least the 60GB for the .bak file and the 60GB for the final database on SQL Server?
Are there any ways of just copying the .bak file into the server?
Network shares are not visible in the SSMS - only local drives like USB devices.
SQL Server need to create the database with the size it had when the backup was produced. No way around that.
You can possibly have the backup file on a different machine and restore using an UNC path to that machine. Something like
RESTORE DATABASE myDB FROM DISK = '\\server\share\db.bak'
Above assumes that the service account for the SQL Server service has permissions on the backup share/file.
But, again, investigate what size each database file had when the backup was produced using RESTORE FILELISTONLY - that is the size the files will be created with.
Welcome to DBA.SE. Currently your question results in a lot of IFs. It depends.
The database could be up to 300 GB in actual size depending on the contents (Binary stuff) and the difference in the data.
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 *.mdf and *.ndf files, and an additional ~20 GB for the *.log file.
The backup itself is 47 MB in my example:
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
NOUNLOAD,
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:).
Disclaimer
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
I would delete the current DB, that would free the space and then restore the .bak as a new DB.
yes, you would require additional space to restore the database in sql server and finally, it depends upon the size of the data and log files of the database.
for more info read the below article to get additional info. https://www.mssqltips.com/sqlservertutorial/109/sql-server-restore-filelistonly/
An alternative to Tibor Karaszi's answer suggested by David Spillett:
Alternately if the machine is physically local and not locked down so that you can't, host the backup on a USB drive and mount it that way.
This may be easier (and potentially safer) than playing with the access permissions of SQL Server's service account.
Even if the machine isn't physically local there may be a way to achieve the same thing: Some remote admin and/or virtualization options allow remote mounting of devices (or local filesystems or parts thereof) via "pretend USB".
