| Author |
Topic |
|
cedubose
Starting Member
22 Posts |
 Posted - 2006-06-09 : 13:21:05
|
| Hi, I have SQL Server 2005 Workgroup edition, and I stupidly overwrote one of the tables with bad data, so I need to restore it from backup.The thing is, I can't figure out how to do a partial restore of only one table. Is this possible?I am primarily a .NET programmer rather than a DBA, so if this is a dumb question, that's why.Cynthia |
|
|
sanjnep
Posting Yak Master
191 Posts |
Posted - 2006-06-09 : 13:28:23
|
| I don't know about sql server 2005 but in sql 2000 you can only don't file group restore not table.You need to put that table to different file group.Thanks SanjeevOhioSanjeev Shrestha12/17/1963 |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-06-09 : 16:57:28
|
| Restore the backup as a new database and manually restore the bad table from this new database.rockmoose |
|
|
|
cedubose
Starting Member
22 Posts |
Posted - 2006-06-09 : 18:06:27
|
| I tried that - I created a new database with a different name, then tried to restore to that database. It gave me an error, saying "you are restoring from a backup not made from this database" (or something to that effect).I guess I need to know how to disable that security check ... is there a way?Cynthia |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-09 : 18:14:18
|
| When performing the restore, you need to move the MDF and LDF to a new location as it is trying to use the original location.RESTORE DATABASE NewDBFROM DISK = 'C:\Backup.BAK'WITH MOVE 'YourMDFLogicalFileName' TO 'C:\NewLocation\NewDB_Data.MDF',MOVE 'YourLDFLogicalFileName' TO 'C:\NewLocation\NewDB_Log.LDF'You can also do this in the RESTORE wizard through the options tab.Tara Kizeraka tduggan |
|
|
|
cedubose
Starting Member
22 Posts |
Posted - 2006-06-09 : 19:29:00
|
| Well, when I use the Management Studio to attempt a restore, and change the "Restore As" in the options to point to my temporary database, I get the following error:"The file 'C:\ ... \MandateTemp.mdf' cannot be overwritten. It is being used by database MandateTemp."It just seems to be one thing after another.I'm a little nervous using the script (not quite sure what I'm doing), so I would prefer to do it through the Restore wizard if possible!When I change the "Restore As", should I specify a non-existent database instead of one I have already created?(Thanks for your help on this BTW!)Cynthia |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-09 : 19:40:22
|
| Yes you need to specific a new name. And then you need to go to the options link in the left pane. You then need to select different file names.Tara Kizeraka tduggan |
|
|
|
cedubose
Starting Member
22 Posts |
Posted - 2006-06-12 : 12:13:48
|
| OK I think I screwed up. I restored to MandatesTemp instead of Mandates (the name of my original database) but it looks to me as if MandatesTemp just replaced Mandates. In the Program Files, it shows MandatesTemp.mdf and MandatesTemp_log.ldf, and Mandates.mdf and Mandates_log.ldf are gone.However, in Management Studio, it is still showing Mandates.What have I done? I think I just overwrote my entire database. Fortunately I have a backup done on Friday, but ... what happened here?God, this is too complicated. I just want to restore one table. Why is it so hard.Cynthia |
|
|
|
cedubose
Starting Member
22 Posts |
Posted - 2006-06-12 : 12:30:44
|
| OK I was able to restore from Friday's backup, and it looks like all my data is there again. However, in the Program Files it still says MandatesTemp.mdf instead of Mandates.mdf. Is this OK? In Management Studio it still says Mandates. I guess I will only try to restore entire databases. Guess you can't do a partial restore.Cynthia |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-13 : 02:10:37
|
| "Guess you can't do a partial restore"I think you used to be able to restore a Table in SQL 6.5, but certainly not now.As suggested above you would have to restore the backup to a new temporary database and then Copy the data back to your "real" database.For the Restore Syntax, which will enable you to also sort out the logical/physical names of the objects, see:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20exampleKristen |
|
|
|
|
Partial restore possible?