Author Posts

October 29, 2015 at 10:55 am

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: (:) [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!!

October 29, 2015 at 11:03 am

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.