How to restore some percentage of backup file?

Question

I have a backup file size 10 GB in a remote location. When I tried to restore it in my pc it is giving error "insufficient storage."

Is there any alternative way to store some percentage of the data that includes the whole schema?

I am using SQL Server 2014 Enterprise.

Answer 1

You probably have an option of:

1. Create several filegroups and data files in these filegroups
2. Move some part of your data to new files using rebuild index command
3. When restoring your database from a backup specify remote location for some data files, and the other files restore locally.

Some useful links to begin with:

• Move an Existing Index to a Different Filegroup (Microsoft Docs)
• SQL Server Can Run Databases from Network Shares & NAS by Brent Ozar

Answer 2

I'd suggest a third party tool that I use on my company. In my case I have a duplicated database.

ApexSQL Diff

With this tool you can duplicate an empty schema of one of your databases. After you has a duplicate schema, you can use next tool:

ApexSQL Data Diff

Using this tool you can choose the content of which tables you want to duplicate on the empty database.

And another one (I've not tested it)

ApexSQL Restore

With this tool you can mount and use virtual databases directly form backup files.

I suppose you can find more products that allow to do similar jobs with your databases. (Have a look at RedGate).

And just another suggestion:

Use an external USB disc or USB pen to store the database.

Answer 3

You might check the file sizes of the database where the backup has been taken from. If the backup had been compressed the size might be much more larger than you think.

These querys might give you some details about the file size and the usage.

SELECT
  NAME,
  TYPE_DESC,
  (SIZE * 8) / 1024 [Size in MB],
  PHYSICAL_NAME
FROM SYS.DATABASE_FILES;

SELECT (SELECT
         SUM(size)
       FROM sys.database_files
       WHERE type IN (0, 2, 4))
       * 8 / 1024 AS [Database size in MB],
       SUM(allocation_units.total_pages)
       * 8 / 1024 AS [Space used in MB],
       (SELECT
         SUM(size)
       FROM sys.database_files
       WHERE type IN (1, 3))
       * 8 / 1024 AS [Log size in MB],
       SUM(allocation_units.used_pages)
       * 8 / 1024 AS [Log used in MB]
FROM sys.partitions
JOIN sys.allocation_units
  ON partition_id = container_id;

Depending on the results you may reduce the file sizes before creating a backup. If you use the database temporary and do not rely on performance use a fast usb stick or external harddisk or even a network storage where you can restore the database for the case you are low on disk space. Don't forget to detach the database before removing the drive or disconnect from the network.

Answer 4

Drop all the non-essential objects, such as indexes, from the DB (or a copy of it).

Some further ideas on Stack Overflow in the Q & A Reducing Size Of SQL Backup?.

There are tools which will extract a referentially correct set of data from a DB. The one I used was for DB2, however. If I find a link for SQL Server I'll edit it in.

Edit: SQL Server Data Tools (SSDT) allows creation of a complete, empty schema and the choice of data. If you are already using Visual Studio this would be especially worth considering. Redgate has a full suite of tools that would achieve this, too.

Of course installing more software will further reduce the space available for the new database.