Usuário com melhor resposta
Criar uma rotina de backup - SQL Server

Pergunta
-
Pessoal boa tarde,
Estou com uma demanda, em que preciso criar uma rotina de backup em 2 bancos de dados que estão no SQL Express, como mostra a imagem abaixo (eu troquei o nome dos bancos):
Peço desculpas pela pergunta, é que eu nunca mexi com banco de dados...
Desde já agradeço.
Respostas
-
Blz !
Tente assim:
DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name -- specify database backup directory SET @path = 'D:\BKP_DB\' -- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR READ_ONLY FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases AND state = 0 -- database is online AND is_in_standby = 0 -- database is not read only for log shipping OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
- Marcado como Resposta Windows Man segunda-feira, 24 de junho de 2019 14:29
-
Prezado,
Em um ambiente de produção , recomendaria fazer backup das bases 'master, msdb e model'. São bases de sistema , porém, em caso de problemas não ter estas bases o BD (instancia) não 'sobe'.
Neste caso, até para que você possa ir aprendendo mais , faça alguns testes fazendo alterações na parte do script onde tem os nomes das bases.
Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]
- Marcado como Resposta Junior Galvão - MVPMVP, Moderator sábado, 10 de agosto de 2019 11:48
-
Boa tarde,
Veja este script, ele fará o backup de todos os bancos na sua instância (excluindo model, msdb e tempdb)
DECLARE @name VARCHAR(150) -- DECLARE @path VARCHAR(256) -- DECLARE @fileName VARCHAR(256) -- -- Define caminho de destino do backup SET @path = 'd:\' -- Cria um cursor para selecionar todas as databases, -- excluindo model, msdb e tempdb DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('tempdb','master','model','msdb','ReportServer','ReportServerTempDB','ASPNETDB') -- Abre o cursor e faz a primeira leitura OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name -- Loop de leitura das databases selecionadas WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '.BAK' -- Executa o backup para o database BACKUP DATABASE @name TO DISK = @fileName ; FETCH NEXT FROM db_cursor INTO @name END -- Libera recursos alocados pelo cursor CLOSE db_cursor DEALLOCATE db_cursor
- Sugerido como Resposta IgorFKModerator terça-feira, 18 de junho de 2019 20:44
- Marcado como Resposta Junior Galvão - MVPMVP, Moderator quarta-feira, 19 de junho de 2019 10:44
- Não Marcado como Resposta Windows Man quarta-feira, 19 de junho de 2019 11:38
- Marcado como Resposta Windows Man quarta-feira, 19 de junho de 2019 11:38
-
Você precisar alterar no script local onde será o caminho de destino do backup, se vc executar da forma que está, o backup será feito na unidade D: do computador.
- Marcado como Resposta Windows Man quarta-feira, 19 de junho de 2019 12:39
-
Estranho ...
Tente assim:
SET @path = 'E:\BKP_DB\'
- Marcado como Resposta Windows Man segunda-feira, 24 de junho de 2019 14:01
-
Este link , mostra um passo a passo de como configurar a task do windows e a '.bat' pra chamar o script de backup.
A intenção é mostrar como fazer, o backup que agendado. Deve ajudar a entender ainda mais o conceito.
https://ittutorial.org/sql-server-express-backups-how-to-schedule-and-automate/
Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]
- Marcado como Resposta Junior Galvão - MVPMVP, Moderator terça-feira, 13 de agosto de 2019 10:59
-
Windows Man,
Pode rodar diretamente em uma query dentro do Management Studio, ou se quiser, poderá também configurar uma tarefa agendada em seu Windows e transformar esta código em uma rotina programada.
Caso deseja fazer uso deste recurso como uma rotina programada, será necessário basicamente salvá-lo como um script, ou seja, um arquivo com extensão .sql e através da ferramenta de linha de comando existente no Microsoft SQL Server denominado SQLCMD fazer a execução deste script.
Você pode também criar um arquivo de lote, um arquivo com extensão .bat, e dentro do código deste arquivo fazer a chamada da execução do SQLCMD processando este script.
Veja este exemplo:
http://www.litri.com.br/boaviagem/executar-queries-no-sql-server-via-windows-sqlcmd/
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
- Marcado como Resposta Windows Man quarta-feira, 19 de junho de 2019 12:39
-
Windows,
Sim eu entendi, mas não tem nada dividido em partes .sql ou .bat, todo script foi elaborado para ser processado diretamente no SQL Server.
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
- Marcado como Resposta Windows Man quarta-feira, 19 de junho de 2019 18:57
-
Junior,
Entendi. Então, o script tem que ser salvo em *.sql...beleza, muito obrigado pela ajuda!
Abraços
Windows,
Isso mesmo, disponha, abraços.
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
- Marcado como Resposta Windows Man sexta-feira, 21 de junho de 2019 14:42
Todas as Respostas
-
Boa tarde,
Veja este script, ele fará o backup de todos os bancos na sua instância (excluindo model, msdb e tempdb)
DECLARE @name VARCHAR(150) -- DECLARE @path VARCHAR(256) -- DECLARE @fileName VARCHAR(256) -- -- Define caminho de destino do backup SET @path = 'd:\' -- Cria um cursor para selecionar todas as databases, -- excluindo model, msdb e tempdb DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('tempdb','master','model','msdb','ReportServer','ReportServerTempDB','ASPNETDB') -- Abre o cursor e faz a primeira leitura OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name -- Loop de leitura das databases selecionadas WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '.BAK' -- Executa o backup para o database BACKUP DATABASE @name TO DISK = @fileName ; FETCH NEXT FROM db_cursor INTO @name END -- Libera recursos alocados pelo cursor CLOSE db_cursor DEALLOCATE db_cursor
- Sugerido como Resposta IgorFKModerator terça-feira, 18 de junho de 2019 20:44
- Marcado como Resposta Junior Galvão - MVPMVP, Moderator quarta-feira, 19 de junho de 2019 10:44
- Não Marcado como Resposta Windows Man quarta-feira, 19 de junho de 2019 11:38
- Marcado como Resposta Windows Man quarta-feira, 19 de junho de 2019 11:38
-
-
Windows Man,
Pode rodar diretamente em uma query dentro do Management Studio, ou se quiser, poderá também configurar uma tarefa agendada em seu Windows e transformar esta código em uma rotina programada.
Caso deseja fazer uso deste recurso como uma rotina programada, será necessário basicamente salvá-lo como um script, ou seja, um arquivo com extensão .sql e através da ferramenta de linha de comando existente no Microsoft SQL Server denominado SQLCMD fazer a execução deste script.
Você pode também criar um arquivo de lote, um arquivo com extensão .bat, e dentro do código deste arquivo fazer a chamada da execução do SQLCMD processando este script.
Veja este exemplo:
http://www.litri.com.br/boaviagem/executar-queries-no-sql-server-via-windows-sqlcmd/
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
- Marcado como Resposta Windows Man quarta-feira, 19 de junho de 2019 12:39
-
Você precisar alterar no script local onde será o caminho de destino do backup, se vc executar da forma que está, o backup será feito na unidade D: do computador.
- Marcado como Resposta Windows Man quarta-feira, 19 de junho de 2019 12:39
-
-
Windows,
O Script elaborado no SQL Server você vai salvar com a extensão .sql, já o arquivo que irá ser utilizado para executar este script, você vai salvar com a extensão .bat.
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
-
-
Windows,
Sim eu entendi, mas não tem nada dividido em partes .sql ou .bat, todo script foi elaborado para ser processado diretamente no SQL Server.
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
- Marcado como Resposta Windows Man quarta-feira, 19 de junho de 2019 18:57
-
-
Junior,
Entendi. Então, o script tem que ser salvo em *.sql...beleza, muito obrigado pela ajuda!
Abraços
Windows,
Isso mesmo, disponha, abraços.
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
- Marcado como Resposta Windows Man sexta-feira, 21 de junho de 2019 14:42
-
Pessoal boa tarde,
Me desculpem voltar aqui no tópico, mas eu rodei o script que o Edvaldo deixou, e, o script faz o backup tudo certinho, porém, não está salvando os arquivos no caminho selecionado (D:). Abaixo mostra o resultado do script.bat que chama o scrtipt BKP_DB.sql:
C:\Temp>sqlcmd -i "C:\Temp\BKP_DB.sql"
Processed 155928 pages for database 'Banco 1', file 'Banco 1_data' on file 2.
Processed 5 pages for database 'Banco 1', file 'Banco 1_log' on file 2.
BACKUP DATABASE successfully processed 155933 pages in 14.737 seconds (82.664 MB
/sec).
Processed 260472 pages for database 'Banco 2', file 'Banco 2_data' on file 2.
Processed 2 pages for database 'Banco 2', file 'Banco 2_log' on file 2.
BACKUP DATABASE successfully processed 260474 pages in 21.896 seconds (92.936 MB
/sec).Eu preciso que esse script crie os backups. Se possível, já salvando com a data do dia. Mas, se não for possível, não tem problema, eu crio um outro esquema para salvar os backups por data. Mas, eu preciso que o backup seja salvo, para eu poder arquivá-lo.
Obrigado desde já!
-
Windows,
Então, se você verificou no exemplo que o Edvaldo postou existe uma variável chamada @filename, é justamente esta variável que você precisa definir o local de armazenamento do arquivo acompanhado do nome do arquivo.
Em relação a data do dia ou dia da semana, veja se este exemplo te ajuda:
Set DateFirst 1 Declare @DiadaSemana TinyInt, @Hora Char(5) Select @DiadaSemana = DATEPART (WeekDay, Getdate()), @Hora=Convert(Char(5),GetDate(),114) If (@DiadaSemana = 1) Begin If (@Hora='12:00') Backup Database SeuBancoDeDados To Disk = 'F:\Backup-SQL\Segunda\SeuBancoDeDados-Segunda-Manha.bak' With Init, Format; If (@Hora='22:00') Backup Database SeuBancoDeDados To Disk = 'F:\Backup-SQL\Segunda\SeuBancoDeDados-Segunda-Noite.bak' With Init, Format; End If (@DiadaSemana = 2) Begin If (@Hora='12:00') Backup Database SeuBancoDeDados To Disk = 'F:\Backup-SQL\Terça\SeuBancoDeDados-Terça-Manha.bak' With Init, Format; If (@Hora='22:00') Backup Database SeuBancoDeDados To Disk = 'F:\Backup-SQL\Terça\SeuBancoDeDados-Terça-Noite.bak' With Init, Format; End If (@DiadaSemana = 3) Begin If (@Hora='12:00') Backup Database SeuBancoDeDados To Disk = 'F:\Backup-SQL\Quarta\SeuBancoDeDados-Quarta-Manha.bak' With Init, Format; If (@Hora='22:00') Backup Database SeuBancoDeDados To Disk = 'F:\Backup-SQL\Quarta\SeuBancoDeDados-Quarta-Noite.bak' With Init, Format; End If (@DiadaSemana = 4) Begin If (@Hora='12:00') Backup Database SeuBancoDeDados To Disk = 'F:\Backup-SQL\Quinta\SeuBancoDeDados-Quinta-Manha.bak' With Init, Format; If (@Hora='22:00') Backup Database SeuBancoDeDados To Disk = 'F:\Backup-SQL\Quinta\SeuBancoDeDados-Quinta-Noite.bak' With Init, Format; End If (@DiadaSemana = 5) Begin If (@Hora='12:00') Backup Database SeuBancoDeDados To Disk = 'F:\Backup-SQL\Sexta\SeuBancoDeDados-Sexta-Manha.bak' With Init, Format; If (@Hora='22:00') Backup Database SeuBancoDeDados To Disk = 'F:\Backup-SQL\Sexta\SeuBancoDeDados-Sexta-Noite.bak' With Init, Format; End If (@DiadaSemana = 6) Begin If (@Hora='12:00') Backup Database SeuBancoDeDados To Disk = 'F:\Backup-SQL\Sábado\SeuBancoDeDados-Sábado-Manha.bak' With Init, Format; If (@Hora='22:00') Backup Database SeuBancoDeDados To Disk = 'F:\Backup-SQL\Sábado\SeuBancoDeDados-Sábado-Noite.bak' With Init, Format; End If (@DiadaSemana = 7) Begin If (@Hora='12:00') Backup Database SeuBancoDeDados To Disk = 'F:\Backup-SQL\Domingo\SeuBancoDeDados-Domingo-Manha.bak' With Init, Format; If (@Hora='22:00') Backup Database SeuBancoDeDados To Disk = 'F:\Backup-SQL\Domingo\SeuBancoDeDados-Domingo-Noite.bak' With Init, Format; End
Neste exemplo, você vai adicionar a data ao nome do arquivo:
Declare @Comando Varchar(200) Set @Comando = 'BACKUP DATABASE SEAS'+Char(10)+ 'TO DISK = '+'''D:\MSSQL2016-Backup\BACKUP-SEAS-'+''+Convert(VarChar(10),GetDate(),112)+'.bak'''+Char(10)+ 'WITH INIT, DESCRIPTION = '+'''Backup Full SEAS''' Exec(@comando)
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
-
Junior bom dia
Então, eu vi hoje que o script que o Edvaldo postou aqui fez o backup. Porém, fez em outro caminho (na verdade, eu coloquei a pasta D:\Backup_Banco, e, ao executar o comando, o backup está sendo salvo apenas na raiz do D)...
Enfim, o script do Edvaldo resolve meu caso, porém, eu gostaria de incrementá-lo, fazendo com que ele salve no caminho que eu defini, e não no D:, e também que salve com a data do dia que o backup for feito. No script que você passou, ele faz backp duas vezes no dia correto? Eu preciso que ele faça backup apenas 1 vez ao dia, na parte da noite.
Obrigado pela valiosa ajuda
-
Junior bom dia
Então, eu vi hoje que o script que o Edvaldo postou aqui fez o backup. Porém, fez em outro caminho (na verdade, eu coloquei a pasta D:\Backup_Banco, e, ao executar o comando, o backup está sendo salvo apenas na raiz do D)...
Enfim, o script do Edvaldo resolve meu caso, porém, eu gostaria de incrementá-lo, fazendo com que ele salve no caminho que eu defini, e não no D:, e também que salve com a data do dia que o backup for feito. No script que você passou, ele faz backp duas vezes no dia correto? Eu preciso que ele faça backup apenas 1 vez ao dia, na parte da noite.
Obrigado pela valiosa ajuda
No script há um cometário, onde é definido o local do backup, localize o script o comentário (
Define caminho de destino do backup
) e altere para o local onde desejar.
-- Define caminho de destino do backup SET @path = 'd:\'
-
Edvaldo, o script está assim:
DECLARE @name VARCHAR(150) --
DECLARE @path VARCHAR(256) --
DECLARE @fileName VARCHAR(256) --
-- Define caminho de destino do backup
SET @path = 'E:\BKP_DB'
-- Cria um cursor para selecionar todas as databases,
-- excluindo model, msdb e tempdb
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb','master','model','msdb','ReportServer','ReportServerTempDB','ASPNETDB')
-- Abre o cursor e faz a primeira leitura
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
-- Loop de leitura das databases selecionadas
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '.BAK'
-- Executa o backup para o database
BACKUP DATABASE @name TO DISK = @fileName ;
FETCH NEXT FROM db_cursor INTO @name
END
-- Libera recursos alocados pelo cursor
CLOSE db_cursor
DEALLOCATE db_cursorSó que ele está salvando na raiz (E:\), e não no caminho (BKP_DB). Além disso, não é possível que esse script salve o arquivo com a data do dia (exemplo: Banco1_24-06-2019.bak)?
Obrigado
-
-
-
Estranho ...
Tente assim:
SET @path = 'E:\BKP_DB\'
- Marcado como Resposta Windows Man segunda-feira, 24 de junho de 2019 14:01
-
-
Blz !
Tente assim:
DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name -- specify database backup directory SET @path = 'D:\BKP_DB\' -- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR READ_ONLY FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases AND state = 0 -- database is online AND is_in_standby = 0 -- database is not read only for log shipping OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
- Marcado como Resposta Windows Man segunda-feira, 24 de junho de 2019 14:29
-
-
-
Windows,
Sim, sim, ele faz duas vezes, mas uma pequena alteração já ficaria da forma que você precisava.
Abraços.
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
-
Prezado,
Em um ambiente de produção , recomendaria fazer backup das bases 'master, msdb e model'. São bases de sistema , porém, em caso de problemas não ter estas bases o BD (instancia) não 'sobe'.
Neste caso, até para que você possa ir aprendendo mais , faça alguns testes fazendo alterações na parte do script onde tem os nomes das bases.
Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]
- Marcado como Resposta Junior Galvão - MVPMVP, Moderator sábado, 10 de agosto de 2019 11:48
-
Este link , mostra um passo a passo de como configurar a task do windows e a '.bat' pra chamar o script de backup.
A intenção é mostrar como fazer, o backup que agendado. Deve ajudar a entender ainda mais o conceito.
https://ittutorial.org/sql-server-express-backups-how-to-schedule-and-automate/
Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]
- Marcado como Resposta Junior Galvão - MVPMVP, Moderator terça-feira, 13 de agosto de 2019 10:59