Book Image

SQL Server 2014 with Powershell v5 Cookbook

By : Donabel Santos
Book Image

SQL Server 2014 with Powershell v5 Cookbook

By: Donabel Santos

Overview of this book

PowerShell can be leveraged when automating and streamlining SQL Server tasks. PowerShell comes with a rich set of cmdlets, and integrates tightly with the .NET framework. Its scripting capabilities are robust and flexible, allowing you to simplify automation and integration across different Microsoft applications and components. The book starts with an introduction to the new features in SQL Server 2014 and PowerShell v5 and the installation of SQL Server. You will learn about basic SQL Server administration tasks and then get to know about some security-related topics such as the authentication mode and assigning permissions. Moving on, you will explore different methods to back up and restore your databases and perform advanced administration tasks such as working with Policies, Filetables, and SQL audits. The next part of the book covers more advanced HADR tasks such as log shipping and data mirroring, and then shows you how to develop your server to work with BLOB, XML, and JSON. Following on from that, you will learn about SQL Server's BI stack, which includes SSRS reports, the SSIS package, and the SSAS cmdlet and database. Snippets not specific to SQL Server will help you perform tasks quickly on SQL servers. Towards the end of the book, you will find some useful information, which includes a PowerShell tutorial for novice users, some commonly-used PowerShell and SQL Server syntax, and a few online resources. Finally, you will create your own SQL Server Sandbox VMs. All these concepts will help you to efficiently manage your administration tasks.
Table of Contents (15 chapters)
14
Index

Detaching a database


In this recipe, we will detach a database programmatically.

Getting ready

For the purpose of this recipe, let's create a database called TestDB and put its data files in the C:\DATA folder. Open up SQL Server Management Studio and run the following code:

IF DB_ID('TestDB') IS NOT NULL
DROP DATABASE TestDB
GO

CREATE DATABASE [TestDB]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'TestDB', FILENAME = N'C:\DATA\TestDB.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
 FILEGROUP [FG1]
( NAME = N'data1', FILENAME = N'C:\DATA\data1.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
 FILEGROUP [FG2]
( NAME = N'data2', FILENAME = N'C:\DATA\data2.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'TestDB_log', FILENAME = N'C:\DATA\TestDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

How to do it...

To detach a database programmatically, follow these steps:

  1. Open PowerShell ISE as administrator.

  2. Import the SQLPS module and create a new SMO Server object:

    #import SQL Server module
    Import...