November 30, 2018 at 1:31 pm
Our production database is 700 GB but only 400 GB is used. Our destination server has only 500 available space. When we try to restore, it tries to acquire all 700. But is there any setting or trick to restore only the used 400 or so?
Thanks
November 30, 2018 at 2:10 pm
You probably have to shrink it, but then you'll have to rebuild all the indexes... Good thing it's Friday... might be done by Monday.
November 30, 2018 at 2:36 pm
Ah thank you, but shrinking production database is not an option.
November 30, 2018 at 2:44 pm
I assume you have space in prod to restore the db to a diff name?
If so, you could:
1) restore the db to a diff name
2) change recovery to simple
3) shrink the log file to a very minimum size
4) shrink any data file with a lot of extra space -- don't try to squeeze all extra space out, just what you really need.
You should always shrink file by file, not at the db level.
Command DBCC SHOWFILESTATS can show you much space is unused for each data file.
5) backup the db
6) restore that backup on the dest server as whatever db name you want
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 4, 2018 at 4:06 am
There isn't a way to do this with backup and restore. The entire idea of the backup is that it is a full copy in every possible regard of the existing database. The restore then is supposed to ensure that exactly that database gets put back in place. So, all definitions, including the size, are included in the backup/restore process. Your options are to get more space, restore to another location and shrink there, do an export/import process rather than backup/restore.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply