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
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
Start SQL Server with the -m parameter, specifying the application name allowed to take the only available connection. For instance, to allow sqlcmd to take the connection:
sqlservr -m"sqlcmd"
If you want to work with SQL Server Management Studio, use this:
sqlservr -m"Microsoft SQL Server Management Studio - Query"
Remember that the "Connect..." button in Management Studio connects the object explorer, that would take your connection without letting you open another one to run the query. In this case, open a query window either disconnected or connected to another server and then use the "Connection, Change Connection" command from the context menu.
See the docs for more information: http://msdn.microsoft.com/en-us/library/ms188236.aspx
An interesting thing to note is that the Configuration Manager doesn't let you enter -m"Microsoft SQL Server Management Studio - Query" as a parameter (I suppose it's the spaces in the parameter), so this is an option only when starting SQL Server from the command line. If you decide to do this, remember to start cmd as an Administrator.
Best Answer
You would only need to put the SQL Server instances in single user mode if you were restoring the
master
database. Foruser
databases, you have to make sure there are no active connections to the database you're restoring. You'd either have to determine and kill any active SPID's (which would not require the database to be in single user mode) or actually put the database in single user mode using one of the following (referencing a post by Greg Robidoux Getting exclusive access to restore SQL Server databases: