0

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.

7

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.

| improve this answer | |
2

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.

StackExchange Database Backup File Details Explained.

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:

StackExchange File Backup Details

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
    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

| improve this answer | |
  • 1
    >>>The database could be up to 300 GB in actual size<<< The database can be even 4Tb, you just cannot deduce it from backup size. >>>Does your database already contain compressed rows / pages?<<< This also is irrelevant. The only things that matters is the actula sizes of all db files – sepupic Nov 18 '19 at 14:54
  • @sepupic I never stated it couldn't be up to 4 TB size. I am writing this as an introduction into the specifics of database backups. I clearly show in my example that a 48 MB backup is 16 GB in size. I think your comment is irrelevant, unless you can state why it is relevant. If you know your database internally, then you can deduce how big you backup and/or restore might be. An already compressed backup cannot be reduced even further. However a non-compressed backup can be similar in size after a restore compared to a compressed backup. – John aka hot2use Nov 18 '19 at 15:15
  • 1
    OP's database can have 1Tb log and 3Tb data file with only 60Gb of data inside. And it still requires 4Tb on disk, irrelevant to page copression – sepupic Nov 18 '19 at 15:39
  • @sepupic Yes, I'm still not denying that, but knowing your database has benefits. You can then make fact based assumptions. (ital. supposizione) – John aka hot2use Nov 18 '19 at 15:46
1

I would delete the current DB, that would free the space and then restore the .bak as a new DB.

| improve this answer | |
0

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.

  1. Copy the file on disk or copy backup location UNC path where sql service account has permission.
  2. Verify info of backup file from sql server using Restore Filelistonly command. Verify the size of data and log files and add additional space to restore the database.

for more info read the below article to get additional info. https://www.mssqltips.com/sqlservertutorial/109/sql-server-restore-filelistonly/

| improve this answer | |
0

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".

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.