SQL Server 2014 with PowerShell v5 Cookbook

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

1. Author:Donabel Santos
   
2. ISBN-10:1785283324
   
3. Year:2016
   
4. Pages:760
   
5. Language:English
   
6. File size:26.81 MB
   
7. File format:PDF
   
8. Category:ASP.NET Network Administration

复制代码

本帖隐藏的内容

SQL Server 2014 with PowerShell v5 Cookbook.pdf (26.18 MB, 需要: 5 个论坛币)

2017-4-10 09:27:47 上传

需要: 5 个论坛币  [购买]

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏1 回帖

关键词：Cookbook Server Powers Power Shell Network 2014

1. Listing SQL Server instances
   
2. 
   
3. In this recipe, we will list all SQL Server instances in the local network.
   
4. 
   
5. Getting ready
   
6. Log in to the server that has your SQL Server development instance as an administrator.
   
7. 
   
8. How to do it...
   
9. Let's look at the steps to list your SQL Server instances:
   
10. 
    
11. Open PowerShell ISE as administrator.
    
12. Let's use the Start-Service cmdlet to start the SQL Browser service:
    
13. Import-Module SQLPS -DisableNameChecking
    
14. 
    
15. #out of the box, the SQLBrowser is disabled. To enable:
    
16. Set-Service SQLBrowser -StartupType Automatic
    
17. 
    
18. #sql browser must be installed and running for us
    
19. #to discover SQL Server instances
    
20. Start-Service "SQLBrowser"
    
21. Next, you need to create a ManagedComputer object to get access to instances. Type the following script and run:
    
22. $instanceName = "localhost"
    
23. $managedComputer = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $instanceName
    
24. 
    
25. #list server instances
    
26. $managedComputer.ServerInstances

复制代码

1. Discovering SQL Server services
   
2. How to do it...
   
3. Let's assume you are running this script on the server box:
   
4. 
   
5. Open PowerShell ISE as administrator.
   
6. Add the following code and execute:
   
7. Import-Module SQLPS -DisableNameChecking
   
8. 
   
9. #you can replace localhost with your instance name
   
10. $instanceName = "localhost"
    
11. 
    
12. $managedComputer = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $instanceName
    
13. 
    
14. #list services
    
15. $managedComputer.Services |
    
16. Select-Object Name, Type, ServiceState, DisplayName |
    
17. Format-Table -AutoSize

复制代码

1. Starting/stopping SQL Server services
   
2. How to do it...
   
3. Let's look at the steps to toggle your SQL Server services states:
   
4. 
   
5. Open PowerShell ISE as administrator.
   
6. Add the following code:
   
7. $verbosepreference = "Continue"
   
8. $services = @("SQLBrowser", "ReportServer")
   
9. $hostName = "localhost"
   
10. 
    
11. $services |
    
12. ForEach-Object {
    
13.    $service = Get-Service -Name $_
    
14.    if($service.Status -eq "Stopped")
    
15.    {
    
16.       Write-Verbose "Starting $($service.Name) ...."
    
17.       Start-Service -Name $service.Name
    
18.    }
    
19.    else
    
20.    {
    
21.       Write-Verbose "Stopping $($service.Name) ...."
    
22.       Stop-Service -Name $service.Name
    
23.    }
    
24. }
    
25. $verbosepreference = "SilentlyContinue"

复制代码

1. Starting/stopping SQL Server services
   
2. How to do it...
   
3. Let's look at the steps to toggle your SQL Server services states:
   
4. 
   
5. Open PowerShell ISE as administrator.
   
6. Add the following code:
   
7. $verbosepreference = "Continue"
   
8. $services = @("SQLBrowser", "ReportServer")
   
9. $hostName = "localhost"
   
10. 
    
11. $services |
    
12. ForEach-Object {
    
13.    $service = Get-Service -Name $_
    
14.    if($service.Status -eq "Stopped")
    
15.    {
    
16.       Write-Verbose "Starting $($service.Name) ...."
    
17.       Start-Service -Name $service.Name
    
18.    }
    
19.    else
    
20.    {
    
21.       Write-Verbose "Stopping $($service.Name) ...."
    
22.       Stop-Service -Name $service.Name
    
23.    }
    
24. }
    
25. $verbosepreference = "SilentlyContinue"

复制代码

1. Listing SQL Server configuration settings
   
2. 
   
3. This recipe walks through how to list SQL Server configurable and nonconfigurable instance settings using PowerShell.
   
4. 
   
5. How to do it...
   
6. Let's look at the steps involved in listing SQL Server configuration settings:
   
7. 
   
8. Open PowerShell ISE as administrator.
   
9. Import the SQLPS module and create a new SMO Server Object:
   
10. #import SQL Server module
    
11. Import-Module SQLPS -DisableNameChecking
    
12. 
    
13. #replace this with your instance name
    
14. $instanceName = "localhost"
    
15. $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
    
16. To explore what members and methods are included in the SMO server, use the following:
    
17. 
    
18. #Explore: get all properties available for a server object
    
19. #see http://msdn.microsoft.com.ezproxy.torontopubliclibrary.ca/en-us/library/ms212724.aspx
    
20. 
    
21. $server |
    
22. Get-Member |
    
23. Where-Object MemberType -eq "Property"

复制代码

1. Creating a database
   
2. 
   
3. This recipe walks through creating a database with default properties using PowerShell.
   
4. 
   
5. Getting ready
   
6. In this example, we are going to create a database called TestDB, and we assume that this database does not yet exist in your instance.
   
7. 
   
8. For your reference, the equivalent T-SQL code of this task is as follows:
   
9. 
   
10. CREATE DATABASE TestDB
    
11. How to do it...
    
12. Follow these steps to create a simple database in SQL Server:
    
13. 
    
14. Open PowerShell ISE as administrator.
    
15. Import the SQLPS module and create a new SMO Server Object:
    
16. #import SQL Server module
    
17. Import-Module SQLPS -DisableNameChecking
    
18. 
    
19. #replace this with your instance name
    
20. $instanceName = "localhost"
    
21. $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
    
22. Add the following script and run:
    
23. #database TestDB with default settings
    
24. #assumption is that this database does not yet exist
    
25. $dbName = "TestDB"
    
26. $db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database($server, $dbName)
    
27. $db.Create()
    
28. 
    
29. #to confirm, list databases in your instance
    
30. $server.Databases |
    
31. Select Name, Status, Owner, CreateDate

复制代码

1. Creating a view
   
2. 
   
3. How to do it...
   
4. Let's check out the steps to create a view using PowerShell:
   
5. 
   
6. Open PowerShell ISE as administrator.
   
7. Import the SQLPS module and create a new SMO Server object:
   
8. #import SQL Server module
   
9. Import-Module SQLPS -DisableNameChecking
   
10. 
    
11. #replace this with your instance name
    
12. $instanceName = "localhost"
    
13. $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
    
14. Add the following script and run:
    
15. 
    
16. $dbName = "AdventureWorks2014"
    
17. $db = $server.Databases[$dbName]
    
18. $viewName = "vwVCPerson"
    
19. $view = $db.Views[$viewName]
    
20. 
    
21. #if view exists, drop it
    
22. if ($view)
    
23. {
    
24.    $view.Drop()
    
25. }
    
26. 
    
27. $view = New-Object -TypeName Microsoft.SqlServer.Management.SMO.View -ArgumentList $db, $viewName, "dbo"
    
28. 
    
29. #TextMode = false meaning we are not
    
30. #going to explicitly write the CREATE VIEW header
    
31. $view.TextMode = $false
    
32. $view.TextBody = @"
    
33. SELECT
    
34.     TOP 100
    
35.   BusinessEntityID,
    
36.   LastName,
    
37.   FirstName
    
38. FROM
    
39.   Person.Person
    
40. WHERE
    
41.     PersonType = 'IN'
    
42. "@
    
43. 
    
44. $view.Create()
    
45. Test the view from PowerShell by running the following code:
    
46. 
    
47. #code below all in one line
    
48. $result = Invoke-Sqlcmd -Query "SELECT * FROM vwVCPerson"
    
49. -ServerInstance $instanceName -Database $dbName
    
50. 
    
51. #display results
    
52. $result | Format-Table -AutoSize

复制代码

谢谢分享