July 16, 2013 at 9:31 am
Au contrair mon pere
All the dev and ore systems I have worked with are built to the same exacting specification.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 17, 2013 at 12:55 am
Ronen Ariely (7/16/2013)
Perry Whittle, i will add a comment about this maybe. thanksBut 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 🙂
It is indeed a workaround, however IMHO Best Practises should always - at least - be mentioned.
It only thakes a single implementation to really mess up your companies day (of not more than just a day) !
We've had a windows admin changing some logical volumes to be compressed because he saw they only contained database backups. Of course he didn't inform the SQL-team. Well, our sqlinstances kept on crashing and we didn't know why. It took use a few days to figure it out. In the mean while, the instances crashed like hell.
We were lucky these instances weren't direct sales related.
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
July 17, 2013 at 3:40 am
true... and i did wrote it already yesterday 🙂
i added ", using cluster sizes up to 4KB"
Senior consultant and architect, data platform and application development, Microsoft MVP.
October 20, 2016 at 7:09 am
I know this is an old post but has anybody tried instructions in http://social.technet.microsoft.com/wiki/contents/articles/18481.shrink-sql-database-during-restore.aspx ?
I'm using SqlServer 2008R2 and I try to restore to a compressed folder.
If the compressed disk has space enough to hold the uncompressed file then it succeeds and the size on the disk is smaller.
But if the disk free space is less than the required it complains that "There is insufficient free space on disk volume". Used Trace Flag 3104 but it made no difference.
So unless I'm doing something horribly wrong, it seems that you need to have the space needed before it compresses it to let you restore(?)
Thanks
October 20, 2016 at 7:21 am
Good day nj-240363.
I tried it and used it more then once and in more then one machine. I posted the article you mentioned, and a blog about the issue. It worked great for me. By the way, If I posted something, then you can be sure that I probably tested it (I showed it live in Microsoft user group meeting as well). With that been said there are limitations which part of them mentioned in this thread and/or in the article. It will not work for all cases.
* I am not sure if I tested your case exactly.
Senior consultant and architect, data platform and application development, Microsoft MVP.
October 20, 2016 at 7:30 am
October 20, 2016 at 7:45 am
Well, i got email saying I have a response here, so I had to check what the hell is it 🙂
* I am not using this website, just come here sometimes "by mistake". Don't get me wrong... this is great website! with great people.. I simply prefer to use MSDN forums, where I serves as Moderator and feel right at home (Here I am "Forum Newbie" 😉 )
Thanks for the warm feedback.
I want to clarify again that this not official solution and it might not work for you in the case study you described. This more fun then solution.
* I will use this opportunity to edit my profile at last 🙂
Senior consultant and architect, data platform and application development, Microsoft MVP.
October 20, 2016 at 10:25 am
nj-240363 (10/20/2016)
Thanks for the quick reply RonenIt is a very good article indeed. I'll try again to see if I'll get lucky next time.
Do yourself a favour and either
To follow the article which i really do not recommend, you're first going to need to compress the volume assuming its formatted with 4kb clusters which is the default but not part of the sql server BP.
Even when you manage to compress the volume (difficult because sql server will already have a shed load of files open), you then need to attempt the restore.
Just take the easy route I detailed above
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 20, 2016 at 11:18 am
A shrink will not save any (significant) space in the db backup, but it certainly can make the restore files smaller.
You could also consider restoring the db back to its original server under a different name, shrinking that db, backing it up, and then restoring that to the new server. This is especially true if you won't be doing any (significant) modifications to the restored db, but just (mostly) reading it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 20, 2016 at 10:03 pm
ScottPletcher, the idea is not to save space in the backup, but in the the restored database.
If you backup empty database (new database for example) of 2GB, then the backup will be about 6MB. This is not an issue to store 6MB without any shrink ;-). But once you restore the database, then it need to be restored to 2GB, and this is the issue. The restored file which is 2GB, can be shrink significant.
* Once again, This is not a solution for production, but simply a Fun play, as I mentioned in any opportunity 🙂
Perry Whittle, you are totally right regarding "get some extra space", but this is not fun and any DBA should know it :crazy: .
* Regarding "shrink the source db before backing up" or "remove or move objects to make space", these you should not do in production, only because on your home disk you don't have space to restore the production. These are actions you should do if they fit the production and without any consideration of one-time restore that you want to do in testing environment. If the testing environment do not fit, then you should upgrade it.
The case study for the article is that we might want one-time need to copy the production database to your testing laptop...
This is not something to do in production! But only for fun :crazy: in your "home" environment.
Senior consultant and architect, data platform and application development, Microsoft MVP.
October 21, 2016 at 8:07 am
Ronen Ariely (10/20/2016)ScottPletcher, the idea is not to save space in the backup, but in the the restored database.
Indeed, and that's exactly what I pointed out. Shrinking before the backup will not reduce the backup size, but it will reduce the restored size, which is what is important here.
I've often had to do this before restoring otherwise huge dbs to qa. Remove massive data from the tables, shrink the db files, make another backup of the shrunk db, then finally restore that backup, which results in a much smaller restore.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply