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.

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

Gerando backups do SQL Server por .NET

No artigo da semana passada, tivemos uma breve introdução à biblioteca SMO, que pode ser tanto utilizada pelo .NET quanto pelo PowerShell para criar aplicações ou scripts para gerenciar o SQL Server.

Nesta semana, demonstrarei como utilizar as classes Smo.Backup e Smo.BackupDeviceItem, de um outro assembly do SMO, o SmoExtended, que poderá ser encontrado no caminho:

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.SmoExtended.dll

As classes Smo.Backup e Smo.BackupDeviceItem são responsáveis respectivamente por gerar backup e determinar onde este será salvo.

Para demonstrar a utilização destas classes, adequei o exemplo da semana passada, que relacionava os bancos de dados de uma determinada instancia do SQL Server, para também possibilitar identificar a data de ultimo backup de um determinado banco de dados e realizar o backup do mesmo:

Ajuste realizado:

// Gerando a classe Smo.Backup
var backup = new Backup { Database = database.Name };

// Definindo com o Smo.BackupDeviceItem onde será salvo o backup
var path = string.Format(@"C:\Temp\{0}_{1:yyyy-MM-dd HH_mm_ss}.bak", database.Name, DateTime.Now);

backup.Devices.AddDevice(path, DeviceType.File);

// Executando o backup
backup.SqlBackup(server);

Gerenciando o SQL Server por meio de aplicações .NET – Primeiros passos

Já tratamos no blog duas formas de interação da .NET Framework com o SQL Server, sendo a primeira e principal forma interação, por ADO.NET para execução de comandos T-SQL,  e a segunda forma, por SQL CLR para estender funcionalidades do SQL com .NET.

Além destas duas formas de interação, temos o SMO (SQL Server Management Objects) que nos permite gerenciar o SQL Server utilizando um coleção de assemblies .NET, que podem ser utilizados em scripts do PowerShell ou em aplicações .NET de forma bem transparente.

Há alguns anos escrevi um artigo para revista Mundo .NET sobre este assunto, mas não demonstrei muito do potencial que temos com SMO.

A partir deste artigo, pretendo iniciar uma nova série de artigos que tratarão várias formas de utilizar SMO com .NET e também com PowerShell (mais como um comparativo de como fazer com .NET e com PowerShell).

Hoje, vamos começar com uma aplicação .NET simples que ilustra bem a utilização do SMO.

Tento um projeto .NET, o primeiro passo será encontrar os assemblies que compõe a SMO, no caso do SQL Server 2008 (100). Eles podem ser encontrados nas pastas:

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

Caso não os encontre, você pode instalá-los:
http://www.microsoft.com/download/en/details.aspx?id=16177#SMO
(Microsoft SQL Server 2008 Management Objects)

Para a nossa primeira aplicação, precisaremos dos seguintes assemblies referenciados em nossa aplicação:

Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll

Adicionadas as referencias ao projeto, será possível criar uma aplicação simples em .NET (em C# no meu caso) para relacionar os bancos de dados de uma determinada instancia do SQL Server:

using System;
using System.Linq;
using System.Security;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace DemoSmo
{
    class Program
    {
        static void Main()
        {
            Console.WriteLine("Servidor:");
            var serverInstance = Console.ReadLine();
            Console.WriteLine();

            Console.WriteLine("Usuário:");
            var user = Console.ReadLine();
            Console.WriteLine();

            ServerConnection conn;

            if (!string.IsNullOrEmpty(user))
            {
                Console.WriteLine("Senha:");
                var pass = ReadPassword();
                Console.WriteLine();

                // Criando um objeto de conexão com SQL Authentication
                conn = new ServerConnection(serverInstance, user, pass);
            }
            else
            {
                // Criando um objeto de conexão com Windows Authentication
                conn = new ServerConnection(serverInstance);
            }

            // Criando um objeto que abstrai a instância do SQL Server
            var server = new Server(conn);

            Console.WriteLine("Carregando lista de bancos de dados ...");
            Console.WriteLine();

            try
            {
                // Recuperando a lista de banco de dados
                var lst = server.Databases;

                // Escrevendo a relação de banco de dados encontrados
                foreach (var db in lst.Cast<Database>().OrderBy(e => e.ID))
                {
                    Console.WriteLine("{0}. {1}", db.ID, db.Name);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            Console.WriteLine();
            Console.WriteLine("Concluído ...");
            Console.ReadKey();
        }

        // Método para evitar que a senha digitada apareça na tela da aplicação
        private static SecureString ReadPassword()
        {
            var pass = new SecureString();

            while (true)
            {
                var key = Console.ReadKey(true);

                if (key.Key == ConsoleKey.Enter) break;

                pass.AppendChar(key.KeyChar);

                Console.Write("*");
            }

            Console.WriteLine();

            return pass;
        }
    }
}

E por fim, executando a aplicação por SQL Authentication:

E por Windows Authetication:

Por enquanto não há muitos materiais no blog sobre SMO, então você pode esperar novos artigos serem publicados, encontrar outras fontes pela internet ou comprar a revista que tem o artigo que escrevi sobre SMO.

Até o próximo artigo!

Mundo.Net: Gerenciando o SQL Server através de aplicações .Net

Pessoal, recebi hoje meu exemplar da Revista Mundo.Net e acabei encontrando ótimos artigos por lá, como desenvolvimento para Sharepoint 2010 (Alex Schulz), SQL Azure Database (Waldemir Cambiucci) e o case do meu amigo Valdair Ziegler sobre “VSTO + WPF + ASP.NET” e vários outros muito bons.

Mas o melhor ainda eu não comentei… “Gerenciando o SQL Server através de aplicações .Net” artigo escrito por um ser das trevas, que mostrou que até sem nenhum comando T-SQL, uma aplicação .NET pode gerenciar perfeitamente o SQL Server e de forma muito prática, abrindo um grande leque de possibilidades! Já é de imaginar que o maluco que escreveu o artigo sou eu, mas para saber mais só comprando a Revista Mundo.Net: Edição 19!

Revista Mundo.Net:
http://www.mundodotnet.com.br

Códigos-fontes relacionados:
http://bit.ly/aGKb7h