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!
SQL From Hell.com 
eu tentei usar o tutorial, mas não consigo ligar o SQL Server Agent
Boa tarde Rodrigo, já tentou ligar o SQL Server Agent pelo services.msc ou pelo SQL Server Configuration Manager? Isso gera alguma mensagem de erro?
Boa tarde Paulo;
Pelo services.msc da o erro abaixo:
O serviço SQL Server (MSSQLSERVER) em computador local foi iniciado e interrompido. Alguns serviços são interrompidos automaticamente se não estiverem sendo usados por outros serviços ou programas.
e pelo SQL Server Configuration Manager da esse erro:
Date,Source,Severity,Message
10/21/2010 15:23:18,,Information,[098] SQLServerAgent terminated (normally)
10/21/2010 15:23:18,,Error,[000] Error creating a new session
10/21/2010 15:23:18,,Error,[298] SQLServer Error: 229 The INSERT permission was denied on the object ‘syssessions’ database ‘msdb’ schema ‘dbo’. [SQLSTATE 42000]
10/21/2010 15:23:18,,Error,[298] SQLServer Error: 229 The UPDATE permission was denied on the object ‘sysalerts’ database ‘msdb’ schema ‘dbo’. [SQLSTATE 42000] (DumpAndCheckServerVersion)
10/21/2010 15:23:18,,Error,[298] SQLServer Error: 229 The EXECUTE permission was denied on the object ‘sp_sqlagent_get_startup_info’ database ‘msdb’ schema ‘dbo’. [SQLSTATE 42000]
10/21/2010 15:23:18,,Error,[298] SQLServer Error: 229 The EXECUTE permission was denied on the object ‘sp_sqlagent_has_server_access’ database ‘msdb’ schema ‘dbo’. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
10/21/2010 15:23:18,,Error,[298] SQLServer Error: 15247 User does not have permission to perform this action. [SQLSTATE 42000] (DisableAgentXPs)
Você pode alterar o usuário de execução do SQL Server Agent para o mesmo que é executado o SQL Server ou criar este usuário como ‘sysadmin’ no SQL Server.
Desculpa talvez a pergunta tosca, mas como posso fazer isso?
Rodrigo, você pode seguir este artigo (at. http://codespin.blogspot.com/2008/01/fixing-startup-failure-of-sql-server.html ), qualquer problema me avise!
Tentei, mas obtive o mesmo erro:
Date,Source,Severity,Message
10/22/2010 08:52:50,,Information,[098] SQLServerAgent terminated (normally)
10/22/2010 08:52:43,,Error,[000] Error creating a new session
10/22/2010 08:52:43,,Error,[298] SQLServer Error: 229 The INSERT permission was denied on the object ‘syssessions’ database ‘msdb’ schema ‘dbo’. [SQLSTATE 42000]
10/22/2010 08:52:43,,Error,[298] SQLServer Error: 229 The UPDATE permission was denied on the object ‘sysalerts’ database ‘msdb’ schema ‘dbo’. [SQLSTATE 42000] (DumpAndCheckServerVersion)
10/22/2010 08:52:43,,Error,[298] SQLServer Error: 229 The EXECUTE permission was denied on the object ‘sp_sqlagent_get_startup_info’ database ‘msdb’ schema ‘dbo’. [SQLSTATE 42000]
10/22/2010 08:52:43,,Error,[298] SQLServer Error: 229 The EXECUTE permission was denied on the object ‘sp_sqlagent_has_server_access’ database ‘msdb’ schema ‘dbo’. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
10/22/2010 08:52:43,,Error,[298] SQLServer Error: 15247 User does not have permission to perform this action. [SQLSTATE 42000] (DisableAgentXPs)
–Solução 01:
Altere o usuário do SQL Server Agent para NT AUTHORITY\NETWORK SERVICE (sem senha) e crie este usuário no banco como sysadmin.
Comando SQL para você criar este usuário como sysadmin:
CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS
WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember
@loginame = N’NT AUTHORITY\NETWORK SERVICE’
, @rolename = N’sysadmin’
GO
— Solução 02:
CREATE LOGIN [NT SERVICE\SQLSERVERAGENT] FROM WINDOWS
WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember
@loginame = N’NT SERVICE\SQLSERVERAGENT’
, @rolename = N’sysadmin’
GO
Bom dia Paulo..
Como Faço Pra Ligar o SQL SERVER AGENT no Windows 7
Aparece o 1º ERRO de Acesso Negado
Depois Aparece o 2º Erro
An error 5 – (Acesso negado.
) occurred while performing this service operation on the
SQLServerAgent service.
Pode Me Informar Pq Ocorre esse Erro
e Como Corrigi-lo
Desde Então Obrigado.
Veja qual erro aparece sobre este serviço no log do Windows e me envie, mas penso que as soluções dos comentários acima já podem te ajudar solucionar este problema.
Bom dia desculpe a falta de conhecimente sou recente usuario do SQL Server e gostaria de saber se na rotina de backup o SQL Server não teria criar as pastas e ir salvando os arquivos dentro das mesmas por dia como é feito pelo linux
Boa tarde Elvis! É possível fazer isso por meio da opção “Create a sub-directory for each database” do passo “Define Back Up (Full) Database Plan Task”.
Pingback: Query 4 Fun – Lançamento do livro « SQL From Hell.com
Valeu muito bem explicado…. me ajudou muitooo
boa noite!! gostaria de saber se existe uma forma de eu automatizar um backup full de uma base e direcionar esse backup full para outro servidor?
Bom dia Daniele,
Tem algumas alternativas para manter uma base em outro servidor sincronizada (ex. Replication e Log Shipping), mas você pode criar um script em PowerShell e colocar ele como um job no SQL Agent.
Log Shipping:
http://msdn.microsoft.com/pt-br/library/ms187103.aspx
Replication:
http://msdn.microsoft.com/pt-br/library/ms151198.aspx
PowerShell:
Gerando backups do SQL Server por PowerShell
Boa noite, parabéns pelo post, mas ainda tenho algumas duvidas, queria salvar o arquivo com o nome do dia da semana e após o sétimo dia ele fosse sobrescrevendo os arquivos, é possivel? Obrigado
Boa noite Renato, por padrão não dá para fazer isso por esta ferramenta.
Para customizar seu plano de manutenção, você pode optar por criar um script em PowerShell e adicionar este script no plano de manutenção.
Para entender como fazer isso, dê uma olhada nos seguintes artigos:
Gerando backups do SQL Server por PowerShell
PowerShell no SQL Server – Step 3: Jobs
Se tiver dúvida, me avise.
Boa tarde Paulo, muito pela resposta rápida, até tentei dar uma olhada mas como SQL não é a minha praia acabei fazendo do modo mais simples e criei uma bat para excluir o arquivo anterior. Consegui validar o backup em um outro servidor e restaurou sem nenhum problema. Mas mesmo assim muito obrigado pela sua ajuda e parabéns pelo blog.
Pingback: Entendendo e Melhorando seus backups (SQL Server) | Alex Souza
Bom dia Paulo R. Pereira!
Aqui na empresa utilizamos o SQL 2008 – versão 10.50.1617.0.
Já existe rotina automática para realizar BKP, porém o SKL grava na pasta de BKP, o nome que foi gravado nos parâmetros, ou seja, o BKP de hoje, irá se acumular com o de amanhã.
Tem como criar uma rotina que, quando o SKL fizer o BKP grave o nome do arquivo pela data do dia realizado?
O caminho da configuração diária esta em: SQL Server Agent / trabalhos / (nome do trabalho no caso) / propriedades / agendas. (eu ia colocar o print aqui, mas não sei como fazer).
Grato,
Elder Carlos
O plano de manutenção já gera o nome com a informação da data do arquivo por padrão.
Você pode tentar fazer isso manualmente por T-SQL:
http://technet.microsoft.com/en-us/library/ms186865.aspx
Ou por PowerShell:
Ou por C#:
ola Paulo , gostei do seu post , voce esta de parabens , gostaria de saber se têm como eu copiar o meu backup para outra maquina via jobs de forma automatica
Você pode mapear uma unidade de rede para isso, e colocar no plano de manutenção para o SQL Server salvar lá.
Ótimo passo a passo.
Ajudou muito.