Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vicki
Posting Yak Master
117 Posts |
 Posted - 2002-03-08 : 14:20:08
|
| Hi,I have the backup database about 100 tables. When I restored the database and I just need to restore only 4 specify tables that I need, Is there a way to do that?, because I don't want to retored all 100 tables then just copy only 4 tables from the back up one to original one, this is waste a lot time If I have the big databaseThankns |
|
|
Jay99
468 Posts |
Posted - 2002-03-08 : 14:42:24
|
| With your existing backup you are going to have to do as you suggested, restore to a different database name then use your drug-of-choice to copy the desired objects.Next time (and this can't be done the backup you already have) put the specific objects on a seperate filegroup, then take your backup of just that filegroup . . .Jay |
 |
|
|
vicki
Posting Yak Master
117 Posts |
Posted - 2002-03-08 : 15:22:58
|
| Thanks I really don't undertand of what you try to tell me?I don't want to do this way. Because If you have about 100 tables and you just need only one table but you have to retored all 100 tables? I don't think this is the best way. Therefore I try to find the other way can be done that only restore the specify table I need?Thanks |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-08 : 15:33:53
|
| If all of the tables are kept in a single filegroup in your database, there is NO OTHER way to restore individual tables than what Jay recommends. Believe me, I sympathize completely, I've been there myself, but if that's what you have now, you're stuck.You can alter your database and add a filegroup(s) to it, then you can alter (or re-create) your tables to use the new filegroup. You can then BACKUP and RESTORE filegroups instead of the entire database, and get the equivalent of individual table BACKUP and RESTORE.Books Online details filegroups, and also re-read the options for BACKUP and RESTORE, I believe there's some decent examples on file and filegroup backup scenarios.Edited by - robvolk on 03/08/2002 15:37:42 |
|
|
|
vicki
Posting Yak Master
117 Posts |
Posted - 2002-03-08 : 16:01:07
|
| Thanks for your respond.Ok, I have no other way to restored the tablesAND now I try to back up the Physician database, then I selectfiles and filegroups, then I select either Primary or physician_dataThen now I try to restored some tables from the one I just backup. Then I selected option Files or FileGroups but I see no option for me to select the tables?Thanks |
|
|
|
vicki
Posting Yak Master
117 Posts |
Posted - 2002-03-08 : 16:06:19
|
quote:
Thanks for your respond.Ok, I have no other way to restored these tablesAND now I try to back up the Physician database, then I selectfiles and filegroups, then I select either Primary or physician_dataThen now I try to restored some tables from the one I just backup. Then I selected option Files or FileGroups but I see no option for me to select the tables?Thanks
|
|
|
|
Jay99
468 Posts |
Posted - 2002-03-08 : 16:12:43
|
Did you...quote:
...put the specific objects on a seperate filegroup...
...before you did the backup? If so, give us the specific sql (backup/restore commands), you are using to do this?Jay |
|
|
|
vicki
Posting Yak Master
117 Posts |
Posted - 2002-03-08 : 16:54:12
|
| This is what I have:restore database Serverbackfile='servers',filegroup='primary'from server1with norecoveryError:Server: Msg 3206, Level 16, State 1, Line 1No entry in sysdevices for backup device 'server1'. Update sysdevices and rerun statement.Server: Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.Thanks |
|
|
|
|
|
|
|
|
Restore Database