I'm trying to wrap up some loose ends with our CI process and I'm down to restoring a database remotely on a Test Server from our CI Build Server. I'm running a series of Web App UI Tests in a browser on the Build Server, pointing to the site on the Test Server. When they complete, I would like to restore the test database on the Test Server.
I'm using the following command in a TFS Build Post-Build Script...
This runs right after the UI tests complete. However, I get this...
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.
+ CategoryInfo : InvalidOperation: (:) [Restore-SqlDatabase], SmoException
+ FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand
+ PSComputerName : MYSERVER-QA1
I've even tried running the restore-sqldatabase locally on the Test Server right after the tests complete and that too fails.
After a period of time, I can execute the commands both locally and remotely with success.
So, what can I script to detect any interference? Maybe this is a MSSQL issue, but hopefully some PowerShell gurus have some experience with just such an issue.