Shrink database while restoring
-
Can we shrink the database files while restoring from backup?
-
No.
You'll have to provide enough disk space (=size of the original db files) to be able to perform the restore.
After that you can shrink files, but keep in mind to perform full db maintenance afterward to get rid of the fragmentation caused by the shrink operation.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
sehgalnamit (8/3/2011)
Can we shrink the database files while restoring from backup?why not shrink before taking backup.
PS:- Shrink should be avoided for day to day operation.
----------
Ashish -
sehgalnamit (8/3/2011)
Can we shrink the database files while restoring from backup?First find out why you need to shrink:
1) If you see that there is a lot of space left in data file due to empty pages after purging a lot of data, you can shrink it to remove empty pages which are not deallocated by default.
2) If you find that there is a lot in the log file, take a log backup and then shrink it.
You can get a smaller size of database backup by following above two. However, if as a DBA you know that today you shrink a data file from 3 GB to 2 GB and every week the data grows by 1 GB and again on the weekend a lot of purge happens, it is better to leave the size as required by the database to perform this operation.I do not recommend to rely on autogrowth of files automatically. But for this particular scenario, you can try shrinking in a way which I described above.
Also, if you happen to be in 2008R2 version, standard and enterprise editions, both of them offer backup compression. Just check the box on the backup wizard GUI or if you use T-SQL just use the option' with compression' .You will find the backups reduced to small size.
Let me know if you want to ask something specific.
Chandan
-
crazy4sql (8/3/2011)
sehgalnamit (8/3/2011)
Can we shrink the database files while restoring from backup?why not shrink before taking backup.
PS:- Shrink should be avoided for day to day operation.
Should ALWAYS be avoided unless you just archived 50% of the db and will NEVER need that space again.
Here's another option that won't drive your servers mad : http://www.red-gate.com/products/dba/sql-virtual-restore
-
chandan_jha18 (8/3/2011)
sehgalnamit (8/3/2011)
Can we shrink the database files while restoring from backup?First find out why you need to shrink:
1) If you see that there is a lot of space left in data file due to empty pages after purging a lot of data, you can shrink it to remove empty pages which are not deallocated by default.
2) If you find that there is a lot in the log file, take a log backup and then shrink it.
You can get a smaller size of database backup by following above two. However, if as a DBA you know that today you shrink a data file from 3 GB to 2 GB and every week the data grows by 1 GB and again on the weekend a lot of purge happens, it is better to leave the size as required by the database to perform this operation.I do not recommend to rely on autogrowth of files automatically. But for this particular scenario, you can try shrinking in a way which I described above.
Shrinking the log file or data files won't make the backup file any smaller. The backup won't run faster, nor the restore.
Logs should be backed up, NOT SHRINKED. I never have to shrinkg them because I take backups every x minutes. They never have to grow either except maybe once a year where I see they need a little more space where I then resize manually, off hours.
chandan_jha18 (8/3/2011)
Also, if you happen to be in 2008R2 version, standard and enterprise editions, both of them offer backup compression. Just check the box on the backup wizard GUI or if you use T-SQL just use the option' with compression' .You will find the backups reduced to small size.
Let me know if you want to ask something specific.
Chandan
2008 ENT also has it.
-
Ninja's_RGR'us (8/3/2011)
chandan_jha18 (8/3/2011)
sehgalnamit (8/3/2011)
Can we shrink the database files while restoring from backup?First find out why you need to shrink:
1) If you see that there is a lot of space left in data file due to empty pages after purging a lot of data, you can shrink it to remove empty pages which are not deallocated by default.
2) If you find that there is a lot in the log file, take a log backup and then shrink it.
You can get a smaller size of database backup by following above two. However, if as a DBA you know that today you shrink a data file from 3 GB to 2 GB and every week the data grows by 1 GB and again on the weekend a lot of purge happens, it is better to leave the size as required by the database to perform this operation.I do not recommend to rely on autogrowth of files automatically. But for this particular scenario, you can try shrinking in a way which I described above.
Shrinking the log file or data files won't make the backup file any smaller. The backup won't run faster, nor the restore.
Logs should be backed up, NOT SHRINKED. I never have to shrinkg them because I take backups every x minutes. They never have to grow either except maybe once a year where I see they need a little more space where I then resize manually, off hours.
chandan_jha18 (8/3/2011)
Also, if you happen to be in 2008R2 version, standard and enterprise editions, both of them offer backup compression. Just check the box on the backup wizard GUI or if you use T-SQL just use the option' with compression' .You will find the backups reduced to small size.
Let me know if you want to ask something specific.
Chandan
2008 ENT also has it.
I think you are correct about shrinking the data file. But in case of log files, I have seen that if the log file is set to 5 GB as initial size and even if you back it up, you cannot get that 5 GB out of it. So you need to shrink it to smaller value. I could be wrong, but have faced it many times.
-
What do yo mean that get 5 GB out of it?
SS does what you ask it to. If you say put a 5 GB log file there, it'll do just that. Wether or no it needs 1 MB or 5 GB.
The restore will always create a perfect, exact copy of the db at the time of the end of the backup. No way around that... and it better stay that way ;-).
-
chandan_jha18 (8/3/2011)
But in case of log files, I have seen that if the log file is set to 5 GB as initial size and even if you back it up, you cannot get that 5 GB out of it. So you need to shrink it to smaller value. I could be wrong, but have faced it many times.What do you mean by 'get that 5 GB out of it'? A log backup will be (around) the size of the active portion of the log. That can be way smaller than the log file itself. The log should not be shrunk unless it is excessively larger than it needs to be for regular operations (and by regular operations I include index rebuilds, bulk-loads and similar)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
In fact what is written is not accurate.
Yes you can "Shrink SQL Database During Restore" using workaround 🙂 and with out "provide enough disk space (=size of the original db files)".
It is all explain at Microsoft's Technet WIKI which i wrote (and on my blog you can see screen shot and some more).
NEVER SAY NEVER when dealing computer,
have fun
Senior consultant and architect, data platform and application development, Microsoft MVP.
-
ariely.ronen (7/16/2013)
In fact what is written is not accurate.Yes you can "Shrink SQL Database During Restore" using workaround 🙂 and with out "provide enough disk space (=size of the original db files)".
It is all explain at Microsoft's Technet WIKI which i wrote (and on my blog you can see screen shot and some more).
NEVER SAY NEVER when dealing computer,
have fun
apart from the fact this post is 2 years old, the following is not entriely correct
ariely.ronen
NTFS compression is only available on volumes that use the NTFS file system.NTFS compression is only available on NTFS volumes using cluster sizes up to 4KB. If you format the volume with any other size (and if you followed best practice on your sql server you did), compression will be unavailable.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Microsoft Support
Because of performance overhead and database recovery issues (as described in KB 231347), it is not a good idea to run SQL Server databases on compressed drives.Moreover, such installations are NOT supported.
Microsoft SQL Server databases are not supported on NTFS or FAT compressed volumes. A compressed volume does not guarantee sector-aligned writes which is needed to guarantee transactional recovery in some circumstances.
-
Sean Pearce, read the article... it is all there
* the use of the compress folder is temporary for the shrink... then you should read more to get to step 6
** it was checked using win 2003 & SQL 2005, win 2003 & SQL 2008r2, windows 2012 & SQL 2012
and i do say DO NOT EVER DO IT IN LIVE PRODUCTION!!!
just to make sure i do not promising anything 🙂
Senior consultant and architect, data platform and application development, Microsoft MVP.
-
As I said though if you partition the drives in accordance with best practices then compression is not available!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Perry Whittle, i will add a comment about this maybe. thanks
But actually as the entire article is not discuss an ideal case, but a case study which should NOT DO IT IN LIVE PRODUCTION then the "best practices" is not relevant. if you need it and it is not possibly so it is not "best practices" for you 🙂
Senior consultant and architect, data platform and application development, Microsoft MVP.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply