Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to create an INSERT script to salvage data from a single table in a corrupt database.

I've succesfully put the database online and put it in EMERGENCY / SINGLE USER mode (following this http://www.sqlskills.com/blogs/paul/disaster-recovery-101-hack-attach-a-damaged-database/ )

This allow SELECTs against the data. However, when trying to use the "Generate Scripts" tool in SSMS, I get an error because there already is a connection to the database. I assume the script tool is attempting to create a seconday (sql server local?) connection/open the database to do its work, but my SSMS session is blocking.

Is it possible to ensure that the script tool is using same connection as the already established? Or drop the connection, but keep the database in EMERGENCY mode?

share|improve this question
1  
I would suggest not to use GUI. If you have the schema, just bcp out/ bcp in to move data in and out. This dba.stackexchange.com/questions/55055/… has my script to bcp out/in data. –  Kin Feb 17 at 3:55

1 Answer 1

up vote 1 down vote accepted

You can drop your connection and the database will stay in EMERGENCY/SINGLE user mode. And the object manager in SSMS does use it's own connection. I'm not sure if the script generator tool uses yet another connection or not. However there is no way to guarantee that your generate scripts tool connection will be the one that takes up the one connection once you have dropped it. The best bet is to generate your script on an alternate database (where ever you are moving your data to should work). Then copy that script to the query window that is connected to the database and run it.

EDIT:

Ok I did a little testing. If you drop your query connection and open up a connection in Object Explorer you can script an INSERT statement (assuming that is what you want).

In Object Explorer find your table, then right click on it. Go to Script Table As -> INSERT To -> Clipboard

enter image description here

Make sure you use clipboard. If you do new query window it will fail. Then disconnect Object Explorer and open a query window to your database and paste.

Otherwise I'll need more information about what type of INSERT statement you are trying to create.

EDIT2: Based on comments

Assuming that you have your destination database (where you are trying to move the data to) on the same instance (and if it isn't make one at least temporarily) then use the following piece of code.

INSERT INTO DestinationDB.dbo.DestinationTable
SELECT * FROM SourceTable

If you have an identity column then you will have to do the following

SET IDENTITY_INSERT DestinationDB.dbo.DestinationTable ON

INSERT INTO DestinationDB.dbo.DestinationTable
(Field List)
SELECT [Field List] FROM SourceTable

SET IDENTITY_INSERT DestinationDB.dbo.DestinationTable OFF
share|improve this answer
    
I tried KILLing the connection right before the generation step in the script wizard, but I got the same error. Looks like it creates another connection. –  goorj Feb 16 at 14:25
    
Doesn't surprise me. I just edited the answer with another option. –  Kenneth Fisher Feb 16 at 14:39
    
I've tried that option, but it seems it does not include data. What I am using is "tasks"->"generate scripts" on the database itself. It has an option to include data. BTW, I am able to do SELECTs on the data and can of course manually create insert statements from the (tabular) data I get. –  goorj Feb 16 at 14:57
    
In the end, I created a new database on the same instance, used the "Script as.." to get the "CREATE TABLE" script (excl data), and then copied the data over as suggested by you. From the healthy database I could easily get the full insert query using "Generate scripts". +Accepted for the workaround –  goorj Feb 17 at 8:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.