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

Anúncios

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

Continuar lendo

Restaurando dados de um horário específico no SQL Server

E ai pessoas! Hoje, vou tratar novamente de backups!

Mas o que ainda tem de importante em backups, para eu retornar a este assunto? Simples! Já pensou em recuperar “dados pedidos em um ‘DELETE sem WHERE’ qualquer” realizados as 20h05min do dia 27/10/2010? Sim! Isso é possível! Mas vamos primeiramente preparar o ambiente para nos proporcionar isso!

O primeiro passo é ajustar o “Recovery model” do banco de dados para “Full”:

O segundo passo é realizar um backup “Full”:

E pronto! A partir de agora você pode destruir tranquilamente seus dados (mas, não se esqueça de lembrar a que horas você começou a destruir seus dados):

Dados foram mandados para o LIMBO? E agora… Bem agora, passe no RH… Mas você pode tentar fazer um Backup “Transaction Log”:

Se prepare para restaurar seu backup “Full” e os “Transaction Log” em um novo banco de dados, mas antes de executar a restauração clique no botão de seleção na frente do “To a point in time: Most recent possible”:

Informe um horário anterior à destruição dos dados e inicie a restauração:

Veja que seus dados foram restaurados corretamente no ponto de restauração que você informou:

Agora é só iniciar um processo de restauração dos seus dados manualmente ou utilizar alguma ferramenta de Data Compare, como as da RedGate ou do Visual Studio, ou até mesmo o Import\Export do SQL Server Management Studio, para ajustar seus dados.

Obs.: SQL Server não restaura dados anteriores ao horário do backup Full que você possui (No-Miracles, please).

Plano de Manutenção: Automatizando Backups no SQL Server

Será que o SQL Server não pode fazer backups por você? Há alguns artigos atrás, expliquei como criar backups nos bancos de dados, mas será que este processo não poderia ser automatizado?

Para responder estas perguntas, vou demonstrar como criar de forma simples um plano de manutenção do SQL Server pelo Management Studio de forma a permitir backups diários, ou seja, automatizar o processo de backups.

O primeiro passo é verificar se o SQL Server Agent esta executando, pois ele é o responsável pela automatização do seu plano de manutenção que você (e outras funcionalidades que não veremos por enquanto):

Em seguida, se aventure pela pasta “Management” para encontrar a pasta “Maintenance Plan”:

Agora adicione um novo plano de manutenção pelo Wizard (por favor, não fale mal de Wizards, pois são boas ferramentas para definir os cenários comuns de forma simples e rápida):

Na primeira tela, nomeie o seu plano de manutenção:

E no botão “Change”, defina quando este plano de manutenção será executado, exemplo:

Diariamente à meia-noite… ficará:

Frequência: diária
Frequência Diária: Ocorrente sempre à meia-noite

Concluídas as alterações, vamos ao próximo passo, no qual você escolherá o que será realizado por este plano de manutenção (no caso, backup full):

Ignore o passo seguinte, visto que somente estamos tratando uma única task e não precisamos definir a ordem de execução de tasks neste cenário:

No próximo passo, configure quais os bancos que serão afetados pelo backup e onde estes backups serão armazenados:

Ao escolher os bancos que serão afetados pelo backup, você pode optar por realizar backup somente dos bancos específicos, somente dos bancos de sistema, ou como no caso apresentado, somente bancos de dados que não são de sistema:

Você pode ignorar as opções do próximo passo, relacionadas a alertas e log:

E finalizar a criação do plano de manutenção:

Pronto, agora você possui seu plano de backups:

Caso seja necessário executar este plano de manutenção fora do horário previsto, você pode executá-lo manualmente pela opção “Execute”:

Após a execução manual ou agendada, você poderá encontrar seus backups:

Então, este foi um breve passo-a-passo de como criar planos de manutenção. Após criar seu primeiro plano, estude como funcionam os outros tipos de backup para criar planos mais complexos e que permitam uma estratégia recuperação dos dados adequada à sua necessidade.

Até o próximo post!

Usuários órfãos no SQL Server? Evite este problema!

Depois de um bom tempo tentando resolver problemas de usuários órfãos após backups, migração de bancos, log shipping, mirroring e outras ocorrências, pude verificar em diversos blogs e artigos na internet algumas formas de resolver este problema, mas não vi formas de evitar que ele aconteça… O que me deixou impressionado, pois o problema é bem mais simples do que parece (depois que você descobre como funciona, realmente parece simples).

Um exemplo comum de usuário órfão:

Usuário Órfão

Solução para o problema:

Para quem chegou a este artigo procurando solucionar um problema de usuários órfãos, você possui várias alternativas:

No SQL Server 2000 e SQL Server 2005:

-- Associando a um login existente:
EXEC sp_change_users_login 'Update_One', 'nome do usuário', 'nome do login'

 

--Associando a um login existente com o mesmo nome do usuário,
--Ou se não existir login com o mesmo nome do usuário, criar um com a senha informada:
EXEC sp_change_users_login 'Auto_Fix', 'nome do usuário', NULL, 'senha'

(ref.: http://msdn.microsoft.com/pt-br/library/ms175475.aspx)

A partir do SQL Server 2005 e SQL Server 2008:

-- Associando a um login existente:
ALTER USER [nome do usuario] WITH LOGIN = [nome do login] 

(ref.: http://msdn.microsoft.com/en-us/library/ms176060.aspx)

Agora vamos saber por que isso ocorre:

Os usuários do banco de dados são associados a um código de segurança (SID) dos logins da instância do banco de dados, mas como este SID pode ser aleatório na criação dos logins, o fato de você possuir um login com mesmo nome em duas instâncias do SQL Server distintas, não quer dizer eles são iguais, pois quando o SQL Server tenta restaurar um banco de dados de outra instância, ele só consegue identificar os logins “pais” de seus usuários pelo SID.

Mas se eu forçar um SID para o meu login nas instâncias do SQL Server, será que ainda vou ter o problema? Então vamos conferir:

Utilizei a seguinte consulta para recuperar o SID de um determinado login:

SELECT name, sid FROM sys.server_principals WHERE type = 'S'

SELECT name, sid FROM sys.server_principals WHERE type = 'S'

Em outro SQL Server, vou criar um login com mesmo SID:

CREATE LOGIN paulo
WITH PASSWORD = 'p@$$w0rd',
SID = 0x0F5AE6C15103B647A7BD41F744C256F3

Após restaurar o banco de dados neste outro servidor, como resultado, sem usuário órfão!

Usuário Adotado

E se você tiver um login com nome diferente do login da outra instância, mas com um mesmo SID, o SQL Server ainda utilizará o SID como critério para associar os usuários aos seus respectivos logins, exemplo:

Usuário Estranhamente Adotado

Então, criar logins com SIDs iguais entre instâncias do SQL Server, evitará que problemas de usuários órfãos ocorram novamente, seja por backups, migração de bancos, log shipping e mirroring! A partir de agora, só diversão!

Backup no SQL Server, por onde começar?

E ai pessoas!

Normalmente eu evitava falar sobre a parte administrativa do SQL Server, visto eu na minha “inocência” considerava natural que bancos de dados em ambiente de produção tinham rotinas de manutenção e nestas rotinas, atividades regulares de backups. Mas o que julgo ser natural é diversas vezes desconsiderado e só lembrado quando a recuperação dos dados se torna irreversível (uma das situações conhecidas como “DBA HELL”).

Para ninguém dizer que backup é difícil, vamos realizar “manualmente” um backup e seguida demonstrar como restaurar este backup.

No SQL Server Management Studio, escolhemos um banco de dados qualquer:

Clicamos com o botão direito sobre o banco de dados, e em “Tasks” escolhemos o item “Back Up…”:

Na nova janela que irá aparecer, adicionaremos os destinos para o nosso backup, clicando no botão “Add”:

E em seguida informaremos o caminho destino do backup (recomendo utilizar a extensão “.bak” para no nome do arquivo):

Uma opção muito importante quando você quer somente uma cópia do banco de dados, mas não quer interferir em qualquer plano de manutenção com backups que a sua empresa possuir, selecione a propriedade “Copy Only Backup” (isso pode evitar momentos de stress quando for necessário que o DBA da sua empresa precise restaurar o banco de dados):

Agora basta clicar em OK e concluímos nosso backup!

E por fim o arquivo de backup que você pode levar para qualquer lugar, mas lembre de não deixar seus backups dispostos de forma insegura, pois qualquer pessoa que souber restaurar seu backup poderá utilizar estes dados para outros fins.

Backup realizado, e quando houver necessidade de recuperar dados que foram salvos no seu backup como faremos?

Para restaurar um banco de dados a partir de um backup, basta escolher nas “Tasks” o menu “Restore”, a opção “Database…”

Escolher a opção “From device” e em seguida clique no botão “…” na frente desta opção:

Adicione seu arquivo de backup pelo botão “Add”:

Feito isso aperte “OK” e selecione o backup que você deseja restaurar:

A partir deste passo, temos duas opções:

Caso você deseje criar um novo banco de dados a partir deste backup, nomeie seu banco de dados com um nome diferente em “To database” e em seguida clique em OK:

Caso você deseje sobrescrever (ou substituir) um banco de dados já existente, selecione o nome do banco de dados já existente em “To database” e na página “Options” selecione a opção “Overwrite the existing database (WITH REPLACE)” e em seguida clique em OK:

Obs.: Verifique também, se realmente quer restaurar os arquivos do banco de dados nos caminhos informados na grid de “Restore As”.

A partir destes exemplos, você estará preparado para realizar backups e restaurar bancos de dados. Nos próximos artigos tratarei algumas formas de automatização deste processo e algumas ações comuns para restaurar partes dos bancos de dados.

SQL Server Day – Grande tecnologia, por grandes profissionais!

sqlserverday

O evento SQL Server Day 2009 trará mais de 12 horas de palestras com os maiores nomes de SQL Server no Brasil. Este primeiro evento será realizado no dia 07/11/2009 começando às 9 hs da manhã, mas como é on-line, o coffee break fica por sua conta!

Pelo post do Zavaschi, a grade do evento será a seguinte:

09:30 – 10:00 – Abertura do Evento
10:00 – 11:00 – Resource Monitor e Policy Management – Vitor Fava e Alexandre Lopes
11:00 – 12:00 – Entenda porque o Query Optimizer é mais esperto que você – Fabiano Amorim
12:00 – 13:00 – SQL Azure Database – Diego Nogare
13:00 – 14:00 – Novos Recursos de Desenvolvimento do SQL Server 2008 – Higor Fernandes
14:00 – 15:00 – Compressão de Dados e Backup no Microsoft SQL Server 2008 – Pedro A. G. Junior
15:00 – 16:00 – Entendendo TDE (Transparent Data Encryption) – Felipe Ferreira
16:00 – 17:00 – Entendendo as Common Table Expressions (CTE) – Thiago Zavaschi
17:00 – 18:00 – Powershell Coletando e Analisando os Dados – Laerte Junior e Thiago Zavaschi
18:00 – 19:00 – Solução Avançada de Problemas com Extended Events – Vladimir Magalhães
19:00 – 20:00 – Disaster Recovery – Backup, Restore e Tópicos Avançados – Gustavo Maia Aguiar
20:00 – 21:00 – Integre seu código .NET com o SQL Server usando o CLR – Roberto Fonseca
21:00 – 22:00 – Analise de Desempenho utilizando as Estatísticas de Espera – Alex Rosa

 

Mais informações:

SQL Server Day – Site Oficial
http://www.sqlserverday.com.br