none
Criar uma rotina de backup - SQL Server RRS feed

  • 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.

    terça-feira, 18 de junho de 2019 19:19

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
    segunda-feira, 24 de junho de 2019 14:15
  • 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

    terça-feira, 18 de junho de 2019 19:23
  • 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
    quarta-feira, 19 de junho de 2019 11:53
  • Estranho ...

    Tente assim:

    SET @path = 'E:\BKP_DB\'

    • Marcado como Resposta Windows Man segunda-feira, 24 de junho de 2019 14:01
    segunda-feira, 24 de junho de 2019 13:52
  • 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
    quarta-feira, 19 de junho de 2019 11:49
    Moderador
  • 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
    quarta-feira, 19 de junho de 2019 16:42
    Moderador
  • 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
    quarta-feira, 19 de junho de 2019 19:00
    Moderador

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

    terça-feira, 18 de junho de 2019 19:23
  • Olá Edvaldo

    Me diz uma coisa: eu posso rodar esse script direto? Ou eu tenho que fazer algum comando, alguma coisa no banco de dados? Valeu pela ajuda!

    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
    quarta-feira, 19 de junho de 2019 11:49
    Moderador
  • 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
    quarta-feira, 19 de junho de 2019 11:53
  • Junior, Edvaldo,

    Obrigado pelas respostas! Edvaldo, por favor, uma dúvida: no script que você deixou, eu preciso salvar esse script como .sql ou .bat?

    Desculpem o monte de perguntas...é que eu sou totalmente leigo em SQL...

    quarta-feira, 19 de junho de 2019 13:33
  • 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]

    quarta-feira, 19 de junho de 2019 13:35
    Moderador
  • Junior,

    No caso, o script enviado pelo Edvaldo só contém um bloco de instruções, ele não está dividido em parte sql e em bat...por isso que eu perguntei como devo salvar, se em .sql ou .bat, entende?

    Obrigado

    quarta-feira, 19 de junho de 2019 14:55
  • 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
    quarta-feira, 19 de junho de 2019 16:42
    Moderador
  • Junior,

    Entendi. Então, o script tem que ser salvo em *.sql...beleza, muito obrigado pela ajuda!

    Abraços

    quarta-feira, 19 de junho de 2019 18:58
  • 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
    quarta-feira, 19 de junho de 2019 19:00
    Moderador
  • 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á!

    sexta-feira, 21 de junho de 2019 19:31
  • 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]

    sexta-feira, 21 de junho de 2019 22:35
    Moderador
  • 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

    segunda-feira, 24 de junho de 2019 12:54
  • 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:\'

    segunda-feira, 24 de junho de 2019 12:59
  • 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_cursor

    Só 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

    segunda-feira, 24 de junho de 2019 13:10
  • Existe  está pasta BKP_DB  ?
    segunda-feira, 24 de junho de 2019 13:14
  • Edvaldo, sim, existe sim. Inclusive eu coloquei a permissão de Controle Total para Todos na pasta.
    segunda-feira, 24 de junho de 2019 13:48
  • Estranho ...

    Tente assim:

    SET @path = 'E:\BKP_DB\'

    • Marcado como Resposta Windows Man segunda-feira, 24 de junho de 2019 14:01
    segunda-feira, 24 de junho de 2019 13:52
  • Edvaldo, agora foi! Faltou mesmo essa barra final. Ficou show! Só para concluir, é possível salvar o backup dos bancos constando a data em que ele for feito, no nome do arquivo que é gerado (.bak)?

    Muito obrigado!

    segunda-feira, 24 de junho de 2019 14:06
  • 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
    segunda-feira, 24 de junho de 2019 14:15
  • Edvaldo,

    Agora sim, o script ficou PERFEITO! MUITO OBRIGADO!! FANTÁSTICO!! SEM PALAVRAS!!

    Tenha um excelente dia! Deus te abençoe!!!

    segunda-feira, 24 de junho de 2019 14:29
  • Disponha, abraço !
    segunda-feira, 24 de junho de 2019 14:32
  • 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]

    segunda-feira, 24 de junho de 2019 16:33
    Moderador