#Get a database object
$db = get-sqldatabase ‘Z002SqlExpress’ pubs
#Run a checkdatabse
invoke-sqldatabasecheck $db
$db | invoke-sqldatabasecheck
#Get index defrag information for all indexes
$db | get-sqltable | get-sqlindex | get-sqlindexfragmentation
#Run an index defrag operation against all indexes
$db | get-sqltable | get-sqlindex | invoke-sqlindexdefrag
#Run an reindex operation against all indexes
$db | get-sqltable | get-sqlindex | invoke-sqlindexrebuild
#Run an update statistics operations against all statistics
$db | get-sqltable | get-sqlstatistic | update-statistic
#Get a server object
$server = Get-SqlServer ‘Z002SqlExpress’
#Return log and data directory information:
Get-SqlDefaultDir ‘Z002SqlExpress’
#Create a new database
Add-sqldatabase ‘Z002SqlExpress’ test
#Remove a database
Remove-sqldatabase ‘Z002SqlExpress’ test
#Add a WindowsGroup login
add-sqllogin ‘Z002SqlExpress’ ‘Z002TestGrp1’ -logintype ‘WindowsGroup’
#Add a SqlLogin
add-sqllogin ‘Z002SqlExpress’ test5 test5 -logintype ‘SqlLogin’
#Add a Windowsuser login
add-sqllogin ‘Z002SqlExpress’ ‘Z002testuser1’ -logintype ‘WindowsUser’
#Add a User
add-sqluser ‘Z002SQLEXPRESS’ pubs test5
#Add Windows user
add-sqluser ‘Z002SQLEXPRESS’ pubs ‘testuser1’ ‘Z002testuser1’
#Remove a user
remove-sqluser ‘Z002SQLEXPRESS’ pubs ‘testuser1’
#Remove a login
remove-sqllogin ‘Z002SqlExpress’ test6
#Add a role member to the bulkadmin server role
add-sqlserverrolemember ‘Z002SqlExpress’ ‘test5’ bulkadmin
#Remove a role member from the bulkadmin server role
remove-sqlserverrolemember ‘Z002SqlExpress’ ‘test5’ bulkdmin
#Add a database role
add-sqldatabaserole ‘Z002SqlExpress’ pubs testrole3
#Remove a database role
remove-sqldatabaserole ‘Z002SqlExpress’ pubs testrole3
#Add a database role member
add-sqldatabaserolemember ‘Z002SqlExpress’ pubs test5 testrole3
#Remove a database role member
remove-sqldatabaserolemember ‘Z002SqlExpress’ pubs test5 testrole3
#Get schemas from a database
$db | get-sqlschema
$db | get-sqlschema -name dbo
#Return current processes
Get-SqlProcess ‘Z002SqlExpress’ | ft
#Return active transaction in the tempdb database
get-sqltransaction ‘Z002SqlExpress’ tempdb
#Return the current ErrorLog
get-sqlerrorlog ‘Z002SqlExpress’
#Set server level permission
set-sqlserverpermission ‘Z002SqlExpress’ AlteAnyLogin test5 Grant
#Set database level permission
set-sqldatabasepermission ‘Z002SqlExpress’ pubs CreateTable test5 Grant
#Set object level permission
$db | get-sqlschema -name dbo | set-sqlobjectpermission -permission Select -name test5 -action Grant
#Backup/restore
$server = Get-SqlServer ‘Z002SqlExpress’
invoke-sqlbackup ‘Z002SqlExpress’ ‘pubs’ $($server.BackupDirectory + "pubs.bak")
invoke-sqlrestore ‘Z002SqlExpress’ ‘pubs’ $($server.BackupDirectory + "pubs.bak") -force
The complete list of new functions added in the 1.5 Release:
Invoke-SqlBackup (Database,Log)
Performs a SQL Backup
Invoke-SqlRestore (Database, Log)
Performs a SQL Restore
Invoke-SqlDatabaseCheck
Performs the equivalent of a DBCC CHECKDB
Invoke-SqlIndexRebuild
Performs a reindex
Get-SqlIndexFragmentation
Returns index fragmentation similar to DBCC SHOWCONTIG
Invoke-SqlIndexDefrag
Defragments an index. Performs the equivalent of a DBCC INDEXDEFRAG
Update-SqlStatistic
Updates statistics
Add-SqlDatabase
Adds a new database to a SQL Server
Remove-SqlDatabase
Removes a database from a SQL Server
Add-SqlFileGroup
Adds a new filegroup to a database
Add-SqlDataFile
Adds a new datafile to a filegroup
Add-SqlLogFile
Adds a new logfile to a database
Get-SqlDefaultDir
Returns the default location for data and log files for a SQL Server
Add-SqlUser
Adds a new user to a database
Remove-SqlUser
Removes a user from a database
Add-SqlLogin
Adds a login to a SQL Server
Remove-SqlLogin
Removes a login from a SQL Server
Add-SqlServerRoleMember
Adds a login to a server role
Remove-SqlServerRoleMember
Removes a login from a server role
Add-SqlDatabaseRole
Adds a new database role to a database
Remove-SqlDatabaseRole
Removes a database roel from a database
Add-SqlDatabaseRoleMember
Adds a user or role to a database role
Remove-SqlDatabaseRolemember
Removes a user or role from a database role
Set-SqlServerPermission (GRANT, REVOKE, DENY)
Sets server level permissions to a login
Set-SqlDatabasePermission (GRANT, REVOKE, DENY)
Sets database level permissiosn to a user or role
Set-SqlObjectPermission (GRANT, REVOKE, DENY)
Sets database object level permissions to a user or role
Get-SqlErrorLog
Returns the SQL Server Errorlog
Get-SqlSchema
Returns a SMO Schema object with additional properties
Get-SqlProcess
Returns the current proccesses on a SQL Server. Equivalent to sp_who
Get-SqlTransaction
Returns the current open transactions for a database
Get-SqlEdition
Returns the SQL Server edition
SQLPSX 1.5 marks what I consider the first feature complete release. I believe the most common database administration tasks can be accomplished using the 107 functions/cmdlets provided in SQLPSX. If you feel something is missing please post to the discussion forum.
With Release 1.5 complete, I’m starting work on the 1.6 Release which will include two enhancements. First, re-implement LibraryShowmbrs as a compiled V1 cmdlet primarily to remove a dependency on WMIC. Second, replace LibrarySSIS with a proper PSProvider so that SSIS packages using a SQL Server store can be navigated and modified using a drive analogy. My goal is to the 1.6 release be the last Powershell V1 release. I’m considering using a mix of compiled and script based functions for Release 2.0 and will re-implement the remaining script-based functions as advanced functions in Powershell V2. I know the lack of help files has become an issue as the function libraries have grown and become more complex. The main benefit moving script-based functions to V2 will provide is the ability to have help files just like compiled cmdlets.
Chad Miller's Old Blogsite 