O objetivo desses scripts é manter uma documentação completa (lista de logins, databases, user, jobs, etc..) de
um servidor SQL Server é um Best Practice que pode nos economizar um tempo
precioso na hora de reinstalar ou migrar um servidor, recriar ou copiar logins
e databases entre servidores, etc... Neste artigo estarei descrevendo uma
stored procedure que você poderá executar diariamente em seus servidores de
forma a manter uma documentação completa dos mesmos.
Quanto tempo você poderia ganhar se todas as vezes que for migrar ou reinstalar
um servidor ou migrar logins e databases entre servidores você já tiver em mãos
todos os scripts dos logins e
senhas, attach
dos databases, users de
cada database, default
database de cada login, jobs, devices
de backup e linked
servers do servidor?
Pois bem,
neste artigo estarei descrevendo uma stored procedure que quando executada
realiza as seguintes atividades:
1. Gera um script
de attach para todos os databases existentes no servidor.
2. Gera
um script com todos os logins e suas respectivas senhas.
3. Gera
um script com o default database de cada login.
4. Gera
um script com todos os devices de backup.
5. Gera
um script com todos os jobs.
6. Gera
um script com todos os linked servers.
7. Gera
um script com todos os usuários e roles (grupos) existentes em cada database.
8. Gera
um script com as configuração globais do servidor (sp_configure).
A STORED PROCEDURE
Na verdade
todo este trabalho é realizado por um conjunto de 9 stored procedures onde cada
uma delas possui uma função específica. A procedureusp_docservidor é
a procedure principal e deve ser executada passando como referência o caminho
onde os scripts deverão ser armazenados.
Exemplo:
--
Se estiver conectado na base Pubs
EXEC usp_docservidor 'C:/temp/doc_servidor'
ou
-- Se estiver conectado a qualquer outra base
diferente da Pubs
EXEC pubs..usp_docservidor 'C:/temp/doc_servidor'
Nota: O
script cria todas as procedures na base de dados Pubs. No entanto, as
mesmas podem ser criadas em qualquer base de sua preferência. Basta alterar o
nome da base no início do script e dentro da procedure principal.
Ao chamar a
procedure principal, a mesma chama as outras 8 procedures, também passando como
referência o caminho onde os scripts deverão ser armazenados. Embora estas 8
stored procedures sejam chamadas pela procedure principal, elas também podem
ser executadas individualmente.
Exemplo:
EXEC
pubs..usp_doclogins 'C:/temp/doc_servidor'
EXEC pubs..usp_docusers 'C:/temp/doc_servidor'
O nome das procedures
e a função de cada uma delas são descritas abaixo:
1.
usp_doclogins: Esta procedure tem como função gerar um script contendo
todos os logins e suas respectivas senhas. Para levar estes logins para outro
servidor, basta pegar o script e executá-lo no servidor desejado.
2.
usp_docusers: Tem
como função gerar um script com todos os usuários e roles (grupos) existentes
em cada banco de dados. Para levar estes usuários e grupos para uma base em
outro servidor, basta pegar o script e executá-lo na base do servidor desejado.
3.
usp_docdbs: Cria
um script que permitirá executar um attach dos databases existentes no servidor
na order em que foram criados, ou seja, mantendo seus dbids. A procedure também
cria um script para reassociar os logins a seus respectivos default databases.
O script do
arquivo permite executar um attach dos databases, porém os respectivos arquivos
.mdf e .ldf dos databases devem estar em suas localizações originais. Caso
tenha copiado os arquivo .mdf e .ldf de um database para outro servidor, altere
o script de forma a apontar para o caminho (letras do disco onde os arquivos
foram copiados) do novo servidor.
4.
usp_docdevices: Cria
um script que permitirá recriar os devices de backup existentes no servidor.
Para recriar estes devices em outro servidor, basta pegar o script e executá-lo
no servidor desejado. Neste caso é preciso ficar atendo ao caminho (letra do
disco) onde os devices serão recriados.
5.
usp_docjobs: Tem
como função gerar um script que permitirá recriar todos os jobs existentes no
servidor. Para recriar estes jobs em outro servidor, basta pegar o script e
executar no servidor desejado. Neste caso, lembre-se de alterar o valor da
variável @server_name para
o nome do servidor de destino.
6.
usp_doclinkedsrv: Tem
como função gerar um script que permitirá recriar todos os linked servers
existentes no servidor. Para recriar estes linked servers em outro servidor,
basta pegar o script e executar no servidor desejado.
7.
usp_docconfig: Cria
um script que permitirá reconfigurar as configurações globais do servidor de
acordo com os últimos valores em execução (sp_configure).
8.
sp_hexadecimal: Esta
procedure é criada no banco de dados MASTER e é utilizada pela
procedure usp_doclogins para
recuperar a senha dos logins.
Bom, como
se pôde notar, estes scripts simplificam a realização de várias atividades,
entre elas:
a) Migrar ou
reinstalar um servidor.
b) Migrar
logins entre servidores ou simplesmente recriá-los no mesmo servidor.
c) Transferir
databases e logins entre servidores.
d) Recriar
os jobs, devices de backup e linked servers ou transferí-los para outro
servidor.
e) Reassociar
logins a seus default databases.
f) Recriar
os usuários dos databases.
g) Reconfigurar
as configurações globais de um servidor.
ARQUIVOS DE SAÍDA
Cada uma
das sete procedures chamadas pela procedure principal usp_docservidor, irá
criar dois arquivo no caminho passado como referência. Os arquivos são criados
no formato *.err e *_
onde
o dia da semana (em inglês) em que o arquivo foi criado e
o nome do servidor. Isso permite ter armazenado os scripts criados durante toda
a semana. O arquivo de erro (*.err) é substituído a cada vez que as procedures
são executadas.
Exemplo:
Databases.err
Databases_Monday_WINXPPRO.sql
Databases_Tuesday_WINXPPRO.sql
Nota: Vale
lembrar que se a documentação for realizada mais de uma vez ao dia, os arquivos
serão substituídos refletindo sempre a última execução.
Os arquivos
gerados durante o processo de documentação do servidor são os seguintes:
--
Arquivos gerados pela procedure usp_doclogins
Logins.err
==> contém
erros encontrados durante a documentação dos Logins.
Logins_
==>
Logins.
--
Arquivos gerados pela procedure usp_docusers
Users.err ==> contém
erros encontrados durante a documentação dos usuários dos databases.
Users_
==>
grupos existentes em cada database.
--
Arquivos gerados pela procedure usp_docdbs
Databases.err
==> contém
erros encontrados durante a documentação dos databases.
Databases_
==>
databases.
Defaultdb.err
==> contém
erros encontrados durante a documentação dos Default Databases.
Defaultdb_
==>
seus default databases.
--
Arquivos gerados pela procedure usp_docdevices
DeviceBackup.err
==> contém erros encontrados durante a documentação dos
devices de backup (se existir).
DeviceBackup_
==>
devices de backup
--
Arquivos gerados pela procedure usp_docjobs
Jobs.err
==> contém
erros encontrados durante a documentação dos Jobs (se existir).
Jobs_
==>
Jobs.
--
Arquivos gerados pela procedure usp_doclinkedsrv
LinkedServer.err
==> contém
erros encontrados durante a documentação dos Linked Servers (se existir).
LinkedServer_
==>
Linked Servers.
--
Arquivos gerados pela procedure usp_docconfig
sp_configure.err
==> contém erros encontrados durante a documentação da
sp_configure.
sp_configure_
==>
configuração globais do servidor.
DOWNLOAD
O script
completo com a criação procedures tanto para o ambiente SQL Server 2000 quanto
para o SQL Server
2005 pode ser baixado clicando sobre os respectivos links.
Script
para SQL Server
2000: usp_docservidor.sql
Script para SQL Server 2005: usp_docservidor_2005.sql
Nota: O
script também criará um Job de nome Documenta_Servidor que
executará a procedure usp_docservidor diariamente as 17:00hs. Por default
o Job salva os scripts e um arquivo de log (Documenta_Servidor.log) no
caminho C:/temp/doc_servidor.
UM CENÁRIO
Como visto,
existem várias situações onde estes scripts poderão ser úteis no dia-a-dia de
um DBA, mas apenas como exemplo, vamos supor um cenário onde o DBA perdeu seu
servidor e precisará reinstalá-lo do zero. No entanto, este DBA tem como regra
deixar os arquivos das bases em um disco diferente e portanto, os arquivos de
suas bases estão íntegros no outro disco.
Suponhamos
que o DBA tenha agendado um job para executar o script de documentação todos os
dias as 17:00hs. Após a reinstalação do servidor, os scripts podem ser
executados na seguinte ordem.
1. Script para
recriar os logins.
2. Script
para attachar os databases.
3. Script
para associar o default database dos logins.
4. Script
para recriar os devices de backup, os jobs e os linked server.
5. Script
para reconfigurar as configurações globais do servidor sp_configure).
Nota: Como
os logins tiveram que ser recriados, pode acontecer de os usuários dentro das
bases ficarem em um estado que chamamos de órfãos. Para reassociar os usuários
a seus respectivos logins, execute em cada base de dados o script para Associar Usuários Orfãos.
Bom
pessoal, como puderam notar os scripts gerados pela documentação possui 1001
utilidades, mas pode ser muito útil principalmente no momento de reinstalação
ou migração de um servidor.
A documentação
trata do servidor como um todo mas fiquem a vontade para adaptá-lo às suas
reais necessidades e não esqueçam de testá-lo em um ambiente de teste antes de
colocá-lo em produção.
Download:
(Versão anterior a 2005): https://www.w1net.com.br/blogdba/usp_docservidor.zip
(Versão posterior a 2005): https://www.w1net.com.br/blogdba/usp_docservidor_2005.zip
Autor: Nilton Pinheiro