Problem connecting to database after a RESTORE. SQL Server 2005 and Classic ASP

Question

Hi,

I have some Selenium test scripts which I'm using to test a Classic ASP web app, but I'm having problems after restoring the database (SQL Server 2005) with a backup file before every test is run. Immediately after the successful RESTORE (from a Python script running sqlcmd ...) when the ASP tries to connect to the db I get the following error...

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).,,,Microsoft OLE DB Provider for ODBC

This is being generated by ADODB using this connection string...

"DSN=mydbdsn;UID=myuser;PWD=mypass;"

But trying to connect from a C++ ActiveX object via ODBC also fails with the same error.

Restarting IIS gets things back to normal. And SQL Server 2008 exhibits the same issue.

Does anyone have any idea why I can't connect to the database after a RESTORE?

Thanks,

Answer 1

I would guess that this is something to do with connection pooling and your connection being killed when the database is put into single user mode for the restore.

Is there any text in the error message other than what is in your question?

Answer 2

Sounds like the connection to the server is being cached somewhere. Either connection pooling is established or your code is leaking connections somewhere... which is likely with classic asp.

Sounds like you might just want to make an IISRESET call part of your data restore plan.

Answer 3

Copying over from serverfault duplicate question:

From the RESTORE specifications:

During an offline restore, if the specified database is in use, RESTORE forces the users off after a short delay.

So make sure you open new connections after the restore. Existing connections from the connection pool are all busted by RESTORE, as clearly documented. Clear your connection pool before each test.