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);