Tuesday, April 28, 2015

Attaching a detached database in SQL Server 2012 with Windows PowerShell

In my previous post a simple way to detach a database from SQL Server 2012 using SQL Server Management Studio was described. The same database will be attached in this post.

It can be attached to the server instance with SQL Server Management Studio in the same way.

However, trying to attach the detached database using Windows PowerShell succeeded but the database name was not restored but the database files were successfully attached. This was an unexpected result.

The next picture shows the databases node before and after attaching the Feb6 database using the code shown in this post. After attaching the database while the name of the database expected was "Feb6", a new system assigned name was given to the attached database.

The PowerShell statements for attaching the detached database were run in Windows PowerShell ISE and are as follows:
PS SQLSERVER:\sql\Hodentek8\RegencyPark\databases>
$files = new-object system.collections.specialized.stringcollection
$files.add("C:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL\DATA\Feb6.mdf")
$files.add("C:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL\DATA\Feb6_log.ldf")
$server=new-object Microsoft.SqlServer.Management.Smo.Server('Hodentek8\RegencyPark')
$db=new-object Microsoft.SqlServer.Management.Smo.Server
Note that the code was run in the databases context.

Although I specified the database name as feb6, the database name was [Hodentek8] after attaching. Hodentek8 is the host name.

Looking at the properties of the database [Hodentek8] shows that the files of database 'Feb6' were attached.

I do not find Feb6 in the databases collection.

However, I am still figuring out the name change.

PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...