February 11, 2011 at 1:46 am
Hi all,
My database is SR_DATA with size 500Gb,I want to restore a table from my database.The size of my table is 5Mb.
Now i want to restore the single table using Filegroup Restoration.
Can any one help me the process of restoring filegroup??
Thanks in advance...
🙂
February 11, 2011 at 3:12 am
its similar to restoring the database....not sure where you finidng the problem.
SSMS--rightclick on your database where u want to restore---tasks--restore--file and filegroups.
If you want to do this by script then follow the above step and provide all the required details in restoration window and befor hitting OK, click Script to generate the sql script.
----------
Ashish
February 11, 2011 at 3:14 am
How is your database structured? How many filegroups? What filegroup is that 5MB table in? What else is in that 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
February 11, 2011 at 4:17 am
If you restore the file group, it restores everything in that file group. That means that all objects, all data, are reset back to what they were at the time of the backup. Here are the basic steps for doing this through SSMS.
But, usually, if you have to recover individual objects, because in most cases you can't afford to overwrite all data in a file or filegroup, you restore to a new database somewhere and then copy the data out.
Another alternative is to use a third party product. My employer, Red Gate software, has a tool called SQL Virtual Restore[/url]. It can create a database off your backup without allocating much space (128kb in most cases) from which you can then recover data.
"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
February 11, 2011 at 7:28 am
Thanks for your reply
i will make myquestion clear
I have adatabase with the name sr_data and the size is 500 gb ,has 6 file groups sr_jan,sr_feb,sr_march,sr_apr suddenly i found one of the table got deleted at yesterday 8'o clock which is sr_jan filegroup .
I have the earlier backup How everly i can restore my table by restoring entire database to the time before the table got deleted.
is there any other way to restore that single table instead of restoring entire database?
Thanks in advance.
🙂
February 11, 2011 at 7:42 am
1) Are you taking the full backup or filegroup backup?
2) Do you have same database setup in some other environment/server where same database with filegroup exist? Like QA or Dev. If so you can restore the database/filegroup backup there and then can move the records of a table or complete table using ssis to your desired server.
----------
Ashish
February 11, 2011 at 7:43 am
Grant Fritchey (2/11/2011)
If you restore the file group, it restores everything in that file group. That means that all objects, all data, are reset back to what they were at the time of the backup. Here are the basic steps for doing this through SSMS.But, usually, if you have to recover individual objects, because in most cases you can't afford to overwrite all data in a file or filegroup, you restore to a new database somewhere and then copy the data out.
Another alternative is to use a third party product. My employer, Red Gate software, has a tool called SQL Virtual Restore[/url]. It can create a database off your backup without allocating much space (128kb in most cases) from which you can then recover data.
No, sorry, there's no way to just restore a table. If it were the only table on the filegroup, you could do a filegroup restore. But if it's not, you're also resetting all the other tables that are on the filegroup with it.
"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
February 11, 2011 at 7:50 am
You could restore Primary plus that one filegroup as a new database and copy the data over. Or you could get a 3rd party tool (like RedGate's Virtual Restore or Quest's LiteSpeed) that allows you to restore a table from a backup.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply