SQL SERVER DENALI CTP3 – SQLPS

I’ve tried Denali CTP3 SQLPS and noticed some great changes. Here I will describe my first experience with Denali SQLPS.
I’ve started SQLPS by right-clicking on server node in SSMS Object Explorer and selecting “Start PowerShell”. I read that sqlps is no longer a mini-shell, so I first executed Get-Module cmdlet. As a result I got 2 modules: SQLPS and SQLASCMDLETS.
Under “PS SQLSERVER:\>”  I ran dir and here is result:

I noticed 3 new Providers in Denali CTP3: XEvent, IntegrationServices and SQLAS.
Next, I looked for available Cmdlets in SQLPS and SQLASCMDLETS modules.

Get-Command -CommandType Cmdlet -Module sqlps | select name
Get-Command -CommandType Cmdlet -Module SQLASCMDLETS  | select name

Here are the results:

SQLPS Cmdlets	SQLASCMDLETS  Cmdlets
Add-SqlAvailabilityDatabase	Add-RoleMember
Add-SqlAvailabilityGroupListenerStaticIp	Backup-ASDatabase
Backup-SqlDatabase	Invoke-ASCmd
Convert-UrnToPath	Invoke-ProcessCube
Decode-SqlName	Invoke-ProcessDimension
Disable-SqlHADRService	Invoke-ProcessPartition
Enable-SqlHADRService	Merge-Partition
Encode-SqlName	New-RestoreFolder
Invoke-PolicyEvaluation	New-RestoreLocation
Invoke-Sqlcmd	Remove-RoleMember
Join-SqlAvailabilityGroup	Restore-ASDatabase
New-SqlAvailabilityGroup
New-SqlAvailabilityGroupListener
New-SqlAvailabilityReplica
New-SqlHADREndpoint
Remove-SqlAvailabilityDatabase
Remove-SqlAvailabilityGroup
Remove-SqlAvailabilityReplica
Restore-SqlDatabase
Resume-SqlAvailabilityDatabase
Set-SqlAvailabilityGroup
Set-SqlAvailabilityGroupListener
Set-SqlAvailabilityReplica
Set-SqlHADREndpoint
Suspend-SqlAvailabilityDatabase
Switch-SqlAvailabilityGroup
Test-SqlAvailabilityGroup
Test-SqlAvailabilityReplica
Test-SqlDatabaseReplicaState

There are 29 cmdlets in SQLPS module and 11 cmdlets in SQLASCMDLETS  module. All new cmdlets in SQLPS module, except Backup-SqlDatabase and Restore-SqlDatabase, are for high-availability and disaster recovery solution (HADR).
Next, I looked inside SQL, SQLPolicy and SQLRegistration Provides. It seems that they are the same as in SQL Server 2008R2. XEvent provider gives us possibility to work with Extended Events. I must admit that I like it. My next post will be about it. 🙂

I had troubles with IntegrationServices provider.

I found solution in Chad Miller’s blog post, but my first command finished with bug. I filled a bug report on Connect and here is response from Microsoft:

Similar as 5.9 Cannot Load the Data-tier Application Assembly in PowerShell in Microsoft SQL Server Code-Named “Denali” CTP3 Release Notes, here, http://social.technet.microsoft.com/wiki/contents/articles/3711.aspx 

The SQL Server Code-Named “Denali” CTP3 IntegrationServices assemblies are compiled using .Net Framework 4.0, and .Net Framework 4.0 assemblies are not supported in any currently available version of PowerShell. No workaround exists for this issue; IntegrationServices operations are not supported in PowerShell for SQL Server Code-Named “Denali” CTP3.

For now, I will not play with IntegrationServices.

Backup-SqlDatabase and Restore-SqlDatabase test

I decided to test Backup-SqlDatabase and Restore-SqlDatabase cmdlets. First, I extracted help about cmdlets to .txt files.
Get-Help Backup-SqlDatabase -detailed > D:\Backup-SQLDatabase.txt
Get-Help Restore-SqlDatabase -detailed > D:\Restore-SQLDatabase.txt
I tested Backup-SQLDatabase cmdlets and it seems to be working correctly. I even tried to create a compressed backup on the media with non-compressed backup.  It didn’t work as I expected.

To format disk Mediaset you need to use “FormatMedia”, “Initialize” and “SkipTapeHeader” properties during backup.

Backup-SqlDatabase -Database TestDB -BackupFile “D:\1\Test.bak” -CompressionOption on -FormatMedia -Initialize -SkipTapeHeader

Here is how I restored my database with Restore-SqlDatabase  cmdlet.