Home > Denali, SQLPS > SQL SERVER DENALI CTP3 – SQLPS

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.

Advertisements
Categories: Denali, SQLPS
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: