CODEWITHSUNDEEP
sql-serverpowershell
Joost
Joost

Reputation: 344

How to properly detach a MSSQL database

I am trying to copy a MDF file using PowerShell.

I take the DB offline and detach it using this SQL (based on Detach local database .mdf, copy, attach the new file):

USE MASTER;
ALTER DATABASE mydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db 'mydatabase'

This sometimes gives the following error:

Invoke-Sqlcmd : Cannot detach the database'mydatabase' because it is currently in use.

How to properly detach the database?

Upvotes: 0

Views: 3028

Answers (1)

Joost
Joost

Reputation: 344

Solution is to close outstanding connections to the database by setting it to SINGLE_USER mode: 

USE master;  
ALTER DATABASE mydatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

As described here:

Upvotes: 1

Related Questions