EMERGENCY -Please help | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

EMERGENCY -Please help

Some users have deleted one table in a database , DB .and i restored the database back as DB1 . now how i go ahead restoring that table. i have never worked on this issue before. Pleae some one help!! its urgent . rajiv
Use Enterprise manager to script the table in DB1, making sure you select every conceivable option in the dialog. Also make a back-up of the current DB, restore that as TestDB. Open Query Analyzer, connected to TestDB. Make sure there’s no reference to the DB or DB1 database in the script, then run the script. Check for any error messages and resolve those issues. SET IDENTITY_INSERT TestDB.dbo.MyTable ON
INSERT INTO TestDB.dbo.MyTable SELECT * FROM DB1.dbo.MyTable
SET IDENTITY_INSERT TestDB.dbo.MyTable OFF Again, check for any error messages and resolve those issues. If there are no problems, then force all users to stop the database. Now run the table script in the production DB, and run the same INSERT INTO replacing the "TestDB" references to "DB". (You will probably have to resolve the same issues again, so you may be able to do that before the actual INSERT INTO.)
Ah yes, and make sure those users no longer have the permission to delete objects – sounds like they don’t kow too well wat they’re doing.
what about DTS import export ?
Some one please tell me if any issue using DTS for this. doing a export from DB1 – import to DB will solve the issue? any challenges has anyone faced before ?
i generated script to create table and found something like if exists (select * from dbo.systypes where name = N’empid’)
exec sp_droptype N’empid’
GO statements in that . suppose one other table in same database has this field , will this affect that table ? will this get droped from that table . Pleaase advice
I see no problem doing with DTS. Don’t forget to remove drop permision to users.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
Using DTS will transfer all indexes,permisstions etc …of that table ? i have no idea of all these
If I were you, first I create a script (using EM) of that table, with indexes, permission but no data, and create that table in original database.
And second I’ll export from one database to other database, only data.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
Luis,<br /><br />your "If I were you" quote raised my confidence .I think its better to do as ‘Adriaan’ told me in begining , same as you said. he has briefed all the steps there .<br /><br />Now can any one tell me, while generating script, what are all the option should be selected to generate it (eg: index,triger,full text index etcc…) .Can anyone list out all that shuld be selected along with .<br /><br />thanks all for your help. i think i can enjoy my weekend <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />rajiv
You need:
Script database users and roles. ( you can change after export table)
Script SQL Servers logins.
Script object level permissions.
Script inedexes.
Script full text indexes (if you use full text indexes).
Scrip triggers.
Scrip primary keys, foreng keys, default, etc. (may be this is no neccesay because who delete the table has no problem at all). Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
Luis , Y shud i script … Script database users and roles. ( you can change after export table)
Script SQL Servers logins.
as I am restoring a table only . Can you please make clear above two points .

First one is users who use that table and roles.
Since one guy delete your table, the idea is to change that roles, if neccesary.
Second one is: in there is one user of that table who does not have loging, scrip is able to create login for that one.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
HI all thanks a lot for your help . Luckly the tape is somewhere in offsite they couldnt bring to restore . Looks like somebody is goin to regenerate the datas in that table. I am not botherd about that <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />Once again thanks guys, i think i learnd something new .<br /><br />rajiv
]]>