0

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

MatSnow
  • 7,357
  • 3
  • 19
  • 31
Judi
  • 710
  • 3
  • 10
  • 25
  • in your code did you close the connection like `$dbconnection.Close()`... Post the code so we can be of better assistance. – ShanayL Sep 12 '17 at 20:03
  • 2
    Perhaps connection pooling. Try `[System.Data.SqlClient.SqlConnection]::ClearAllPools()` after the Invoke-SqlCommand. – Dan Guzman Sep 12 '17 at 21:20

0 Answers0