October 26, 2008 at 5:18 am
Hello there,
i have to restore a backup which is allocated as 100GB file but having only 20GB inside to another server.
The server where to restore doesn't have enough diskspace to restore the 100GB file.
So i am searching for an restore option which allows me to restore the datafile with only the space which is realy needed.
regards
Michael
October 26, 2008 at 5:33 am
Hi Michael,
I'll be interested to see if anyone has an answer to this as I've had similar problems in the past and ended up having to "borrow" an environment simply to truncate a ridiculously huge transaction log, and then back-up and restore to the intended development environment as the production DBAs were not being helpful. The only other thing I can think of is by spreading the database files across multiple disks - assuming you have a drive large enough for the largest database file (usually the transaction log).
Regards,
Abbs
October 26, 2008 at 8:58 am
If a file is recorded as 100GB in size within a .bak file then when restored it will be 100Gb in size, no way round that.
You could use SSIS (export wizard) to transfer the data logically and thus fit it into a smaller database you have pre-created, or pre-shrink the source database, BUT, shrinking a database causes fragmentation, so is not good for your production database. It may also then need to grow again, which is wasteful of resources. This may be why your production DBA's were being 'unhelpful', its their job to protect the producton environment, not try and fit a quart size production into a development pint sized pot.
---------------------------------------------------------------------
October 26, 2008 at 12:51 pm
if you have your database backup file striped or the tranlog backup striped then you can restore only that required 20 gb of the file to the drive and move the other restores to another drives. in case if your backup is not striped then what you need to do use the SSIS export wizard to transfer the data logically.
October 27, 2008 at 11:29 am
michael.anyone (10/26/2008)
allocated as 100GB file but having only 20GB inside
first thing i would do Michael is ascertain just how much disk space the live database requires.
Once this is done shrink the data file and rebuild all the indexes. Then use a backup of this to restore. 80GB free space sounds like overkill too me, depends on your environment though and the reasoning behind the space allocation in the first place
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 27, 2008 at 1:38 pm
shrinking is not a real choice because the DB will be at 100GB within a year
i think we have to buy another 150GB for things like this or if our customers gives their ok we shrink with the knowing about fragmentation and the others nice things that will happen.
regards
Michael
October 27, 2008 at 1:49 pm
As you've found out, you really need the original size to restore a database.
With db sizes growing, and your db still being small but expected to grow fast, maybe now is the time to really work out a good strategy for filegroups and backups.
Especialy if you are on Enterprise edition !
First of all don't use primary filegroup for user objects !
Create your own user-filegroup and make it the default.
This way you have your system catalog separated from the rest and you can easily perform a filegroup restore or a partial restore.
So you only need the original size of the filegroup in stead of the database.
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
October 27, 2008 at 1:55 pm
A striped backup still requires the full size. If you have the database on multiple file groups, meaning multiple ndf files, you can restore those on separate drives.
October 27, 2008 at 2:52 pm
thanks for the correction Mr.Steve..
October 28, 2008 at 5:07 am
michael.anyone (10/27/2008)
shrinking is not a real choice because the DB will be at 100GB within a year
makes this post rather redundant then really as your target server will need the same disk resources as the source server!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 28, 2008 at 6:38 am
I do this every week for our developers.
Step one: Restore database as is to a machine with enough disk space.
Step two: Strip out the records that the developers do not need access to. In our case we create a table of ids that are test cases. Then delete all records that do not match the test id from every table.
Step three: Truncate the log
Step four: Shrink the files (ldf, mdf, ndf)
Step five: Backup the new database.
Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g
October 28, 2008 at 11:53 am
thats assuming he wants to strip data out 😉
personally i would always truncate and de personalise data before giving it to development
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 28, 2008 at 12:26 pm
Would a production DBA necessarily know enough about the individual database to be able to de-personalise it? I guess it should be a script that the developers include as part of the handover when the system goes live.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply