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

Question

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

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.