April 3, 2008 at 5:29 am
Accidentally truncated one table from my DB. I would hate to loose the work in the other tables, so: Is it possible to restore just one table from a backupset?
I know I could restore the set to another DB and then Insert Select to the table, but that takes a lot of (unavailable) space.
Cees Cappelle
April 3, 2008 at 5:45 am
- Yes ... but not with the propriatary MS tools.
- you could restore the db using a new dbname (and file-set) and then copy the rows of the desired table to the old db.
Don't forget to cleanup the restoreddb.
restore DATABASE RestoreDB
from DISK='D:\mssql.1\mssql\backup\mydbFULL.Bak'
with MOVE N'yourdb_Data' TO N'D:\mssql.1\mssql\data\RestoreDB_Data.mdf'
, MOVE N'yourdb_Log' TO N'D:\mssql.1\mssql\data\RestoreDB_log.ldf'
, recovery
go
use yourdb
go
-- Using Identity column ?
-- SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON / OFF
set identity_insert yourdb.yourschema.yourtable on
go
insert into yourschema.yourtable
select * from RestoreDB.yourschema.yourtable
go
set identity_insert yourschema.yourtable off
go
/* if OK
drop database RestoredDB
*/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 3, 2008 at 5:47 am
Thank you, I figured that as much 😉
Cees Cappelle
April 3, 2008 at 6:05 am
If you have a large usb-disk that could house the temporary restored db db-files, you could start your sqlserver with an extra parameter, perform your restore, copy your table, drop the restored db and restart your sqlserver normaly.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply