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 :
- Why is invoke_sqlcmd not closing the session?
- Can I execute the alter database .... command from my powershell script?
Thanks