Restore database of 90GB to computer of only 100GB

Question

How do I restore a huge Microsoft SQL Server database that is 6GB as a zip file and 90GB as a bak file to a computer that only has 100GB free space?

When I uncompress the zip file I get a bak file that is 90GB so only 10GB left on hard drive hence not enough space to restore using SQL Server Management Studio.

The advantage is that I have two computers, the other had 200GB free so I was able to fully restore but its not the intended computer. I've been able to access the restored database over the network using ethernet but my other software of interest requires that the SQL Server running the database is on the same computer as the software of interest so I have to move / restore the database to the computer with less space somehow.

Kindly requesting for guidance, I've been exploring the option of manually copying and pasting the .mdf and log files from the computer where I fully restored to the new computer and then wiring it up somehow but I don't know if it will work.

Answer 1

Once you have restored the database on another system, you can detach the database, move/copy all DB files (data and log) to your target system and attach the database there. The method is relatively simple in most circumstances, you can follow this Microsoft doc for commands and this concept doc for more detail.

Databases will usually grow over time, you will want to make sure you have a plan of how to cope with this - if you are already low on space then you don't have a lot of breathing room. This is especially important for your log files, assuming you are using the full recovery model, you will need to keep your transaction log backed up (to a location other than the DB server) so that you don't exhaust your storage from a growing log file.

Note that running a SQL Server Instance on the same server as the application is often not recommended. Make sure you are giving sane memory limits to your instance so that it does not try to use memory on the server that is likely to be needed by the application.

Answer 2

At least 2 approaches:

1. Put the backup on a shared folder, make the SQL server restore from the shared folder. The MS SQL server accepts shared folder syntax (\server\share\file.bak) pretty well. It will not copy the database locally, it will read it from the shared drive. Depending on your shared folder permissions, this may require logging in as the SQL service account, accessing the folder and saving the password.

2. Restore the database to another SQL server, copy the database files to the local server, attach the database. The another SQL server will keep the database files open and won't let you copy them. You can stop the SQL server service on the computer where the database files reside or "detach" the database in order to release the files.

Note that you will have to match the database server versions. Moving files from older to newer MS SQL server generally works, moving from newer to older version usually doesn't.

Answer 3

External Drives to the Rescue

I had a similar situation (needed hard drive upgrade on a server with most of the space taken by an active database) years ago with a server in a data center (long story...) and the solution was to buy an external hard drive (via the data center, so it cost a bit extra) and use that to store the backups, do the upgrade and then restore from the external drive.

The process now is even easier thanks to flash drives. Get a 128 GB flash drive for around $20 or so:

Make sure it is a USB 3.0 drive and that you connect it to a USB 3.0 port for speed.

Copy the .zip file to the flash drive and unzip it on the flash drive. Then restore to the SQL database on your computer.