###################### Detach a Database in Powershell #####################
# Let's see an error message first
# Let's see an error message first
PS SQLSERVER:\SQL\XU-ACER-PC>
$a=dir
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.DetachDatabase("MyTestDB",$False) #I try to
detach MyTestDB and get the error below.
<#
Exception calling "DetachDatabase" with "2" argument(s):
"Detach database failed for Server 'XU-ACER-PC'. "
At line:1 char:1
+ $a.DetachDatabase("MyTestDB",$False)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException
#>
# Horrible error message, no ideas at all. Then I try to
execute the script below in PS ISE
$a=dir
$a.DetachDatabase("MyTestDB",$False)
Trap
{
$err=$_.Exception
$err.message
while
($err.InnerException)
{
$err=$err.InnerException
write-output
$err.message
}
Continue
}
<# Now the error message is: Detach database failed for
Server 'XU-ACER-PC'.
An exception occurred while executing a Transact-SQL statement
or batch.
Cannot detach the database 'MyTestDB' because it is currently
in use.
#>
# Now I create a TestDB with 3 data files and two log files.
The 2nd and 3rd data files are in the FG2 file group.
# Also note I have two instances on the server, the TestDB is
on the default instance.
PS SQLSERVER:\SQL\XU-ACER-PC>
dir
<#
Instance Name
-------------
DEFAULT
I2
# Select the right instance first
PS SQLSERVER:\SQL\XU-ACER-PC> $a=dir | ? {$_.INSTANCEName
-eq "" } ### Note: For the default instance, the instance name is
empty, not DEFAULT
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.DetachDatabase("TestDB",$False)
#How many DB files for the DB? You can find them out by using
the EnumDetachedDatabaseFiles method, you need to know the master mdf file name
and location
PS SQLSERVER:\sql\localhost> dir
Instance Name
-------------
DEFAULT
I2
PS SQLSERVER:\sql\localhost> $a=dir | ? {$_.INSTANCEName
-eq "" }
PS SQLSERVER:\sql\localhost> $a
Instance Name
-------------
DEFAULT
PS SQLSERVER:\sql\localhost>
$a.EnumDetachedDatabaseFiles("C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB.mdf")
# C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB.mdf
# C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB2.ndf
# C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB3.ndf
#How many log files for the DB? You can find them out by using
the EnumDetachedLogFiles method, you also need to know the master mdf file name
and location
PS SQLSERVER:\sql\localhost>
$a.EnumDetachedLogFiles("C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB.mdf")
# C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf
# C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB_log2.ldf
###################### Attach a Database in Powershell
#####################
# Enter the script below in PowerShell ISE, or save it in a ps1
file and execute it from the right location
# point to the right location
CD SQLSERVER:\SQL\Localhost
# I have two instances.Need to limit it to the default
instance
$a=dir | ? {$_.INSTANCEName -eq
"" }
# create a variable for filelist
$sc=new-object System.Collections.Specialized.StringCollection
$sc.Add("C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestAttachDB.mdf")
# You do not need the following files uncless their locations
have changed
#sc.Add("C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestAttachDB2.ndf")
#sc.Add("C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestAttachDB3.ndf")
#sc.Add("C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestAttachDB_log.ldf")
#sc.Add("C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestAttachDB_log2.ldf")
<# The next step is required due to a bug in provider.
For the 1st time
attachment, it is not required. But after the 1st successful attachment, you
will get the following error message even though the database is detached:
=====================================================================
Attach database
failed for Server 'XU-ACER-PC'.
Cannot attach a
database with the same name as an existing database.
=====================================================================
#>
$a.databases.refresh()
###Overload 1
$a.AttachDatabase("TestAttachDB",$sc) #### DB Name,
FileList
###Overload 2
# $a.AttachDatabase("TestDetachDB",$sc, [Microsoft.SqlServer.Management.Smo.AttachOptions]::None)
#### DB Name, FileList, Attach Options
###Overload 3
# $a.AttachDatabase("TestDetachDB",$sc,
"sa") #### DB Name, FileList, DBOwner
###Overload 4
#
$a.AttachDatabase("TestDetachDB",$sc,"sa",
[Microsoft.SqlServer.Management.Smo.AttachOptions]::None) #### DB Name,
FileList,DBOwner, Attach Options
Trap {
$err=$_.Exception
$err.message
while
($err.InnerException)
{
$err=$err.InnerException
write-output
$err.message
}
Continue
}
#$a.DetachDatabase("TestDetachDB",$False)
