none
Estratégia de Backup e Recovery para bases com tabelas particionadas RRS feed

  • Pergunta

  • Bom dia

    Estou em um projeto para remodelar uma base de dados de um cliente e devido a uma nova necessidade de negócio, será necessário particionar algumas tabelas desse banco. Gostaria de sugestões para a estratégia de Backup e Recovery levando em consideração que o banco terá vários FileGroups para suportar o partiocionamento e existir a necessidade de restaurar apenas um FG por exemplo. Hoje, o banco trabalha com o tipo de Recovery FULL e existem rotunas dos backups FULL, DIF e Transacional.

    Agradeço desde já a atenção

    quarta-feira, 30 de janeiro de 2013 11:20

Respostas

  • José,

    Sim, isso é possível sim, mas tem alguns cuidados que devem ser tomados, veja os exemplos:

    CREATE DATABASE AES
    ON PRIMARY
    	(NAME = AES_Dados,
    	  FILENAME = N'C:\SQL\AES_Dados.mdf',
              SIZE = 10MB,
              MAXSIZE = 50MB,
              FILEGROWTH = 10%),	
    FILEGROUP Secundario
    	( NAME = AES_Secundario_Dados,
    	  FILENAME = N'C:\SQL\AES_Secundario_Dados.ndf',
              SIZE = 10MB,
              MAXSIZE = 50MB,
              FILEGROWTH = 10%)
    LOG ON
    	( NAME = AES_Log,
    	  FILENAME = N'C:\SQL\AES_Log.ldf',
              SIZE = 10MB,
              MAXSIZE = 50MB,
              FILEGROWTH = 10%)
    GO
    
    Use AES
    Go
    
    Create Table T1
     (Codigo Int) On [Primary]
     
    Create Table T2
     (Codigo Int) On [Secundario]
     
    --Backup Database
    Backup Database AES
     To Disk = 'C:\SQL\Backup-Full-AES.bak'
     With Init, NoFormat
     
    --Backup Database + File x Filegroup
    Backup Database AES
     File = 'AES_Dados',
     Filegroup = 'Primary'
     To Disk = 'C:\SQL\Backup-Primary-AES.bak'
     With Init, NoFormat
    
    Backup Database AES
     File = 'AES_Secundario_Dados',
     Filegroup = 'Secundario'
     To Disk = 'C:\SQL\Backup-Secundario-AES.bak'
     With Init, NoFormat
     
    --Backup Log
    Use master
    Go
    
    Backup Log AES
     To Disk = 'C:\SQL\Backup-Log-AES.bak'
     With Init, NoRecovery
    Go
    
    --Restaurando o Filegroup(Primary)
    Use master
    Go
    
    Restore Database AES 
     Filegroup = 'Primary' 
     From Disk = 'C:\SQL\Backup-Primary-AES.bak'
     With Partial, Recovery, Replace
    Go
    
    --Restaurando o Filegroup(Secundario)
    Use master
    Go
    
    Restore Database AES 
     Filegroup = 'Secundario' 
     From Disk = 'C:\SQL\Backup-Secundario-AES.bak'
     With NoRecovery
    Go
    
    --Restaurando do Log e liberando o Banco de Dados
    Use master
    Go
    
    Restore Log AES 
     From Disk = 'C:\SQL\Backup-Log-AES.bak'
     With Recovery
    Go
    
    --Restaurando o Backup Full e forçando a leitura dos filegroups
    Use master
    Go
    
    Restore Database AES READ_WRITE_FILEGROUPS
     From Disk = 'C:\SQL\Backup-Full-AES.bak'
     With File=1, Replace, NoRecovery
    Go 
    
    --Restaurando o Filegroup em Estado Offline 
    Restore Database AES 
     Filegroup='Secundario' 
     From Disk ='C:\SQL\Backup-Secundario-AES.bak'
     With NoRecovery
    Go
     
    --Restaurando do Log e liberando o Banco de Dados
    Use master
    Go
    
    Restore Log AES
     From Disk = 'C:\SQL\Backup-Log-AES.bak'
     With Recovery
    Go
    
    
    Select * from AES.Sys.Database_Files
    


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    • Marcado como Resposta Jose Marcelo sexta-feira, 8 de fevereiro de 2013 13:23
    quarta-feira, 30 de janeiro de 2013 17:29
    Moderador

Todas as Respostas

  • José Marcelo,

    Acredito que você possa manter a mesma estratégia de backup que esta sendo utilizada atualmente, como você mesmo destacou esta trabalhando com Backup Full, Diff e Log de Transações.

    Acredito que você poderia montar uma agenda de backup mais dinâmica com horários alternativos de backup e também armazenamento dos arquivos de backup em locais distintos.

    Agora, se você já realiza um backup Diferencial, será que é necessário realizar um Backup de Log de Transações?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quarta-feira, 30 de janeiro de 2013 16:07
    Moderador
  • Boa tarde Junior

    Sobre a sua pergunta, o backup Diferencial é realizado para agilizar um possível restore e também como uma garantia a mais para o processo de restore.

    Minha dúvida se é possível restaurar apenas um filegroup e como proceder.

    Grato

    quarta-feira, 30 de janeiro de 2013 16:54
  • José,

    Sim, isso é possível sim, mas tem alguns cuidados que devem ser tomados, veja os exemplos:

    CREATE DATABASE AES
    ON PRIMARY
    	(NAME = AES_Dados,
    	  FILENAME = N'C:\SQL\AES_Dados.mdf',
              SIZE = 10MB,
              MAXSIZE = 50MB,
              FILEGROWTH = 10%),	
    FILEGROUP Secundario
    	( NAME = AES_Secundario_Dados,
    	  FILENAME = N'C:\SQL\AES_Secundario_Dados.ndf',
              SIZE = 10MB,
              MAXSIZE = 50MB,
              FILEGROWTH = 10%)
    LOG ON
    	( NAME = AES_Log,
    	  FILENAME = N'C:\SQL\AES_Log.ldf',
              SIZE = 10MB,
              MAXSIZE = 50MB,
              FILEGROWTH = 10%)
    GO
    
    Use AES
    Go
    
    Create Table T1
     (Codigo Int) On [Primary]
     
    Create Table T2
     (Codigo Int) On [Secundario]
     
    --Backup Database
    Backup Database AES
     To Disk = 'C:\SQL\Backup-Full-AES.bak'
     With Init, NoFormat
     
    --Backup Database + File x Filegroup
    Backup Database AES
     File = 'AES_Dados',
     Filegroup = 'Primary'
     To Disk = 'C:\SQL\Backup-Primary-AES.bak'
     With Init, NoFormat
    
    Backup Database AES
     File = 'AES_Secundario_Dados',
     Filegroup = 'Secundario'
     To Disk = 'C:\SQL\Backup-Secundario-AES.bak'
     With Init, NoFormat
     
    --Backup Log
    Use master
    Go
    
    Backup Log AES
     To Disk = 'C:\SQL\Backup-Log-AES.bak'
     With Init, NoRecovery
    Go
    
    --Restaurando o Filegroup(Primary)
    Use master
    Go
    
    Restore Database AES 
     Filegroup = 'Primary' 
     From Disk = 'C:\SQL\Backup-Primary-AES.bak'
     With Partial, Recovery, Replace
    Go
    
    --Restaurando o Filegroup(Secundario)
    Use master
    Go
    
    Restore Database AES 
     Filegroup = 'Secundario' 
     From Disk = 'C:\SQL\Backup-Secundario-AES.bak'
     With NoRecovery
    Go
    
    --Restaurando do Log e liberando o Banco de Dados
    Use master
    Go
    
    Restore Log AES 
     From Disk = 'C:\SQL\Backup-Log-AES.bak'
     With Recovery
    Go
    
    --Restaurando o Backup Full e forçando a leitura dos filegroups
    Use master
    Go
    
    Restore Database AES READ_WRITE_FILEGROUPS
     From Disk = 'C:\SQL\Backup-Full-AES.bak'
     With File=1, Replace, NoRecovery
    Go 
    
    --Restaurando o Filegroup em Estado Offline 
    Restore Database AES 
     Filegroup='Secundario' 
     From Disk ='C:\SQL\Backup-Secundario-AES.bak'
     With NoRecovery
    Go
     
    --Restaurando do Log e liberando o Banco de Dados
    Use master
    Go
    
    Restore Log AES
     From Disk = 'C:\SQL\Backup-Log-AES.bak'
     With Recovery
    Go
    
    
    Select * from AES.Sys.Database_Files
    


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    • Marcado como Resposta Jose Marcelo sexta-feira, 8 de fevereiro de 2013 13:23
    quarta-feira, 30 de janeiro de 2013 17:29
    Moderador
  • Bom dia Junior

    Obrigado mais uma vez pela ajuda com o exemplo de backup e restore.

    Como a tendencia desse banco é ter um volume muito grande de informações pois serão vários anos disponíveis ON-LINE e portanto muitos Arquivos e/ou Filegroups não serão atualizados com muita frequência, o que voce acha de adotar a estratégia de efetuar o backup desses objetos ao invés do backup do banco como um todo? E para esse tipo de backup, o restore sempre parte do filegroup PRIMARY e depois os demais filegroups finalizando com o tail log?

    quinta-feira, 31 de janeiro de 2013 09:55
  • José,

    Quando você esta trabalhando com backups de filegroup ou até mesmo files, o restore do filegroup primary sempre será obrigatório, visto que ele guarda as referencias de metadados e alocação de objetos. Feito o restore do filegrou primary, você deverá restaurar o filegroup que interessa você naquele momento, podendo ser o secundário, terceiro ou quarto filegroup.

    E para finalizar o restore, você também deverá realizar o restore do log, visto que ele guarda a porção ativa do log que não esta contida em nenhum backup. Feito isso é só deixar a base online.

    A demonstração do Junior está ótima no meu ponto de vista.

    Você já sabe qual será o tamanho da base de dados? Dependendo do tamanho, você nem precisaria de um particionamento.

    Se a resposta foi útil, classifique-a.


    Att,
    Marcos Freccia [MTA|MCTS|MCITP|MCT SQL Server 2008]
    Blog|Twitter
    Assine também os feeds clicando aqui

    quinta-feira, 31 de janeiro de 2013 12:38
  • José Marcelo,

    Sim pode ser uma ótimo alternativa.

    E como você percebeu e também o Marcel destacou, primeiro temos que restaurar o Filegroup Primary, posteriormente o Filegroup ou Filegroups desejados e por último o restore do log.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    segunda-feira, 4 de fevereiro de 2013 13:22
    Moderador
  • Bom dia Junior

    Se adicionar mais um arquivo e filegroup ao seu exemplo e efetuarmos o restore de filegroup SECUNDARIO, por que o arquivo do próximo filegroup fica no status RECOVERY_PENDING mesmo tendo sido executado o restore do tail log com a opção recovery?


    • Editado Jose Marcelo segunda-feira, 4 de fevereiro de 2013 15:10
    segunda-feira, 4 de fevereiro de 2013 13:53
  • José Marcelo,

    Você realizou o restore do filegroup primary?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    segunda-feira, 4 de fevereiro de 2013 15:17
    Moderador
  • Fiz o seguinte:

    CREATE DATABASE AES
    ON PRIMARY
    	(NAME = AES_Dados,
    	  FILENAME = N'C:\SQL\AES_Dados.mdf',
              SIZE = 10MB,
              MAXSIZE = 50MB,
              FILEGROWTH = 10%),	
    FILEGROUP Secundario
    	( NAME = AES_Secundario_Dados,
    	  FILENAME = N'C:\SQL\AES_Secundario_Dados.ndf',
              SIZE = 10MB,
              MAXSIZE = 50MB,
              FILEGROWTH = 10%),
    FILEGROUP Terciario
    	( NAME = AES_Terciario_Dados,
    	  FILENAME = N'C:\SQL\AES_Terciario_Dados.ndf',
              SIZE = 10MB,
              MAXSIZE = 50MB,
              FILEGROWTH = 10%)
    LOG ON
    	( NAME = AES_Log,
    	  FILENAME = N'C:\SQL\AES_Log.ldf',
              SIZE = 10MB,
              MAXSIZE = 50MB,
              FILEGROWTH = 10%)
    GO
    
    Use AES
    Go
    
    Create Table T1
     (Codigo Int) On [Primary]
     
    Create Table T2
     (Codigo Int) On [Secundario]
    
    Create Table T3
     (Codigo Int) On [Terciario]
    go
    
    
    
    --Backup Database + File x Filegroup
    Backup Database AES
     File = 'AES_Dados',
     Filegroup = 'Primary'
     To Disk = 'C:\SQL\Backup-Primary-AES.bak'
     With Init, NoFormat
    
    Backup Database AES
     File = 'AES_Secundario_Dados',
     Filegroup = 'Secundario'
     To Disk = 'C:\SQL\Backup-Secundario-AES.bak'
     With Init, NoFormat
    
    Backup Database AES
     File = 'AES_Terciario_Dados',
     Filegroup = 'Terciario'
     To Disk = 'C:\SQL\Backup-Terciario-AES.bak'
     With Init, NoFormat
    
    --Backup Log
    Use master
    Go
    
    Backup Log AES
     To Disk = 'C:\SQL\Backup-Log-AES.bak'
     With Init, NoRecovery
    Go
    
    --Restaurando o Filegroup(Primary)
    Use master
    Go
    
    Restore Database AES 
     Filegroup = 'Primary' 
     From Disk = 'C:\SQL\Backup-Primary-AES.bak'
     With Partial, NoRecovery, Replace
    Go
    
    -- Supondo que ocorreu um problema com o Filgroup secundario
    -- Restaurando o Filegroup(Secundario)
    Use master
    Go
    
    Restore Database AES 
     Filegroup = 'Secundario' 
     From Disk = 'C:\SQL\Backup-Secundario-AES.bak'
     With NoRecovery
    Go
    
    --Restaurando do Log e liberando o Banco de Dados
    Use master
    Go
    
    Restore Log AES 
     From Disk = 'C:\SQL\Backup-Log-AES.bak'
     With Recovery
    Go
    
    -- Verificando o status dos arquivos
    select * from AES.sys.database_files
    


    segunda-feira, 4 de fevereiro de 2013 15:40
  • José Marcelo,

    Cara eu rodei o código e aqui esta tudo certo, o banco de dados esta Normal.

    Após você executar o Script você atualizou a tela?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    segunda-feira, 4 de fevereiro de 2013 16:07
    Moderador
  • Junior

    O seu script com com 1 filegroup alem do primary, funciona perfeitamente. Porém adicionando um novo filegroup e tentar fazer o restore do Primary e secundario, o arquivo do ultimo filegroup  fica com status de RECOVERY_PENDING. O banco não fica no status RECOVERING mas não é possível acessar a tabela do proximo Filegroup, mostrando o erro abaixo:

    Msg 8653, Level 16, State 1, Line 1
    The query processor is unable to produce a plan for the table or view 'T3' because the table resides in a filegroup which is not online.


    • Editado Jose Marcelo quarta-feira, 6 de fevereiro de 2013 11:27
    segunda-feira, 4 de fevereiro de 2013 16:13
  • Bom

    Vou aproveitar um post e acrescentar outra pergunta: Existe alguma maneria de restaurar um filegroup no banco de origem sem a necessidade de partir de um backup FULL e deixar todos os arquivos do filegroups restaurados ONLINE?

    sexta-feira, 8 de fevereiro de 2013 12:54
  • José,

    Sem o Backup Full não é possível.

    Em relação ao Script, sinceramente não consigo entender, eu já testei aqui com diversos filegroups e funcionou.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    sexta-feira, 8 de fevereiro de 2013 13:06
    Moderador
  • Ola Junior

    O script não funcionou pois os backups realizados foram dos filegroups apenas

    sexta-feira, 8 de fevereiro de 2013 13:23
  • José,

    Tente assim:

    CREATE DATABASE AES
    ON PRIMARY
    	(NAME = AES_Dados,
    	  FILENAME = N'C:\BANCOS\AES_Dados.mdf',
              SIZE = 10MB,
              MAXSIZE = 50MB,
              FILEGROWTH = 10%),	
    FILEGROUP Secundario
    	( NAME = AES_Secundario_Dados,
    	  FILENAME = N'C:\BANCOS\AES_Secundario_Dados.ndf',
              SIZE = 10MB,
              MAXSIZE = 50MB,
              FILEGROWTH = 10%),
    FILEGROUP Terceiro
    	( NAME = AES_Terceiro_Dados,
    	  FILENAME = N'C:\BANCOS\AES_Terceiro_Dados.ndf',
              SIZE = 10MB,
              MAXSIZE = 50MB,
              FILEGROWTH = 10%)          
    LOG ON
    	( NAME = AES_Log,
    	  FILENAME = N'C:\BANCOS\AES_Log.ldf',
              SIZE = 10MB,
              MAXSIZE = 50MB,
              FILEGROWTH = 10%)
    GO
    
    Use AES
    Go
    
    Create Table T1
     (Codigo Int) On [Primary]
     
    Create Table T2
     (Codigo Int) On [Secundario]
    
    Create Table T3
     (Codigo Int) On [Terceiro]
      
    --Backup Database
    Backup Database AES
     To Disk = 'C:\BANCOS\Backup-Full-AES.bak'
     With Init, NoFormat
     
    --Backup Database + File x Filegroup
    Backup Database AES
     File = 'AES_Dados',
     Filegroup = 'Primary'
     To Disk = 'C:\BANCOS\Backup-Primary-AES.bak'
     With Init
    
    Backup Database AES
     File = 'AES_Secundario_Dados',
     Filegroup = 'Secundario'
     To Disk = 'C:\BANCOS\Backup-Secundario-AES.bak'
     With Init
    
    Backup Database AES
     File = 'AES_Terceiro_Dados',
     Filegroup = 'Secundario'
     To Disk = 'C:\BANCOS\Backup-Terceiro-AES.bak'
     With Init 
     
    --Backup Log
    Use master
    Go
    
    Backup Log AES
     To Disk = 'C:\BANCOS\Backup-Log-AES.bak'
     With Init, NoRecovery
    Go
    
    --Restaurando o Backup Full e forçando a leitura dos filegroups
    Use master
    Go
    
    Restore Database AES READ_WRITE_FILEGROUPS
     From Disk = 'C:\BANCOS\Backup-Full-AES.bak'
     With File=1, Replace, NoRecovery
    Go 
    
    --Restore File e Filegroup 
    Restore Database AES
     File = 'AES_Secundario_Dados',
     Filegroup = 'Secundario'
     From Disk = 'C:\BANCOS\Backup-Secundario-AES.bak'
     With File=1, Replace, NoRecovery
    
    
    --Restaurando do Log e liberando o Banco de Dados
    Use master
    Go
    
    Restore Log AES 
     From Disk = 'C:\BANCOS\Backup-Log-AES.bak'
     With Recovery
    Go
    
    
    Select * from AES.sys.filegroups
    
    Select * from sys.master_files
    
    Select * from T1
    
    Select * from T2
    
    Select * from T3


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]


    sexta-feira, 8 de fevereiro de 2013 13:56
    Moderador