Remotely Restoring a Database - In Use!

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Don Jones Don Jones 1 year ago.

  • Author
  • #31490
    Profile photo of Superfreak3

    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!!

  • #31494
    Profile photo of Don Jones
    Don Jones

    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.

You must be logged in to reply to this topic.