September 14, 2003 at 11:05 pm
Hay
I have got a problem. Recently we upgraded our Database to SQL 2000. Earlier (in 6.5) we used to restore tables using LOAD , AND DUMP statements .SQL 2000 help also talks about partial database restoration but I am not so clear about how to do it.
Here I am defining my problem
My Table : STR_TBL_STOCK_MAIN
My purpose:Want to restore it to a table of same structure TMP_STR_TBL_STOCK_MAIN
I am not having space to restore the entire database.
Friends please help me with a proper example
September 14, 2003 at 11:28 pm
Not a 100% sure but on 2000 one cannot restore a specific table only, can restore a specific filegroup.
quote:
BOL 2000: If a file is specified, all of the files in its filegroup are also restored.
Plan B: Backup database, restore previous version with table/data you want, then copy table/data somewhere and restore current/live version.
September 14, 2003 at 11:28 pm
Hi there
Your out of luck, there are no sql server tools or commands to recover a single table from a backup. Even so, there are bound to be 3rd party tools to complete the job for you, what they are, i dont know. I dont think a log explorer product will help you here either.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
September 15, 2003 at 12:43 am
quote:
Not a 100% sure but on 2000 one cannot restore a specific table only, can restore a specific filegroup.quote:
BOL 2000: If a file is specified, all of the files in its filegroup are also restored.
I think filegroup backup could be a way to go.
Create a new filegroup, move table(s), index(es) you want to that new group.
You can also have a look at BOL for filegroups -> backing up
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
September 15, 2003 at 8:28 am
Partial restore means that if you have transaction log backups, you can RESTORE to a point-in-time. Let's say your database failed/was corrupted at 12:00. You can do a RESTORE using the STOPAT command to stop the restore prior to 12:00.
-SQLBill
September 15, 2003 at 8:46 am
quote:
Here I am defining my problemMy Table : STR_TBL_STOCK_MAIN
My purpose:Want to restore it to a table of same structure TMP_STR_TBL_STOCK_MAIN
hmm...do you need to do this on a regular basis?
If so, I really think you should take a look at filegroup in BOL.
If not, why not simply use a SELECT INTO statement?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
September 15, 2003 at 5:11 pm
IMHO this is a really bad practice on a relational database. If you give people the ability to restore only one table in the database you give them the ability to easily corrupt the data. The only way I have heard of to do this is through the use of file groups as mentioned above. If you are trying to restore from an old backup you will need to restore the database to a different location and then copy the data to the location you want it. If this is just for archival purposes there are many different solutions including the Select Into as stated above.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 15, 2003 at 5:25 pm
I dont know if I agree that a table restore is a bad practice. Take the case where a knucklehead deletes all the rows from a 100 row lookup table. Sucks to restore a 10g or whatever db just to get 100 rows. I'll grant that this is where a log tool earns its money. Doing a point in time restore is hardly ever the right thing, you're db is out of service AND you end up throwing away other good transactions to rescue other data. Trade off to be sure.
Andy
October 14, 2003 at 8:01 am
Thank you all for overwhelming response to my somewhat foolish question, But if some one knows what exactly the DUMP and LOAD (SQL 6.5 ) statements where doing ,What ever it is I could restore the data into a Table of same structure ,any comments please.For the need of knowing the state of table as on a date I don’t want to restore the entire database. No where in the world restoration is a regular practice , Steve Jones any comments
Thanking you one again
Regards
John
October 14, 2003 at 10:42 am
Not sure I have a good answer, but here are a few thoughts.
There is no current way to load a single table. It was a great feature in 6.5, mainly for when someone deleted or altered a single table and you caught it quickly.
The latest Litespeed (v3) and SQLBacktrack will allow you to recover a single table, but you have to have performed a backup first. It basically works a bcp behind the scenes, but makes it run very smoothly.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
October 15, 2003 at 2:35 am
Thank you Mr Steve Johnes.
I know if you are comming across any sort of new tool which serve the purpose you will post it in the forums.
Have a glance over my new thread “Overriding System Messages”
Regards
John
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply