Criar um backup completo de banco de dados (SQL Server)

 

Publicado: dezembro de 2016

Este tópico descreve como criar um backup de banco de dados completo no SQL Server 2016 usando o SQL Server Management Studio, o Transact-SQL ou PowerShell.

Para obter informações sobre o backup do SQL Server no serviço de armazenamento de Blobs do Microsoft Azure, veja Backup e restauração do SQL Server com o Serviço de Armazenamento de Blobs do Microsoft Azure e Backup do SQL Server para URL.

Limitações e restrições

  • A instrução BACKUP não é permitida em uma transação explícita ou implícita.

  • Os backups criados por uma versão mais recente do SQL Server não podem ser restaurados em versões anteriores do SQL Server.

  • Para ter uma visão geral e aprofundar-se nos conceitos e tarefas de backup, consulte Visão geral de backup (SQL Server) antes de prosseguir.

Recomendações

  • À medida que um banco de dados aumenta, os backups completos de banco de dados levam mais tempo para serem concluídos e exigem mais espaço de armazenamento. Para um banco de dados grande, convém complementar um backup de banco de dados completo com uma série de backups de bancos de dados diferenciais. Para obter mais informações, veja Backups diferenciais (SQL Server) e Backup do SQL Server para URL.

  • Estime o tamanho de um backup de banco de dados completo usando o procedimento armazenado do sistema sp_spaceused.

  • Por padrão, toda operação de backup bem-sucedida acrescenta uma entrada ao log de erros do SQL Server e ao log de eventos do sistema. Se você fizer backup com frequência, essas mensagens de êxito acumularão rapidamente, resultando em logs de erro grandes. Isso pode dificultar a localização de outras mensagens. Em tais situações, você pode suprimir essas entradas de log de backup usando o sinalizador de rastreamento 3226, caso nenhum dos seus scripts dependa dessas entradas. Para obter mais informações, veja, Sinalizadores de rastreamento (Transact-SQL).

Segurança

TRUSTWORTHY é definido como OFF em um backup de banco de dados. Para obter informações sobre como definir TRUSTWORTHY como ON, veja Opções do ALTER DATABASE SET (Transact-SQL).

A partir do SQL Server 2012 , as opções PASSWORD e MEDIAPASSWORD foram descontinuadas para a criação de backups. Você ainda poderá restaurar os backups criados com senhas.

Permissões

As permissões BACKUP DATABASE e BACKUP LOG usam como padrão os membros da função de servidor fixa sysadmin e as funções de banco de dados fixas db_owner e db_backupoperator.

Os problemas de propriedade e permissão no arquivo físico do dispositivo de backup podem interferir em uma operação de backup. SQL Server deve ser capaz de ler e gravar no dispositivo; a conta sob a qual o serviço do SQL Server executa deve ter permissões de gravação. No entanto, sp_addumpdevice, que adiciona uma entrada para um dispositivo de backup nas tabelas do sistema, não verifica permissões de acesso a arquivos. Esses problemas no arquivo físico do dispositivo de backup podem não aparecer até que o recurso físico seja acessado quando o backup ou restauração é tentado.

Ao especificar uma tarefa de backup usando o SQL Server Management Studio, é possível gerar o script Transact-SQL BACKUP correspondente, clicando no botão Script e selecionando um destino para o script.

Fazer o backup de um banco de dados

  1. Depois de se conectar à instância apropriada do Microsoft Mecanismo de Banco de Dados do SQL Server, em Pesquisador de Objetos, clique no nome do servidor para expandir a árvore do servidor.

  2. Expanda Bancos de Dados e selecione um banco de dados de usuário ou expanda Bancos de Dados de Sistema e selecione um banco de dados de sistema.

  3. Clique com o botão direito do mouse no banco de dados, aponte para Tarefas e clique em Backup. Será exibida a caixa de diálogo Backup de Banco de Dados .

Página Geral

  1. Na lista suspensa Banco de Dados, verifique o nome do banco de dados. Opcionalmente, você pode selecionar um banco de dados diferente na lista.

  2. A caixa de texto Modelo de recuperação serve apenas para referência. Você pode executar um backup de banco de dados para qualquer modelo de recuperação (FULL, BULK_LOGGED ou SIMPLE).

  3. Na lista suspensa Tipo de backup, selecione Completo.

    Observe que depois de criar um backup de banco de dados completo, é possível criar um backup de banco de dados diferencial. Para obter mais informações, consulte Criar um backup de banco de dados diferencial (SQL Server).

  4. Opcionalmente, você pode marcar a caixa de seleção Backup somente cópia para criar um backup somente cópia. Um backup somente cópia é um backup do SQL Server que não depende da sequência de backups convencionais do SQL Server. Para obter mais informações, veja Backups somente cópia (SQL Server). Um backup somente cópia não está disponível para o tipo de backup Diferencial.

  5. Em Componente de backup, selecione o botão de opção Banco de dados.

  6. Na seção Destino, use a lista suspensa Fazer backup em para selecionar o destino do backup. Clique em Adicionar para adicionar outros objetos e/ou destinos de backup.

    Para remover um destino de backup, selecione-o e clique em Remover. Para exibir o conteúdo de um destino de backup existente, selecione-o e clique em Conteúdo.

Página Opções de Mídia

  1. Para exibir ou selecionar as opções de mídia, clique em Opções de Mídia no painel Selecionar uma página .

  2. Selecione uma opção Substituir Mídia , com um clique em uma das opções a seguir:

    System_CAPS_ICON_important.jpg Importante


    A opção Substituir mídia será desabilitada se você selecionou URL como destino de backup na página Geral. Para saber mais, confira Fazer backup do banco de dados (página Opções de Mídia)

  • Fazer backup no conjunto de mídias existente

    System_CAPS_ICON_important.jpg Importante


    Se você planeja usar criptografia, não selecione essa opção. Se você selecionar esta opção, as opções de criptografia na página Opções de Backup serão desabilitadas. A criptografia não tem suporte ao anexar ao conjunto de backup existente.

    Para essa opção, clique em Anexar ao conjunto de backup existente ou Substituir todos os conjuntos de backup existentes. Para obter mais informações, consulte Conjuntos de mídias, famílias de mídia e conjuntos de backup (SQL Server).

    Opcionalmente, selecione Verificar nome do conjunto de mídias e validade do conjunto de backup para que a operação de backup verifique a data e a hora em que o conjunto de mídias e de backup expiram.

    Como opção, digite um nome na caixa de texto Nome do conjunto de mídias . Se nenhum nome for especificado, um conjunto de mídias com um nome em branco será criado. Se você especificar um nome de conjunto de mídias, a mídia (fita ou disco) é verificada para ver se o nome real corresponde ao nome digitado.

  • Fazer backup em um novo conjunto de mídias e apagar todos os conjuntos de backup existentes

    Para essa opção, digite um nome na caixa de texto Nome do novo conjunto de mídias e, opcionalmente, descreva o conjunto de mídias na caixa de texto Descrição do novo conjunto de mídias .

  1. Na seção Confiabilidade , como opção, marque:

  2. A seção Log de transações estará inativa exceto se o backup estiver sendo feito em um log de transações (como especificado na seção Tipo de backup da página Geral).

  3. Na seção Unidade de fita, a opção Descarregar a fita após o backup estará ativa se você estiver fazendo backup em uma unidade de fita (como especificado na seção Destino da página Geral). Clicar nessa opção ativa a opção Rebobinar a fita antes de descarregar .

Página Opções de Backup

  1. Para exibir ou selecionar as opções de backup, clique em Opções de Backup no painel Selecionar uma página .

  2. Na caixa de texto Nome, aceite o nome do conjunto de backup padrão ou insira um nome diferente para o conjunto de backup.

  3. Opcionalmente, na caixa de texto Descrição, você pode inserir uma descrição do conjunto de backup.

  4. Especifique quando o conjunto de backup irá expirar e pode ser substituído sem ignorar explicitamente a verificação dos dados de expiração:

    • Para que o conjunto de backup expire depois de um número específico de dias, clique em Depois (a opção padrão) e digite quantos dias depois da criação do conjunto ele deve expirar. Esse valor pode ser de 0 a 99999 dias; 0 dia significa que o conjunto de backup nunca vai expirar.

      O valor padrão é definido na opção Retenção de mídia de backup padrão (em dias) da caixa de diálogo Propriedades do Servidor (página Configurações do Banco de Dados). Para acessar, clique com o botão direito do mouse no nome do servidor em Pesquisador de Objetos e selecione propriedades. Depois, selecione a página Configurações de Banco de Dados.

    • Para que o conjunto de backup expire em uma data específica, clique no campo Eme digite a data de expiração do conjunto.

      Para obter mais informações sobre datas de validade de backup, consulte BACKUP (Transact-SQL).

  5. Na seção Compactação, use a lista suspensa Definir compactação de backup para selecionar o nível de compactação desejado. SQL Server 2008 Enterprise e posteriores dão suporte para compactação de backup. Por padrão, a compactação de um backup depende do valor da opção de configuração de servidor padrão de compactação de backup. Porém, independentemente do padrão atual do nível do servidor, é possível compactar um backup, marcando a opção Compactar backup e evitar a compactação marcando Não compactar o backup.

    Para obter mais informações sobre configurações de compactação de backup, consulte Exibir ou configurar a Opção de Configuração de Servidor backup compression default

  6. Na seção criptografia, use a caixa de seleção Criptografar backup para decidir se deverá ser usada criptografia para o backup. Use a lista suspensa Algoritmo para selecionar um algoritmo de criptografia. Use a lista suspensa Certificado ou Chave Assimétrica para selecionar um Certificado ou Chave Assimétrica existente. A criptografia tem suporte no SQL Server 2014 ou posterior. Para obter mais detalhes sobre as opções de criptografia, consulte Fazer backup do banco de dados (página Opções de Backup).

É possível usar o Assistente de Plano de Manutenção para criar backups de bancos de dados.

Exemplos

A. Backup completo em disco em local padrão

Neste exemplo, será feito backup do banco de dados Sales em disco no local de backup padrão. Nunca foi feito backup do Sales.

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de Dados do SQL Server e expanda-a.

  2. Expanda Banco de Dados, clique com o botão direito do mouse em Sales, aponte para Tarefas e clique em Fazer Backup....

  3. Clique em OK.

B. Backup completo em disco em local não padrão

Neste exemplo, será feito backup do banco de dados Sales em disco em E:\MSSQL\BAK. Já foram feitos backups anteriores do Sales.

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de Dados do SQL Server e expanda-a.

  2. Expanda Banco de Dados, clique com o botão direito do mouse em Sales, aponte para Tarefas e clique em Fazer Backup....

  3. Na página Geral, na seção Destino, selecione a opção Disco na lista suspensa Fazer backup em:.

  4. Clique em Remover até que todos os arquivos de backup existentes sejam removidos.

  5. Clique em Adicionar e a caixa de diálogo Selecionar Destino do Backup será aberta.

  6. Insira E:\MSSQL\BAK\Sales_20160801.bak na caixa de texto Nome de arquivo.

  7. Clique em OK.

  8. Clique em OK.

C. Criar um backup criptografado

Neste exemplo, será feito backup do banco de dados Sales com criptografia no local de backup padrão. Uma chave mestra do banco de dados já foi criada. Um certificado chamado MyCertificate já foi criado. Um exemplo de T-SQL de como criar uma chave mestra do banco de dados e um certificado pode ser visto em Criar um backup criptografado.

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de Dados do SQL Server e expanda-a.

  2. Expanda Banco de Dados, clique com o botão direito do mouse em Sales, aponte para Tarefas e clique em Fazer Backup....

  3. Na página Opções de Mídia da seção Substituir mídia, selecione Fazer backup em um novo conjunto de mídias e apagar todos os conjuntos de backup existentes.

  4. Na página Opções de Backup da seção Criptografia, marque a caixa de seleção Criptografar backup.

  5. Na lista suspensa Algoritmo, selecione AES 256.

  6. Na lista suspensa Certificado ou Chave Assimétrica, selecione MyCertificate.

  7. Clique em OK.

D. Fazer backup do serviço de Armazenamento de Blobs do Microsoft Azure

Etapas comuns

Os três exemplos abaixo executam um backup completo do banco de dados do Sales no serviço de Armazenamento de Blobs do Microsoft Azure. O nome da Conta de armazenamento é mystorageaccount. O contêiner é chamado myfirstcontainer. Para resumir, as quatro primeiras etapas são listadas aqui uma vez e todos os exemplos serão iniciados na Etapa 5.

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de Dados do SQL Server e expanda-a.

  2. Expanda Banco de Dados, clique com o botão direito do mouse em Sales, aponte para Tarefas e clique em Fazer Backup....

  3. Na página Geral, na seção Destino, selecione a opção URL na lista suspensa Fazer backup em:.

  4. Clique em Adicionar e a caixa de diálogo Selecionar Destino do Backup será aberta.

    D1. Já existe um backup distribuído para URL e uma credencial do SQL Server
    Uma política de acesso armazenado foi criada com direitos de leitura, gravação e listagem. A credencial do SQL Server, https://mystorageaccount.blob.core.windows.net/myfirstcontainer, foi criada usando uma Assinatura de Acesso Compartilhado associada à política de acesso armazenado.

    1. Selecione https://mystorageaccount.blob.core.windows.net/myfirstcontainer do contêiner de armazenamento do Azure: caixa de texto

    2. Na caixa de texto Arquivo de Backup:, digite Sales_stripe1of2_20160601.bak.

    3. Clique em OK.

    4. Repita as etapas de 4 e 5.

    5. Na caixa de texto Arquivo de Backup:, digite Sales_stripe2of2_20160601.bak.

    6. Clique em OK.

    7. Clique em OK.

    D2. Existe uma assinatura de acesso compartilhado e não existe uma credencial do SQL Server

    1. Digite https://mystorageaccount.blob.core.windows.net/myfirstcontainer na caixa de texto Contêiner de armazenamento do Azure:

    2. Insira a assinatura de acesso compartilhado na caixa de texto Política de Acesso Compartilhado:.

    3. Clique em OK.

    4. Clique em OK.

    D3. Não há uma assinatura de acesso compartilhado

    1. Clique no botão Novo contêiner e a caixa de diálogo Conectar-se a uma Assinatura da Microsoft será aberta.

    2. Conclua a caixa de diálogo Conectar-se a uma Assinatura da Microsoft e clique em OK para retornar à caixa de diálogo Selecionar um destino de backup. Veja Conectar-se a uma assinatura do Microsoft Azure para obter mais informações.

    3. Clique em OK na caixa de diálogo Selecionar Destino do Backup.

    4. Clique em OK.

Para criar um backup de banco de dados completo

  1. Execute a instrução BACKUP DATABASE para criar o backup do banco de dados completo, especificando:

    • O nome do banco de dados do qual fazer backup.

    • O dispositivo de backup em que o backup completo do banco de dados será gravado.

    A sintaxe básica Transact-SQL para o backup de banco de dados completo é:

    BACKUP DATABASE database

    TO backup_device [ ,...n ]

    [ WITH com_opções [ ,...o ] ] ;

    OpçãoDescrição
    Banco de DadosÉ o banco de dados do qual fazer backup.
    backup_device [ ,...n ]Especifica uma lista de 1 a 64 dispositivos de backup a serem usados para a operação de backup. Você pode especificar um dispositivo de backup físico ou pode especificar um dispositivo de backup lógico correspondente, se já definido. Para especificar um dispositivo de backup físico, use a opção DISK ou TAPE:

    { DISK | TAPE } =physical_backup_device_name

    Para obter mais informações, consulte Dispositivos de backup (SQL Server).
    WITH with_options [ ,...o ]Opcionalmente, especifica uma ou mais opções adicionais, o. Para obter informações sobre os fundamentos de opções, consulte a etapa 2.
  2. Opcionalmente, especifique uma ou mais opções WITH. Algumas opções WITH básicas são descritas aqui. Para obter informações sobre todas as opções WITH, consulte BACKUP (Transact-SQL).

    • Opções WITH do conjunto de backup básico:

      { COMPRESSION | NO_COMPRESSION }
      No SQL Server 2008 Enterprise e versões posteriores somente, especifica se compressão de backup é executada neste backup, substituindo o padrão de nível de servidor.

      ENCRYPTION (ALGORITHM, SERVER CERTIFICATE |ASYMMETRIC KEY)
      No SQL Server 2014 ou em versões posteriores somente, especifique o algoritmo de criptografia a ser usado, e o certificado ou chave assimétrica usada para proteger a criptografia.

      DESCRIPTION = { 'text' | @text_variable }
      Especifica o texto de forma livre que descreve o conjunto de backup. A cadeia de caracteres pode conter um máximo de 255 caracteres.

      NAME = { backup_set_name | @backup_set_name_var }
      Especifica o nome do conjunto de backup. Os nomes podem ter no máximo de 128 caracteres. Se NAME não estiver especificado, ele estará em branco.

    • Opções WITH do conjunto de backup básico:

      Por padrão, BACKUP anexa o backup a um conjunto de mídias existente, preservando conjuntos de backup existentes. Para especificar isso explicitamente, use a opção NOINIT. Para obter informações sobre o acréscimo a conjuntos de backup existentes, consulte Conjuntos de mídias, famílias de mídia e conjuntos de backup (SQL Server).

      Alternativamente, para formatar a mídia de backup, use a opção FORMAT:

      FORMAT [ , MEDIANAME= { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]
      Use a cláusula FORMAT quando estiver usando a mídia pela primeira vez ou quando quiser sobrescrever todos os dados existentes Opcionalmente, atribua à nova mídia um nome e uma descrição.

      System_CAPS_ICON_important.jpg Importante


      Tenha muito cuidado ao usar a cláusula FORMAT ou a instrução BACKUP, pois isso destrói qualquer backup previamente armazenado na mídia de backup.

Exemplos (Transact-SQL)

A. Fazendo backup para um dispositivo de disco.

O exemplo a seguir faz backup de banco de dados completo AdventureWorks2012 em um disco, usando FORMAT para criar um novo conjunto de mídia.

USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'  
   WITH FORMAT,  
      MEDIANAME = 'Z_SQLServerBackups',  
      NAME = 'Full Backup of AdventureWorks2012';  
GO  

B. Fazendo backup para um dispositivo de fita

O exemplo a seguir faz backup do banco de dados completo AdventureWorks2012 em fita, anexando o backup aos backups anteriores.

USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
   TO TAPE = '\\.\Tape0'  
   WITH NOINIT,  
      NAME = 'Full Backup of AdventureWorks2012';  
GO  

C. Fazendo backup em um dispositivo de fita lógico

O exemplo a seguir cria um dispositivo de backup lógico para uma unidade de fita. O exemplo faz backup completo do banco de dados AdventureWorks2012 nesse dispositivo.

-- Create a logical backup device,   
-- AdventureWorks2012_Bak_Tape, for tape device \\.\tape0.  
USE master;  
GO  
EXEC sp_addumpdevice 'tape', 'AdventureWorks2012_Bak_Tape', '\\.\tape0'; USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
   TO AdventureWorks2012_Bak_Tape  
   WITH FORMAT,  
      MEDIANAME = 'AdventureWorks2012_Bak_Tape',  
      MEDIADESCRIPTION = '\\.\tape0',   
      NAME = 'Full Backup of AdventureWorks2012';  
GO  

Use o cmdlet Backup-SqlDatabase. Para indicar explicitamente que este é um backup completo de banco de dados, especifique o parâmetro -BackupAction com seu valor padrão Database. Esse parâmetro é opcional para backups completos de banco de dados.

Exemplos

A. Backup completo local

O exemplo a seguir cria um backup de banco de dados completo do banco de dados MyDB para o local de backup padrão da instância de servidor Computer\Instance. Como opção, esse exemplo especifica -BackupAction Database.

Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Database  

B. Backup completo para o Microsoft Azure

O exemplo a seguir cria um backup completo do banco de dados Sales na instância MyServer para o serviço de Armazenamento de Blobs do Microsoft Azure. Uma política de acesso armazenado foi criada com direitos de leitura, gravação e listagem. A credencial do SQL Server, https://mystorageaccount.blob.core.windows.net/myfirstcontainer, foi criada usando uma Assinatura de Acesso Compartilhado associada à política de acesso armazenado. O comando do PowerShell usa o parâmetro BackupFile para especificar o local (URL) e o nome do arquivo de backup.

import-module sqlps;
$container = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer';
$FileName = 'Sales.bak';
$database = 'Sales';
$BackupFile = $container + '/' + $FileName ;
  
Backup-SqlDatabase -ServerInstance "MyServer" –Database $database -BackupFile $BackupFile;

Para configurar e usar o provedor do SQL Server PowerShell

Solução de problemas de operações de backup e restauração do SQL Server
Visão geral do backup (SQL Server)
Backups de log de transações (SQL Server)
Conjuntos de mídias, famílias de mídia e conjuntos de backup (SQL Server)
sp_addumpdevice (Transact-SQL)
BACKUP (Transact-SQL)
Fazer backup do banco de dados (página Geral)
Fazer backup do banco de dados (página Opções de Backup)
Backups diferenciais (SQL Server)
Backups de bancos de dados completos (SQL Server)

Contribuições da comunidade

ADICIONAR
Mostrar: