Criando e manipulando Tabelas e Colunas no SQL Server com PowerShell

Partindo da mesma ideia do artigo anterior, apresento agora a utilização do SMO em um script PowerShell com objetivo de criar e manipular uma tabela no SQL Server:

# Referências
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll")

# Objetos
$server = new-object Microsoft.SqlServer.Management.Smo.Server(".\SQLEXPRESS")

# CREATE DATABASE [Estoque]
$database = new-object Microsoft.SqlServer.Management.Smo.Database($server, "Estoque")

$database.Create()

# Tipos a serem utilizados
$constraint_pk = [Microsoft.SqlServer.Management.Smo.IndexKeyType]::DriPrimaryKey
$type_int = [Microsoft.SqlServer.Management.Smo.DataType]::Int
$type_money =[Microsoft.SqlServer.Management.Smo.DataType]::Money
$type_varchar250 = [Microsoft.SqlServer.Management.Smo.DataType]::VarChar(250)
$type_decimal5_2 = [Microsoft.SqlServer.Management.Smo.DataType]::Decimal(2, 5)
$type_datetime = [Microsoft.SqlServer.Management.Smo.DataType]::DateTime

# CREATE TABLE [dbo].[Produto]
$table = new-object Microsoft.SqlServer.Management.Smo.Table($database, "Produto", "dbo")

#   [Id] [int] IDENTITY(1,1) NOT NULL
$col = new-object Microsoft.SqlServer.Management.Smo.Column($table, "Id", $type_int)
$col.Nullable = $FALSE
$col.Identity = $TRUE
$table.Columns.Add($col)

#   [Nome] [varchar](250) NOT NULL
$col = new-object Microsoft.SqlServer.Management.Smo.Column($table, "Nome", $type_varchar250)
$col.Nullable = $FALSE
$table.Columns.Add($col)

#   [Quantidade] [int] NOT NULL
$col = new-object Microsoft.SqlServer.Management.Smo.Column($table, "Quantidade", $type_int)
$col.Nullable = $FALSE
$table.Columns.Add($col)

#   [Valor] [money] NOT NULL
$col = new-object Microsoft.SqlServer.Management.Smo.Column($table, "Valor", $type_money)
$col.Nullable = $FALSE
$table.Columns.Add($col)

#   [Peso] [decimal](5, 2) NOT NULL
$col = new-object Microsoft.SqlServer.Management.Smo.Column($table, "Peso", $type_decimal5_2)
$col.Nullable = $FALSE
$table.Columns.Add($col)

#   [Frete] [money] NULL
$col = new-object Microsoft.SqlServer.Management.Smo.Column($table, "Frete", $type_money)
$col.Nullable = $TRUE
$table.Columns.Add($col)

$table.Create()

# ALTER TABLE [dbo].[Produto]
#   DROP COLUMN [Frete]

$table.Columns["Frete"].Drop()

# ALTER TABLE [dbo].[Produto]
#   ADD CONSTRAINT [PK_Produto] PRIMARY KEY ([Id])
$pk = new-object Microsoft.SqlServer.Management.Smo.Index($table, "PK_Produto")
$pk.IndexKeyType = $constraint_pk

$col = new-object Microsoft.SqlServer.Management.Smo.IndexedColumn($pk, "Id")
$pk.IndexedColumns.Add($col)

$pk.Create()

# Criando colunas em tabelas já existentes:

## Forma #1
# ALTER TABLE [dbo].[Produto]
#   ADD [CategoriaId] [int] NOT NULL
$col = new-object Microsoft.SqlServer.Management.Smo.Column($table, "CategoriaId", $type_int)
$col.Nullable = $FALSE
$table.Columns.Add($col)
$table.Alter()

## Forma #2
# ALTER TABLE [dbo].[Produto]
#   ADD [DataCadastro] [datetime] NOT NULL
$col = new-object Microsoft.SqlServer.Management.Smo.Column($table, "DataCadastro", $type_datetime)
$col.Nullable = $FALSE
$col.Create()

# DROP TABLE [dbo].[Produto]
$table.Drop()

# DROP DATABASE [dbo].[Estoque]
$database.Drop()

Criando e manipulando Tabelas e Colunas no SQL Server com .NET

Mesmo se tratando de algo simples para ser feito com T-SQL, as bibliotecas do SMO permitem ao DBA/desenvolvedor uma forma transparente de criar e manipular objetos do SQL Server sem necessidade de uma linha de T-SQL.

Para um primeiro exemplo, vamos criar um banco de dados:

const string serverName = @".\SQLEXPRESS";

var server = new Server(serverName);

// CREATE DATABASE [Estoque]
var database = new Database(server, "Estoque");
database.Create();

Neste banco de dados, vamos criar uma tabela e suas colunas:

// CREATE TABLE [dbo].[Produto]
var table = new Table(database, "Produto", "dbo");

//   [Id] [int] IDENTITY(1,1) NOT NULL
table.Columns.Add(new Column(table, "Id", DataType.Int) { Nullable = false, Identity = true });

//   [Nome] [varchar](250) NOT NULL
table.Columns.Add(new Column(table, "Nome", DataType.VarChar(250)) { Nullable = false });

//   [Quantidade] [int] NOT NULL
table.Columns.Add(new Column(table, "Quantidade", DataType.Int) { Nullable = false });

//   [Valor] [money] NOT NULL
table.Columns.Add(new Column(table, "Valor", DataType.Money) { Nullable = false });

//   [Peso] [decimal](5, 2) NOT NULL
table.Columns.Add(new Column(table, "Peso", DataType.Decimal(2, 5)) { Nullable = false });

//   [Frete] [money] NULL
table.Columns.Add(new Column(table, "Frete", DataType.Money) { Nullable = true });

table.Create();

Nesta tabela, podemos remover colunas de forma bem simples:

// ALTER TABLE [dbo].[Produto]
//   DROP COLUMN [Frete]
table.Columns["Frete"].Drop();

Assim como adicionar uma Primary Key para a tabela:

// ALTER TABLE [dbo].[Produto]
//   ADD CONSTRAINT [PK_Produto] PRIMARY KEY ([Id])
var pk = new Index(table, "PK_Produto") { IndexKeyType = IndexKeyType.DriPrimaryKey };
pk.IndexedColumns.Add(new IndexedColumn(pk, "Id"));
pk.Create();

Ao se tratar de adicionar colunas em uma tabela, de duas formas isso poderá ser feito, sendo a primeira por meio o objeto Table e o método Alter:

// ALTER TABLE [dbo].[Produto]
//   ADD [CategoriaId] [int] NOT NULL
table.Columns.Add(new Column(table, "CategoriaId", DataType.Int) { Nullable = false });
table.Alter();

E a segunda forma, pelo objeto Column e o método Create:

// ALTER TABLE [dbo].[Produto]
//   ADD [DataCadastro] [datetime] NOT NULL
var col = new Column(table, "DataCadastro", DataType.DateTime) { Nullable = false };
col.Create();

Para apagar uma tabela ou até mesmo o banco de dados inteiro, podemos utilizar o método Drop do respectivo objeto a ser apagado:

// DROP TABLE [dbo].[Produto]
table.Drop();

// DROP DATABASE [dbo].[Estoque]
database.Drop();

Na próxima semana, veremos também como é possível criar Foreign Keys, Unique Keys, Default Constraints e Indexes com SMO.

Continuar lendo

Gerando scripts do banco de dados por PowerShell – Passo 2

Partindo da mesma ideia de utilizar o objeto Scripter do SMO para gerar o scripts do SQL Server por .NET, agora veremos como fazer o mesmo procedimento com PowerShell:

# Referências
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll")
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll")

# Objetos
$srv = new-object Microsoft.SqlServer.Management.Smo.Server(".\SQLEXPRESS")

$db = $srv.Databases["DemoAspNet"]

# Gerando o script do banco de dados
$script = $db.Script()

$script += "`nUSE [DemoAspNet]`n"

# Relacionando as URNs dos objetos que serão "scriptados"

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$roles =
    $db.Roles | Where { $_.IsFixedRole -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$schemas =
    $db.Schemas | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$tables =
    $db.Tables | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$views =
    $db.Views | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$procs =
    $db.StoredProcedures | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

$urn = new-object Microsoft.SqlServer.Management.Smo.UrnCollection

$urn.AddRange($roles)
$urn.AddRange($schemas)
$urn.AddRange($tables)
$urn.AddRange($views)
$urn.AddRange($procs)

$scr = new-object Microsoft.SqlServer.Management.Smo.Scripter
$scr.Server = $srv
$scr.Options.DriAll = $TRUE

# Gerando o script dos objetos pelo Scripter
$script += $scr.Script($urn)

# Salvando o script gerado em um arquivo
$go = "`nGO`n"

$script | % { $_ + $go} | Out-File "C:\Temp\DemoAspNet.sql"

Forma resumida para ser executado dentro do próprio contexto do SQL Server (Jobs, PowerShell do SQL Server ou sqlps.exe):

# Sem referências

# Objetos
sl SQLSERVER:\SQL\LOCALHOST\SQLEXPRESS

$srv = get-item .

sl Databases\DemoAspNet

$db = get-item .

# Gerando o script do banco de dados
$script = $db.Script()

$script += "`nUSE [DemoAspNet]`n"

# Relacionando as URNs dos objetos que serão "scriptados"

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$roles =
    dir Roles | Where { $_.IsFixedRole -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$schemas =
    dir Schemas | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$tables =
    dir Tables | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$views =
    dir Views | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$procs =
    dir StoredProcedures | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

$urn = new-object Microsoft.SqlServer.Management.Smo.UrnCollection

$urn.AddRange($roles)
$urn.AddRange($schemas)
$urn.AddRange($tables)
$urn.AddRange($views)
$urn.AddRange($procs)

$scr = new-object Microsoft.SqlServer.Management.Smo.Scripter
$scr.Server = $srv
$scr.Options.DriAll = $TRUE

# Gerando o script dos objetos pelo Scripter
$script += $scr.Script($urn)

# Salvando o script gerado em um arquivo
$go = "`nGO`n"

$script | % { $_ + $go} | Out-File "C:\Temp\DemoAspNet.sql"

Gerando scripts do banco de dados por .NET – Passo 2

Após entendermos como utilizar o método de Script dos objetos do SMO com a finalidade de gerar scripts dos objetos do SQL Server, esta semana veremos como utilizar o Script, um objeto um pouco mais complexo com a finalidade de gerar o script de vários objetos de uma só vez.

Antes de conhecermos o objeto Scripter, é importante entender que os objetos do SQL Server no SMO possuem uma URN, um caminho para identifica-los no SQL Server, exemplo:

{Server[@Name=’SERVIDOR\INSTANCIA’]/
Database[@Name=’BANCO DE DADOS’]/
TipoDoObjeto[@Name=’OBJETO’ …]}

Com o nosso objeto Scripter, poderemos gerar scripts tanto informando os objetos que serão “scriptados” ou as URNs destes. Visto que as URN são uma forma mais simples de referenciar os objetos a serem “scriptados”, trabalharemos o nosso exemplo de utilização do Scripter com URNs:

using System;
using System.IO;
using System.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    class DemoScripter
    {
        static void Main()
        {
            const string serverName = @".\SQLEXPRESS";

            const string databaseName = "DemoAspNet";

            const string scriptPath = @"C:\TEMP\DemoAspNet.sql";

            var server = new Server(serverName);

            var database = server.Databases[databaseName];

            var urn = new UrnCollection();

            Console.WriteLine("Carregando roles...");

            var roles = database.Roles.Cast<DatabaseRole>()
                .Where(obj => !obj.IsFixedRole)
                .Select(obj => obj.Urn);

            urn.AddRange(roles);

            Console.WriteLine("Carregando schemas...");

            var schemas = database.Schemas.Cast<Schema>()
                .Where(obj => !obj.IsSystemObject)
                .Select(obj => obj.Urn);

            urn.AddRange(schemas);

            Console.WriteLine("Carregando tables...");

            var tables = database.Tables.Cast<Table>()
                .Where(obj => !obj.IsSystemObject)
                .Select(obj => obj.Urn);

            urn.AddRange(tables);

            Console.WriteLine("Carregando views...");

            var views = database.Views.Cast<View>()
                .Where(obj => !obj.IsSystemObject)
                .Select(obj => obj.Urn);

            urn.AddRange(views);

            Console.WriteLine("Carregando procedures...");

            var procs = database.StoredProcedures.Cast<StoredProcedure>()
                .Where(obj => !obj.IsSystemObject)
                .Select(obj => obj.Urn);

            urn.AddRange(procs);

            using (var file = new StreamWriter(scriptPath))
            {
                Console.WriteLine("Script do banco de dados...");

                foreach (var str in database.Script())
                {
                    file.WriteLine(str);
                    file.WriteLine("GO");
                }

                file.WriteLine("USE [{0}]", database.Name);
                file.WriteLine("GO");

                Console.WriteLine("Script dos objetos...");

                //Definindo o Scripter e o ScriptOptions
                var scripter = new Scripter(server) { Options = { DriAll = true } };

                foreach (var str in scripter.Script(urn))
                {
                    file.WriteLine(str);
                    file.WriteLine("GO");
                }
            }

            Console.WriteLine("Script concluído ...");

            Console.ReadKey();
        }
    }
}

Neste exemplo, gerei o script das roles, schemas, tabelas, views e procedures de um banco de dados simples que possuo, mas de acordo com seu cenário, também será possível/necessário relacionar functions, índices e triggers da mesma forma que foi realizado com os outros objetos.

Gerando scripts do banco de dados por PowerShell – Passo 1

Tento já explicado como utilizar o SMO para gerar o scripts do SQL Server por .NET, agora teremos a alternativa com PowerShell:

# Referências
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll")

# Objetos
$srv = new-object Microsoft.SqlServer.Management.Smo.Server(".\SQLEXPRESS")

$db = $srv.Databases["DemoAspNet"]

# 1. Gerando o Script do banco de dados
$db.Script() | Out-File "C:\Temp\DemoAspNet.sql"

# 2. Gerando o Script das tabelas do banco de dados
$db.Tables | % { $_.Script() } | Out-File "C:\Temp\DemoAspNet_Tables.sql"

# 3. Script com o GO entre as linhas (Forma 1)
$go = "`nGO`n"
$db.Tables | % { $_.Script() | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_Tables.sql"

# 4. Script com o GO entre as linhas (Forma 2)
$go = "
GO
"
$db.Tables | % { $_.Script() | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_Tables.sql"

# 5. Gerando o DROP das tabelas
$options = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.ScriptDrops = $TRUE

$go = "`nGO`n"
$db.Tables | % { $_.Script($options) | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_TableDrop.sql"

# 6. Gerando o script das tabelas com as Primary Keys
$options = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.DriPrimaryKey = $TRUE

$go = "`nGO`n"
$db.Tables | % { $_.Script($options) | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_TableKey.sql"

# 7. Gerando o script das tabelas com as Keys e Constraints, sem Collation
$options = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.DriAll = $TRUE
$options.NoCollation = $TRUE

$go = "`nGO`n"
$db.Tables | % { $_.Script($options) | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_TableAll.sql"

Forma resumida para este script se executado dentro do próprio contexto do SQL Server (Jobs, PowerShell do SQL Server ou sqlps.exe):

# Sem referências

# Objetos
sl SQLSERVER:\SQL\LOCALHOST\SQLEXPRESS\Databases\DemoAspNet

$db = get-item .

# 1. Gerando o Script do banco de dados
$db.Script() | Out-File "C:\Temp\DemoAspNet.sql"

# 2. Gerando o Script das tabelas do banco de dados
dir Tables | % { $_.Script() } | Out-File "C:\Temp\DemoAspNet_Tables.sql"

# 3. Script com o GO entre as linhas (Forma 1)
$go = "`nGO`n"
dir Tables | % { $_.Script() | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_Tables.sql"

# 4. Script com o GO entre as linhas (Forma 2)
$go = "
GO
"
$db.Tables | % { $_.Script() | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_Tables.sql"

# 5. Gerando o DROP das tabelas
$options = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.ScriptDrops = $TRUE

$go = "`nGO`n"
dir Tables | % { $_.Script($options) | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_TableDrop.sql"

# 6. Gerando o script das tabelas com as Primary Keys
$options = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.DriPrimaryKey = $TRUE

$go = "`nGO`n"
dir Tables | % { $_.Script($options) | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_TableKey.sql"

# 7. Gerando o script das tabelas com as Keys e Constraints, sem Collation
$options = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.DriAll = $TRUE
$options.NoCollation = $TRUE

$go = "`nGO`n"
dir Tables | % { $_.Script($options) | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_TableAll.sql"

Artigo relacionado:

PowerShell no SQL Server – Step 1

Gerando scripts do banco de dados por .NET – Passo 1

Criar scripts do banco de dados, ou de qualquer objeto do SQL Server por .NET não é algo muito complexo, pois os principais objetos do SMO possuem o método Script. Mas como gerar o script desejado é o que pode dar um pouco de dor de cabeça para quem esta começando com SMO, seja no .NET ou no PowerShell.

Para primeira demonstração, vamos ver como é simples gerar o script de criação do banco de dados:

using System;
using System.IO;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    class DemoScript1
    {
        static void Main()
        {
            const string serverName = @".\SQLEXPRESS";

            const string databaseName = "DemoAspNet";

            const string scriptPath = @"C:\TEMP\DemoAspNet.sql";

            var server = new Server(serverName);

            var database = server.Databases[databaseName];

            // Gerando o script do banco de dados e salvando este em um arquivo
            using (var file = new StreamWriter(scriptPath))
            {
                Console.WriteLine("Script do banco de dados...");

                foreach (var str in database.Script())
                {
                    file.WriteLine(str);
                    file.WriteLine("GO");
                }
            }

            Console.WriteLine("Script concluído ...");

            Console.ReadKey();
        }
    }
}

Agora a geração do script das tabelas do banco de dados:

using System;
using System.IO;
using System.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    class DemoScript2
    {
        static void Main()
        {
            const string serverName = @".\SQLEXPRESS";

            const string databaseName = "DemoAspNet";

            const string scriptPath = @"C:\TEMP\DemoAspNet.sql";

            var server = new Server(serverName);

            var database = server.Databases[databaseName];

            using (var file = new StreamWriter(scriptPath))
            {
                Console.WriteLine("Script das tabelas...");

                // Relacionando as tabelas do banco de dados
                foreach (var table in database.Tables.Cast<Table>().Where(table => !table.IsSystemObject))
                {
                    // Gerando o script de cada uma das tabelas
                    file.WriteLine("--");
                    file.WriteLine("-- Tabela: [{0}].[{1}]", table.Schema, table.Name);
                    file.WriteLine("--");
                    foreach (var str in table.Script())
                    {
                        file.WriteLine(str);
                        file.WriteLine("GO");
                    }
                }
            }

            Console.WriteLine("Script concluído ...");
            Console.ReadKey();
        }
    }
}

Na geração do script das tabelas, é bem provável que você vá se perguntar “onde estão as constraints das tabelas?”, ai entra o objeto ScriptOptions, o qual pode ser utilizado como argumento para o método Script, afim de definir como e do que será gerado o script.

Para entender isso, temos abaixo um ScriptOptions configurado para gerar o script das tabelas com as Primary Keys (DriPrimaryKey):

var scriptOptions = new ScriptingOptions { DriPrimaryKey = true };

O ScriptOptions para gerar o script de DROP das tabelas (ScriptDrops):

var scriptOptions = new ScriptingOptions { ScriptDrops = true };

E para um exemplo final, utilizando o ScriptOption afim de gerar o script das tabelas com as Keys e Constraints (DriAll) e sem informar Collation das colunas (NoCollation):

using System;
using System.IO;
using System.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    class DemoScript3
    {
        static void Main()
        {
            const string serverName = @".\SQLEXPRESS";

            const string databaseName = "DemoAspNet";

            const string scriptPath = @"C:\TEMP\DemoAspNet.sql";

            var server = new Server(serverName);

            var database = server.Databases[databaseName];

            //Definindo o objeto ScriptingOptions
            var scriptOptions = new ScriptingOptions { DriAll = true, NoCollation = true };

            using (var file = new StreamWriter(scriptPath))
            {
                Console.WriteLine("Script das tabelas...");

                foreach (var table in database.Tables.Cast<Table>().Where(table => !table.IsSystemObject))
                {
                    //ScriptingOptions com argumento do método Script
                    file.WriteLine("\n\n--\n-- Tabela: [{0}].[{1}]\n--\n", table.Schema, table.Name);
                    foreach (var str in table.Script(scriptOptions))
                    {
                        file.WriteLine(str);
                        file.WriteLine("GO");
                    }
                }
            }

            Console.WriteLine("Script concluído ...");
            Console.ReadKey();
        }
    }
}

No artigo da próxima semana veremos como utilizar o objeto Scripter para gerar o script de vários objetos ao mesmo tempo, e ainda nesta semana a versão deste artigo para PowerShell.

Gerando backups do SQL Server por PowerShell

No primeiro artigo desta semana vimos como gerar backups do SQL Server por .NET, agora veremos alternativas mais simples para tal, com o PowerShell.

Para o nosso primeiro script de Backup com PowerShell, precisaremos referenciar os assemblies do SMO, e criar um código semelhante ao que vimos com .NET:

# Referências
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll")
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.SmoExtended.dll")

# Objetos
$srv = new-object Microsoft.SqlServer.Management.Smo.Server(".\SQLEXPRESS")

$bk = new-object Microsoft.SqlServer.Management.Smo.Backup

# Definindo de qual banco de dados será executado o backup
$bk.Database = "BASE1"

# Definindo onde será salvo o backup
$bk.Devices.AddDevice("C:\TEMP\BASE1_PowerShell.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

# Executando o backup
$bk.SqlBackup($srv)

Mas quando no contexto do PowerShell do SQL Server (scripts executados em Jobs ou diretamente pelo PowerShell presente no SQL Server Management Studio ou SQLPS.exe), teremos algumas facilidades, como não precisar referenciar os assemblies do SMO e utilizar de “atalhos” para instanciar as variáveis do SQL Server (ex.: Smo.Server):

# Sem referências

# Objetos
$srv = get-item SQLSERVER:\SQL\LOCALHOST\SQLEXPRESS

$bk = new-object Microsoft.SqlServer.Management.Smo.Backup

# Definindo de qual banco de dados será executado o backup
$bk.Database = "BASE1"

# Definindo onde será salvo o backup
$bk.Devices.AddDevice("C:\TEMP\BASE1_PowerShell.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

# Executando o backup
$bk.SqlBackup($srv)

Referências:

How To Load .NET Assemblies In A PowerShell Session