1

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:

enter image description here

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?

1
  • 2
    RESTORE will restore the file sizes as they exist on the source server. If you want to restore a copy that is smaller, you'll need to shrink the source before you take a backup. Commented Nov 23, 2015 at 15:52

1 Answer 1

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.

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 ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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