Remotely Restoring a Database - In Use!

Hi all,

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…

restore-sqldatabase -ServerInstance MYSERVER-QA1 -Database MyDatabase-BackupFile “C:\DbBackup\MyDatabase.bak”

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: (:slight_smile: [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.

Any help appreciated!!

You need to issue commands to take the database offline first. That’s definitely a SQL Server thing - I’d review the docs for restoring a database in the MSSQL docs.