0

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,

2 Answers2

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.

Remus Rusanu
  • 8,253
  • 1
  • 19
  • 22
1

Restart your Classic ASP application after restore. This will clear existing connections before the Restore.

Alternatively, you could set the database to SINGLE USER mode before the Restore (which should evict any existing connections), perform the Restore before proceeding with your tests.

The SQL script for setting Single User mode is:

ALTER DATABASE <DB_NAME_HERE> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Brett Veenstra
  • 1,487
  • 5
  • 18
  • 27