Day 20 Introduction to SMO
>>SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server
>>The SMO object model extends and supersedes the Distributed Management Objects (SQL-DMO) object model.
>>The DMO library is a COM object model, whereas SMO is implemented as a .NET Framework assembly
>>SMO is compatible with SQL Server 2000, SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2,
>>The SQL Server provider automatically loads the SQL Server Management Object (SMO) assemblies
The Microsoft.SqlServer.Management.Smo namespace contains classes that represent the core SQL Server Database Engine objects. These include instances, databases, tables, stored procedures, and views.
>>SMO classes include two categories:
Instance classes :databases, tables, triggers, and stored procedures
Utility classes: Transfer, Backup, Restore and scripter
Support all advance feature from sql server :
Table and Index Partitioning
EndPoints
Snapshot Isolation/Row Level Versioning
XML Schema Namespace, XML Indexes and XML datatype
Full-Text Search Enhancements
Page Verify
Snapshot Databases
Service Broker
Index Enhancements
>>SQL Server Management Objects (SMO) Supports programming in Microsoft Visual Basic .NET, Microsoft Visual C# .NET, and Microsoft Visual C++.
>>SQL Server Management Objects (SMO) uses the Microsoft System.Data.SqlClient (we discussed about this in earlier blog)
>>Using smo classes you can do the following:
- Connect to an instance of the SQL Server Database Engine.
- View and modify instance settings and configuration options.
- View and modify database objects.
- Perform DDL (data definition language) tasks on an instance of the SQL Server Database Engine.
- Script databases dependencies.
- Perform database maintenance tasks, such as backup and restore operations.
- Transfer database schemas and data.
>> The list of assembly will be located at “C:\WINDOWS\assembly\” Folder
>>All the classes in the Microsoft.SqlServer.Management.Smo namespace are in following four files:
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoExtended.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.SmoEnum.dll
You can get the list of all the smo classes
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo(v=SQL.100).aspx
>>If you want to know which all Assemblies are loaded
[appdomain]::currentdomain.getassemblies()
>>Lets start the simple example to write a simple script for SMO
Load the assembly
[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | Out-Null
Create the object you want to work with eg. Server,database,table, backup….
For eg. Create an object for sever
$Server1=new-object (“Microsoft.SqlServer.Management.Smo.Server”) ‘server\instance’
Now you are good to go and explore all the server related information.
Our great help…. Get-member
PS C:\>$server1 |gm
