May 4, 2011 at 4:12 am
Hi All,
I have DB with 1.8 TB size and it's taking huge time to restore the database.
Actually i required to restore only 2 tables of 1.5 GB size from the HUGE backup file on priority.
The problem here is need the tables in less time as it takes lot of time restore entire database
and even if i go with restoration space also a problem in my environment.
Please let me know is there any othere way (or alternative) to avoid database restoration, like selecting or reading some part of data from the backup file (without using 3rd party –using 3rd party is restricted).
please guide me if you have any other alternative solutions for my problem?
Thanks in advance!
May 6, 2011 at 5:09 am
Without using a 3rd party tool, nope. You have to restore the database somewhere and then retrieve the tables that way.
If you want to talk about possible 3rd party solutions, let me know. You don't have any alternatives.
"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 6, 2011 at 5:13 am
Is the database a single filegroup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 8, 2011 at 8:40 am
SQL Server doesn't provide you any way to read the backup file and extract only a table. There are third party tools that can do this, but you would have to purchase them.
If you have Enterprise Edition and the tables are in a filegroup, you could possible restore the filegroup only.
There are two things you are dealing with: space and time.
Re: Space, there's nothing you can do without a third party tool. Virtual Restore from Red Gate and a few other tools, will allow you to "mount" a backup as a database, saving the space and time of restoring the files.
Re: Time - Do you have Instant File Initialization set? If not, that can slow the restore as Windows must zero out the file. That doesn't help for the log file, and if the log file is large that's an issue. One way to speed this in the future is keep a database of that size ready so that you don't have the file creation time added to the restore.
May 8, 2011 at 10:45 pm
1) gotta ask why 3rd party tools are forbidden for this need - they are the RIGHT and BEST solution
2) is the backup file on same IO as IO you are restoring to? if so, don't do that for optimal restore performance
3) are you attempting to restore from network? don't do that for optimal restore
4) is backup done to multiple files on multiple IO? that can dramatically improve both backup and restore times
5) have you done a file IO stall analysis during the restore?
6) maybe you just need to spend a kajillion dollars to get an IO subsystem that doesn't suck. Or you could spend WAYYYY less than that and get a proper 3rd party tool such as Virtual Restore from RedGate.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 9, 2011 at 5:36 am
The one thing you can do, that Gail was leaning towards, would be to have a table on a filegroup. You can restore a filegroup individually.but, the issue here is that you can't put all your tables onto individual filegroups. So you'd have to decide that you have two or three tables that will regularly need to be restored individually and then put them onto a filegroup. But then when a different table needs that same retrieval you're back to restoring the entire database again.
As I say, I can tell you a totally different story with 3rd party tools.
"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 10, 2011 at 7:15 am
Could you put the two tables in question is a different database and then simply use synonyms to get to that data instead?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply