How to restore a database, ignoring the free space within each file?

Question

Using the following script I get all the disk space used by each file in my database:

;with radhe as (
SELECT 

DatabaseName = 'my Database', --DB_NAME(), 

a.FILEID, 

[FILE_SIZE_GB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000/1024.000, 2)), 

[SPACE_USED_GB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, 'SpaceUsed') / 128.000/1024.000, 2)), 

[FREE_SPACE_GB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128.000/1024.000, 2)), 

a.NAME, a.FILENAME 

FROM dbo.sysfiles a)

select * from radhe

this results:

As you can see on the above picture, the amount of free space inside MyDatabase is high.

I need a copy of this database so that I can test some partitions operations.

Is there a way I can restore this database ignoring the free space in each file?

Basically I will use my "new database" to develop and test something, I would prefer to keep it small.

How could I achieve that?

Answer 1

Is there a way I can restore this database ignoring the free space in each file?

No you cannot. You can restore the database and then do a shrink of database file.

I assume that you are aware of all the caution that the smart people have given.

• I Need to Shrink My Database - I just freed a lot of space
• Why Does the Transaction Log Keep Growing or Run Out of Space? - since you have a large ldf file.

Basically I will use my "new database" to develop and test something, I would prefer to keep it small.

Why dont you use a data generator or from here or just get a sample of data from your PROD for testing ?