Skip to content

Commit bddce87

Browse files
author
msebolt
committed
powershell updates
1 parent 9c44cd0 commit bddce87

18 files changed

Lines changed: 116 additions & 169 deletions

‎docs/2014/analysis-services/instances/install-windows/migrate-power-pivot-to-sharepoint-2013.md‎

Lines changed: 6 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -86,7 +86,7 @@ manager: craigg
8686

8787
Use PowerShell cmdlets in the SharePoint 2013 Management shell to mount the migrated content database. The service application database does not need to be mounted, only the content databases: ![PowerShell related content](../../../reporting-services/media/rs-powershellicon.jpg "PowerShell related content")
8888

89-
```
89+
```powershell
9090
Mount-SPContentDatabase "SharePoint_Content_O14-KJSP1" -DatabaseServer "[server name]\powerpivot" -WebApplication [web application URL]
9191
```
9292
@@ -125,19 +125,18 @@ manager: craigg
125125
126126
1. In the SharePoint 2013 Management Run the `Set-PowerPivotServiceApplication` cmdlet with the `-StartMigratingRefreshSchedules` switch to enable automatic on demand schedule migration ![PowerShell related content](../../../reporting-services/media/rs-powershellicon.jpg "PowerShell related content"). The following Windows PowerShell script assumes that there is only one PowerPivot service application.
127127
128-
```
129-
$app=Get-PowerPivotServiceApplication
128+
```powershell
129+
$app = Get-PowerPivotServiceApplication
130130
Set-PowerPivotServiceApplication $app -StartMigratingRefreshSchedules
131131
```
132132
133133
After the Windows PowerShell script is run, the schedules are active and the schedules will run at the next appropriate time. However, the status one the schedule refresh page is not enabled. When the schedule runs the first time it will be migrated and on the schedule refresh page, **Enabled** will be true.
134134
135135
2. If you want to check the current value of the StartMigratingRefreshSchedules property, run the following PowerShell script. The Script loops through all PowerPivot service application objects and display the name and property values:
136136
137-
```
137+
```powershell
138138
$apps = Get-PowerPivotServiceApplication
139-
foreach ($app in $apps){}
140-
Get-PowerPivotServiceApplication $appp | format-table -property displayname,id,StartMigratingRefreshSchedules
139+
foreach ($app in $apps){ Get-PowerPivotServiceApplication $app | Format-Table -Property displayname, id, StartMigratingRefreshSchedules }
141140
```
142141
143142
**Migrate Schedules option2: User updates each workbook**
@@ -169,6 +168,4 @@ manager: craigg
169168
170169
- [Clean up preparations before an upgrade to SharePoint 2013](https://go.microsoft.com/fwlink/p/?LinkId=256689) (https://go.microsoft.com/fwlink/p/?LinkId=256689).
171170
172-
- [Upgrade databases from SharePoint 2010 to SharePoint 2013](https://go.microsoft.com/fwlink/p/?LinkId=256690) (https://go.microsoft.com/fwlink/p/?LinkId=256690).
173-
174-
171+
- [Upgrade databases from SharePoint 2010 to SharePoint 2013](https://go.microsoft.com/fwlink/p/?LinkId=256690) (https://go.microsoft.com/fwlink/p/?LinkId=256690).

‎docs/2014/database-engine/availability-groups/windows/join-a-secondary-database-to-an-availability-group-sql-server.md‎

Lines changed: 3 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -80,7 +80,7 @@ manager: craigg
8080

8181
The following example joins the secondary database, `Db1`, to the local secondary replica of the `MyAG` availability group.
8282

83-
```
83+
```sql
8484
ALTER DATABASE Db1 SET HADR AVAILABILITY GROUP = MyAG;
8585
```
8686

@@ -96,10 +96,8 @@ manager: craigg
9696

9797
For example, the following command joins a secondary database, `Db1`, to the availability group `MyAG` on one of the server instances that hosts a secondary replica.
9898

99-
```
100-
Add-SqlAvailabilityDatabase `
101-
-Path SQLSERVER:\SQL\SecondaryServer\InstanceName\AvailabilityGroups\MyAG `
102-
-Database "Db1"
99+
```powershell
100+
Add-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\SecondaryServer\InstanceName\AvailabilityGroups\MyAG -Database "Db1"
103101
```
104102

105103
> [!NOTE]
@@ -119,5 +117,3 @@ manager: craigg
119117
[ALTER AVAILABILITY GROUP (Transact-SQL)](/sql/t-sql/statements/alter-availability-group-transact-sql)
120118
[Overview of AlwaysOn Availability Groups (SQL Server)](overview-of-always-on-availability-groups-sql-server.md)
121119
[Troubleshoot AlwaysOn Availability Groups Configuration (SQL Server)deleted](troubleshoot-always-on-availability-groups-configuration-sql-server.md)
122-
123-

‎docs/2014/database-engine/availability-groups/windows/join-a-secondary-replica-to-an-availability-group-sql-server.md‎

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -82,7 +82,7 @@ manager: craigg
8282

8383
The following example, joins the secondary replica to the `MyAG` availability group.
8484

85-
```
85+
```sql
8686
ALTER AVAILABILITY GROUP MyAG JOIN;
8787
```
8888

@@ -100,7 +100,7 @@ manager: craigg
100100

101101
For example, the following command joins a secondary replica hosted by the server instance located at the specified path to the availability group named `MyAg`. This server instance must host a secondary replica in this availability group.
102102

103-
```
103+
```powershell
104104
Join-SqlAvailabilityGroup -Path SQLSERVER:\SQL\SecondaryServer\InstanceName -Name 'MyAg'
105105
```
106106

@@ -122,5 +122,3 @@ manager: craigg
122122
[Creation and Configuration of Availability Groups (SQL Server)](creation-and-configuration-of-availability-groups-sql-server.md)
123123
[Overview of AlwaysOn Availability Groups (SQL Server)](overview-of-always-on-availability-groups-sql-server.md)
124124
[Troubleshoot AlwaysOn Availability Groups Configuration (SQL Server)deleted](troubleshoot-always-on-availability-groups-configuration-sql-server.md)
125-
126-

‎docs/2014/database-engine/availability-groups/windows/manually-prepare-a-secondary-database-for-an-availability-group-sql-server.md‎

Lines changed: 11 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -145,7 +145,7 @@ manager: craigg
145145

146146
1. To use the [!INCLUDE[ssSampleDBobject](../../../includes/sssampledbobject-md.md)] database, modify it to use the full recovery model:
147147

148-
```
148+
```sql
149149
USE master;
150150
GO
151151
ALTER DATABASE MyDB1
@@ -160,7 +160,7 @@ manager: craigg
160160

161161
On the server instance that hosts the primary replica (`INSTANCE01`), create a full backup of the primary database as follows:
162162

163-
```
163+
```sql
164164
BACKUP DATABASE MyDB1
165165
TO DISK = 'C:\MyDB1.bak'
166166
WITH FORMAT
@@ -175,7 +175,7 @@ manager: craigg
175175

176176
On the computer that hosts the secondary replica, restore the full backup as follows:
177177

178-
```
178+
```sql
179179
RESTORE DATABASE MyDB1
180180
FROM DISK = 'C:\MyDB1.bak'
181181
WITH NORECOVERY
@@ -191,7 +191,7 @@ manager: craigg
191191

192192
For example, the following command restores a backup of a primary database that resides in the data directory of the default instance of [!INCLUDE[ssCurrent](../../../includes/sscurrent-md.md)], C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA. The restore database operation must move the database to the data directory of a remote instance of [!INCLUDE[ssCurrent](../../../includes/sscurrent-md.md)] named (*AlwaysOn1*), which hosts the secondary replica on another cluster node. There, the data and log files are restored to the *C:\Program Files\Microsoft SQL Server\MSSQL12.ALWAYSON1\MSSQL\DATA* directory . The restore operation uses WITH NORECOVERY, to leave the secondary database in the restoring database.
193193

194-
```
194+
```sql
195195
RESTORE DATABASE MyDB1
196196
FROM DISK='C:\MyDB1.bak'
197197
WITH NORECOVERY,
@@ -204,7 +204,7 @@ manager: craigg
204204

205205
5. After you restore the full backup, you must create a log backup on the primary database. For example, the following [!INCLUDE[tsql](../../../includes/tsql-md.md)] statement backs up the log to the a backup file named *E:\MyDB1_log.bak*:
206206

207-
```
207+
```sql
208208
BACKUP LOG MyDB1
209209
TO DISK = 'E:\MyDB1_log.bak'
210210
GO
@@ -214,7 +214,7 @@ manager: craigg
214214

215215
For example, the following [!INCLUDE[tsql](../../../includes/tsql-md.md)] statement restores the first log from *C:\MyDB1.bak*:
216216

217-
```
217+
```sql
218218
RESTORE LOG MyDB1
219219
FROM DISK = 'E:\MyDB1_log.bak'
220220
WITH FILE=1, NORECOVERY
@@ -225,7 +225,7 @@ manager: craigg
225225

226226
For example, the following [!INCLUDE[tsql](../../../includes/tsql-md.md)] statement restores two additional logs from *E:\MyDB1_log.bak*:
227227

228-
```
228+
```sql
229229
RESTORE LOG MyDB1
230230
FROM DISK = 'E:\MyDB1_log.bak'
231231
WITH FILE=2, NORECOVERY
@@ -259,16 +259,15 @@ manager: craigg
259259
### <a name="ExamplePSscript"></a> Sample Backup and Restore Script and Command
260260
The following PowerShell commands back up a full database backup and transaction log to a network share and restore those backups from that share. This example assumes that the file path to which the database is restored is the same as the file path on which the database was backed up.
261261

262-
```
262+
```powershell
263263
# Create database backup
264264
Backup-SqlDatabase -Database "MyDB1" -BackupFile "\\share\backups\MyDB1.bak" -ServerInstance "SourceMachine\Instance"
265265
# Create log backup
266266
Backup-SqlDatabase -Database "MyDB1" -BackupAction "Log" -BackupFile "\\share\backups\MyDB1.trn" -ServerInstance "SourceMachine\Instance"
267-
# Restore database backup
267+
# Restore database backup
268268
Restore-SqlDatabase -Database "MyDB1" -BackupFile "\\share\backups\MyDB1.bak" -NoRecovery -ServerInstance "DestinationMachine\Instance"
269-
# Restore log backup
270-
Restore-SqlDatabase -Database "MyDB1" -BackupFile "\\share\backups\MyDB1.trn" -RestoreAction "Log" -NoRecovery -ServerInstance "DestinationMachine\Instance"
271-
269+
# Restore log backup
270+
Restore-SqlDatabase -Database "MyDB1" -BackupFile "\\share\backups\MyDB1.trn" -RestoreAction "Log" -NoRecovery -ServerInstance "DestinationMachine\Instance"
272271
```
273272

274273
## <a name="FollowUp"></a> Follow Up: After Preparing a Secondary Database
@@ -280,5 +279,3 @@ Restore-SqlDatabase -Database "MyDB1" -BackupFile "\\share\backups\MyDB1.trn" -R
280279
[RESTORE Arguments &#40;Transact-SQL&#41;](/sql/t-sql/statements/restore-statements-arguments-transact-sql)
281280
[RESTORE &#40;Transact-SQL&#41;](/sql/t-sql/statements/restore-statements-transact-sql)
282281
[Troubleshoot a Failed Add-File Operation &#40;AlwaysOn Availability Groups&#41;](troubleshoot-a-failed-add-file-operation-always-on-availability-groups.md)
283-
284-

‎docs/2014/database-engine/listeners-client-connectivity-application-failover.md‎

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -178,7 +178,7 @@ SAN = ServerFQDN,AG1_listener.Adventure-Works.com, AG2_listener.Adventure-Works.
178178

179179
Use the `setspn` Windows command line tool to configure the SPN. For example to configure an SPN for an availability group named `AG1listener.Adventure-Works.com` hosted on a set of instances of SQL Server all configured to run under the domain account `corp/svclogin2`:
180180

181-
```
181+
```cmd
182182
setspn -A MSSQLSvc/AG1listener.Adventure-Works.com:1433 corp/svclogin2
183183
```
184184

@@ -212,5 +212,3 @@ setspn -A MSSQLSvc/AG1listener.Adventure-Works.com:1433 corp/svclogin2
212212
[About Client Connection Access to Availability Replicas &#40;SQL Server&#41;](availability-groups/windows/about-client-connection-access-to-availability-replicas-sql-server.md)
213213
[Active Secondaries: Readable Secondary Replicas &#40;AlwaysOn Availability Groups&#41;](availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups.md)
214214
[Connect Clients to a Database Mirroring Session &#40;SQL Server&#41;](database-mirroring/connect-clients-to-a-database-mirroring-session-sql-server.md)
215-
216-

‎docs/2014/database-engine/monitor-sql-server-managed-backup-to-windows-azure.md‎

Lines changed: 23 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,7 @@ manager: craigg
2828

2929
3. Copy and paste the following example into the query window and then click **Execute**. This will return the current configuration for Extended Events, and e-mail notifications.
3030

31-
```
31+
```sql
3232
Use msdb
3333
Go
3434
SELECT * FROM smart_admin.fn_get_parameter (NULL)
@@ -47,40 +47,38 @@ GO
4747

4848
1. To view available Extended Event channels and their current status by running the following query:
4949

50-
```
50+
```sql
5151
SELECT * FROM smart_admin.fn_get_current_xevent_settings()
5252
```
5353

5454
The output from this query will display the event_name, whether it is configurable or not, and whether it is currently enabled. For more information, see [smart_admin.fn_get_current_xevent_settings &#40;Transact-SQL&#41;](/sql/relational-databases/system-functions/managed-backup-fn-get-current-xevent-settings-transact-sql).
5555

5656
2. To enable debug events, run the following query:
5757

58-
```
58+
```sql
5959
-- to enable debug events
6060
Use msdb;
61-
Go
62-
EXEC smart_admin.sp_set_parameter 'FileRetentionDebugXevent', 'True'
63-
61+
GO
62+
EXEC smart_admin.sp_set_parameter 'FileRetentionDebugXevent', 'True'
6463
```
6564

6665
For more information about the stored procedure, see [smart_admin.sp_set_parameter &#40;Transact-SQL&#41;](/sql/relational-databases/system-stored-procedures/managed-backup-sp-set-parameter-transact-sql).
6766

6867
3. To view the logged events run the following query:
6968

70-
```
69+
```sql
7170
-- View all events in the current week
7271
Use msdb;
7372
Go
7473
DECLARE @startofweek datetime
7574
DECLARE @endofweek datetime
76-
SET @startofweek = DATEADD(Day, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
75+
SET @startofweek = DATEADD(Day, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
7776
SET @endofweek = DATEADD(Day, 7-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
7877
79-
EXEC smart_admin.sp_get_backup_diagnostics @begin_time = @startofweek, @end_time = @endofweek;
80-
78+
EXEC smart_admin.sp_get_backup_diagnostics @begin_time = @startofweek, @end_time = @endofweek;
8179
```
8280

83-
```
81+
```sql
8482
-- view all admin events
8583
Use msdb;
8684
Go
@@ -100,8 +98,7 @@ GO
10098
EXEC smart_admin.sp_get_backup_diagnostics @begin_time = @startofweek, @end_time = @endofweek
10199
102100
SELECT * from @eventresult
103-
WHERE event_type LIKE '%admin%'
104-
101+
WHERE event_type LIKE '%admin%'
105102
```
106103

107104
### Aggregated Error Counts/Health Status
@@ -131,18 +128,17 @@ These aggregated counts can be used to monitor system health. For example, if th
131128

132129
3. Run the following query in a query window and provide the e-mail address where you want the notification to be sent to:
133130

134-
```
131+
```sql
135132
Use msdb
136133
Go
137-
EXEC smart_admin.sp_set_parameter @parameter_name = 'SSMBackup2WANotificationEmailIds', @parameter_value = '<email address>'
138-
134+
EXEC smart_admin.sp_set_parameter @parameter_name = 'SSMBackup2WANotificationEmailIds', @parameter_value = '<email address>'
139135
```
140136

141137
This creates a SQL Server Agent job that is used to gather health status and send notifications when there is an error or an issue with backups.
142138

143139
Following is a sample script to enable DB Mail and set up the e-mail notification through SQL Server Agent Job
144140

145-
```
141+
```sql
146142
-- Prereq: Make sure that SQL Server service runs in a service account that has
147143
-- access to SMTP Server
148144
-- set SQL Server service account as domain account
@@ -186,8 +182,7 @@ EXEC msdb.smart_admin.sp_set_parameter
186182
@parameter_value = @emailid
187183
188184
-- To test is you are receiving notifications
189-
-- delete few backup files from your storage container, Wait for 15 minutes & see if you get any email notification
190-
185+
-- delete few backup files from your storage container, Wait for 15 minutes & see if you get any email notification
191186
```
192187

193188
### Using PowerShell to Setup Custom Health Monitoring
@@ -197,27 +192,25 @@ EXEC msdb.smart_admin.sp_set_parameter
197192

198193
Following is a sample PowerShell script that returns a report of errors and warnings based on the system policies and any user policies created:
199194

200-
```
201-
$policyResults = get-sqlsmartadmin | test-sqlsmartadmin -AllowUserPolicies
202-
$policyResults.PolicyEvaluationDetails | select Name, Category, Expression, Result, Exception | fl
203-
195+
```powershell
196+
$policyResults = Get-SqlSmartAdmin | Test-SqlSmartAdmin -AllowUserPolicies
197+
$policyResults.PolicyEvaluationDetails | Select Name, Category, Expression, Result, Exception | fl
204198
```
205199

206-
The following script returns a detailed report of the errors and warnings for the default instance:
200+
The following script returns a detailed report of the errors and warnings for the default instance (`\SQL\COMPUTER\DEFAULT`):
207201

208-
```
209-
PS C:\>PS SQLSERVER:\SQL\COMPUTER\DEFAULT> (get-sqlsmartadmin ).EnumHealthStatus()
202+
```powershell
203+
(Get-SqlSmartAdmin ).EnumHealthStatus()
210204
```
211205

212206
### Objects in MSDB database
213207
There are objects that are installed to implement the functionality. These objects are reserved for internal use. However, there is one system table that can be useful in monitoring the backup status: smart_backup_files. Most of the Information stored in this table relevant to monitoring like the type of backup, database name, first and last lsn, backup expiry dates are exposed through the system function [smart_admin.fn_available_backups &#40;Transact-SQL&#41;](/sql/relational-databases/system-functions/managed-backup-fn-available-backups-transact-sql). However the status column in the smart_backup_files table which indicates the status of the backup file is not available using the function. Following is a sample query you can use to retrieve the some information including the status from the system table:
214208

215-
```
209+
```sql
216210
USE msdb
217211
GO
218212
SELECT
219-
database_name AS [Database Name]
220-
,backup_path AS [Backup Destination and File]
213+
database_name AS [Database Name] ,backup_path AS [Backup Destination and File]
221214
,[Backup Type] =
222215
CASE backup_type
223216
WHEN 1 THEN 'FULL'
@@ -238,8 +231,7 @@ END
238231
,backup_finish_date AS [Backup Completion Time]
239232
,expiration_date AS [Backup Expiry Date/Time]
240233
FROM
241-
smart_backup_files;
242-
234+
smart_backup_files;
243235
```
244236

245237
Following is a detailed explanation of the different status returned:
@@ -255,5 +247,3 @@ smart_backup_files;
255247
- **Deleted - D:** The corresponding file cannot be found in the Azure storage. [!INCLUDE[ss_smartbackup](../includes/ss-smartbackup-md.md)] will schedule a backup if the deleted file results in a break in the backup chain.
256248

257249
- **Unknown - U:** This status indicated that [!INCLUDE[ss_smartbackup](../includes/ss-smartbackup-md.md)] has not yet been able to verify file existence and its properties in the Azure storage. The next time the process runs, which is approximately every 15 minutes, this status will be updated.
258-
259-

‎docs/2014/powershell/load-the-smo-assemblies-in-windows-powershell.md‎

Lines changed: 3 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -26,14 +26,12 @@ manager: craigg
2626
## Example: Loading the SQL Server Management Objects
2727
The following code loads the SMO assemblies:
2828

29-
```
30-
#
29+
```powershell
3130
# Loads the SQL Server Management Objects (SMO)
32-
#
3331
3432
$ErrorActionPreference = "Stop"
3533
36-
$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"
34+
$sqlpsreg = "HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"
3735
3836
if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
3937
{
@@ -75,11 +73,9 @@ foreach ($asm in $assemblylist)
7573
7674
Push-Location
7775
cd $sqlpsPath
78-
update-FormatData -prependpath SQLProvider.Format.ps1xml
76+
Update-FormatData -PrependPath SQLProvider.Format.ps1xml
7977
Pop-Location
8078
```
8179

8280
## See Also
8381
[SQL Server PowerShell](sql-server-powershell.md)
84-
85-

0 commit comments

Comments
 (0)