Powershell invoke_sqlcmd is not closing connection

3.3k views Asked by At

I am using Powershell to a script to dynamically restore a database (sql server 2012).

set-location "SQLSERVER:\sql\$host_name\$inst\databases\.$db_target"
$db = Invoke-Sqlcmd -Query "select type_desc,name,physical_name from sys.master_files where database_id=db_id(N'$db_target') order by type_desc desc " 

When I execute the script I get the following error message :

DBMS MSG - ODBC return code <-1>, SQL State <37000>, SQL Message <3101><[Microsoft][SQL Server Native Client 10.0][SQL Server]Exclusive access could not be obtained because the database is in use.>.

I then use SSMS to make the database offline

alter database [db_target] set offline with rollback immediate 

After this my script works fine.

Question :

  1. Why is invoke_sqlcmd not closing the session?
  2. Can I execute the alter database .... command from my powershell script?

Thanks

0

There are 0 answers