May 20, 2013 at 5:43 pm
Hi all,
I'm rather inexperienced in the Data Warehouse side of SQL, but we have rather small sized data warehouse db of 500 GB in Prod (SS2008 R2). The db is in full recovery mode. Our devs would like a copy of this database on their dev server, however I'm looking for ways to reduce the size of this db upon restore. There are many filegroups with partitions that they mentioned some of them are not needed for their development. (I'm looking for something similar to restoring the db with only requested filegroups, but I'm not sure if SQL will allow this) What options do I have and how best should I go about them. Thanks in advance.
-RR
May 21, 2013 at 3:55 am
To restore the database, you'll have to move everything. No options there. You can shrink it after the restore is complete. Or, you can modify the structure prior to the backup. Or, you can restore it somewhere else, modify the structure, back that up and then restore it dev. But you can't piecemeal a restore operation (yes, there is an exception called a partial restore, but that requires the entire database to be in place already and then pieces can be restored separately).
"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
May 21, 2013 at 5:40 pm
Thanks for the info Grant. Can you clarify how a partial restore works in terms of space allocated? I've read some blogs on the practice but they basically claim it for availability purposes and the time it takes to restore, (restore the primary FG first, then optionally restore the others) When the other FileGroups are offline, the space they occupy still counts against the disk space just like if they were online? And all the data(datafile space) in those offline files was restored in their entirety?
-RR
May 22, 2013 at 4:05 am
There's no way to get around space allocation. In order to run the restore process, you must allocate the required space needed for that process. You can use the MOVE option to rearrange where you store files, but the size of the files can't be changed during the restore, only before the backup or after the 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
May 22, 2013 at 5:29 pm
Thanks for the information, it makes sense. I suppose will have to restore and then defrag/truncate and compress all those unnecessary partitions.
For a refresh done daily, I was hoping for better features.
Cheers
-RR
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply