Criar um backup de banco de dados completo

Aplica-se a:SQL Server

Este artigo descreve como criar um backup completo de banco de dados no SQL Server usando SQL Server Management Studio (SSMS), Transact-SQL ou PowerShell.

Para obter mais informações, consulte Backup e restauração do SQL Server com o Armazenamento de Blobs do Azure e Backup do SQL Server para URL para o Armazenamento de Blobs do Azure. Para uma visão geral dos conceitos e tarefas de backup, veja Visão geral de Backup (SQL Server).

Recomendações

• À medida que um banco de dados cresce, backups completos do banco de dados levam mais tempo e exigem mais armazenamento. Para bancos de dados grandes, complemente backups completos com uma série de backups diferenciais de banco de dados.

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

• Por padrão, cada backup bem-sucedido adiciona uma entrada ao registro de erros do SQL Server e ao registro de eventos do sistema. Backups frequentes podem preencher esses logs e dificultar a localização de outras mensagens.

  Para suprimir entradas de log de backup, use o sinalizador de rastreamento 3226, desde que seus scripts não dependam delas.

Limitações

Você não pode usar a instrução BACKUP em uma transação explícita ou implícita.

Você não pode restaurar backups de versões mais recentes do SQL Server em versões anteriores. Por exemplo, você não pode restaurar um backup do SQL Server 2025 (17.x) no SQL Server 2022 (16.x).

Segurança

TRUSTWORTHY é definido como OFF em um backup de banco de dados. Para definir TRUSTWORTHY como ON, veja as ALTER DATABASE SET opções.

A partir do SQL Server 2012 (11.x), as PASSWORD opções e MEDIAPASSWORD não estão disponíveis para criar backups. Você ainda poderá restaurar os backups criados com senhas.

Permissões

As permissões BACKUP DATABASE e BACKUP LOG são concedidas por padrão aos membros da função de servidor fixa sysadmin e das funções de banco de dados fixas db_owner e db_backupoperator.

A conta do serviço SQL Server deve ter permissões de leitura e gravação no arquivo físico do dispositivo de backup. Problemas de propriedade ou permissão nesse arquivo impedem operações de backup e restauração, e só aparecem quando a operação de backup ou restauração é executada.

Por exemplo, o procedimento armazenado do sistema sp_addumpdevice , que adiciona uma entrada de dispositivo de backup às tabelas do sistema, não valida o acesso a arquivos.

Usar o SQL Server Management Studio

1. No Pesquisador de Objetos, conecte-se ao Mecanismo de Banco de Dados e então expanda a árvore de servidores.

2. Expanda Bancos de Dados e então selecione um banco de dados de usuários. Ou, expanda os Bancos de Dados do Sistema e selecione um banco de dados do sistema.

3. Clique com o botão direito no banco de dados que você quer fazer backup, aponte para Tarefas e então selecione Backup....

4. Na caixa de diálogo Fazer Backup do Banco de Dados, o banco de dados selecionado aparece na lista suspensa. Você pode mudar o banco de dados para qualquer outro banco de dados do servidor.

5. Na lista de tipos de backup , selecione um tipo de backup. O padrão é Completo.

   Importante

   Você deve executar pelo menos um backup completo do banco de dados antes de executar um backup diferencial ou de log de transações.

6. Em Componente de Backup, clique em Banco de Dados.

7. Na seção Destino, examine a localização padrão para o arquivo de backup (na pasta ../mssql/data).

   Use a lista Fazer backup em para selecionar um dispositivo diferente. Selecione Adicionar para adicionar objetos de backup ou destinos. Você pode dividir o conjunto de backup em vários arquivos para aumentar a velocidade de backup.

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

8. Opcionalmente, revise as outras configurações nas páginas de Opções de Mídia e Opções de Backup .

   Para mais informações sobre opções de backup, veja Banco de Dados de Backup (Página Geral), Banco de Dados de Backup (página de Opções de Mídia) e Banco de Dados de Backup (Página de Opções de Backup).

9. Selecione OK para iniciar o backup.

10. Quando o backup for concluído com sucesso, selecione OK para fechar o diálogo.

Observações

• Quando você especifica uma tarefa de backup no SQL Server Management Studio, pode gerar o script Transact-SQL correspondente BACKUP selecionando o botão Script e, em seguida, selecionando um destino para o script.

• Depois de criar um backup completo do banco de dados, você pode criar um backup diferencial do banco de dados ou um backup do log de transações.

• Opcionalmente, selecione a caixa de seleção Backup apenas com cópia para criar um backup somente cópia. Um backup somente cópia é um backup do SQL Server que é independente da sequência de backups do SQL Server convencionais. Para obter mais informações, veja Backups somente cópia. Backups apenas com cópia não estão disponíveis para o tipo de backup diferencial .

• A opção Sobrescrever mídia é desativada na página de Opções de Mídia quando você faz backup para uma URL.

Exemplos

Para os exemplos a seguir, crie um banco de dados de teste usando o seguinte código Transact-SQL:

USE master;
GO

CREATE DATABASE [SQLTestDB];
GO

USE [SQLTestDB];
GO

CREATE TABLE SQLTest
(
    ID INT NOT NULL PRIMARY KEY,
    c1 VARCHAR (100) NOT NULL,
    dt1 DATETIME DEFAULT getdate() NOT NULL
);
GO

USE [SQLTestDB];
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1');
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2');
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3');
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4');
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5');
GO

SELECT *
FROM SQLTest;
GO

A. Backup completo em disco para o local padrão

Este exemplo faz backup do SQLTestDB banco de dados para o disco no local padrão de backup.

1. No Pesquisador de Objetos, conecte-se ao Mecanismo de Banco de Dados e então expanda a árvore de servidores.

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

3. Selecione OK.

4. Quando o backup for concluído com sucesso, selecione OK para fechar o diálogo.

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

Este exemplo faz backup do SQLTestDB banco de dados no disco em um local que você escolher.

1. No Pesquisador de Objetos, conecte-se ao Mecanismo de Banco de Dados e então expanda a árvore de servidores.

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

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

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

5. Selecione Adicionar. A caixa de diálogo Selecionar Destino de Backup se abre.

6. Insira um caminho válido e um nome de arquivo na caixa Nome do arquivo . Use .bak como extensão para simplificar a classificação de arquivos.

7. Selecione OK e depois selecione OK novamente para iniciar o backup.

8. Quando o backup for concluído com sucesso, selecione OK para fechar o diálogo.

C. Criar um backup criptografado

Este exemplo faz backup do SQLTestDB banco de dados com criptografia para o local padrão de backup.

1. No Pesquisador de Objetos, conecte-se ao Mecanismo de Banco de Dados e então expanda a árvore de servidores.

2. Expanda Bancos de Dados, expanda Bancos de Dados do Sistema, clique com o botão direito do mouse mastere selecione Nova Consulta para abrir uma janela de consulta com uma conexão com seu SQLTestDB banco de dados.

3. Execute os seguintes comandos para criar uma chave mestra de banco de dados e um certificado no master banco de dados.

   -- Create the master key.
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
   
   -- If the master key already exists, open it in the same session that you create the certificate. (See next step.)
   OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>';
   
   -- Create the certificate encrypted by the master key.
   CREATE CERTIFICATE MyCertificate
   WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';
   

4. No Pesquisador de Objetos, no nó Banco de Dados, clique com o botão direito do mouse em SQLTestDB, aponte para Tarefas e selecione Fazer Backup....

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

6. Na página Opções de Backup , na seção Criptografia , selecione Criptografar backup.

7. Na lista Algoritmo , selecione AES 256.

8. Na lista Certificado ou chave assimétrica, selecione MyCertificate.

9. Selecione OK.

D. Fazer backup no Armazenamento de Blobs do Azure

Este exemplo cria um backup completo do banco de dados de SQLTestDB no Armazenamento de Blobs do Azure. O exemplo assume que você já tem uma conta de armazenamento com um contêiner de blobs. O exemplo cria uma assinatura de acesso compartilhada e falha se o contêiner tiver uma assinatura de acesso compartilhada existente.

Se você não tem um container Armazenamento de Blobs em uma conta de armazenamento, crie um antes de continuar. Confira Criar uma conta de armazenamento de uso geral e Criar um contêiner.

1. No Pesquisador de Objetos, conecte-se ao Mecanismo de Banco de Dados e então expanda a árvore de servidores.

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

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

4. Selecione Adicionar. A caixa de diálogo Selecionar Destino de Backup se abre.

5. Se você já registrou o container de armazenamento Azure que deseja usar com o SSMS, selecione-o. Caso contrário, selecione Novo contêiner para registrar um novo contêiner.

6. Na caixa de diálogo Conectar a uma Assinatura da Microsoft, faça login na sua conta.

7. Na caixa Selecionar Conta de Armazenamento , selecione sua conta de armazenamento.

8. Na caixa Selecionar Contêiner de Blobs, selecione seu contêiner de blobs.

9. Na caixa do calendário de Expiração da Política de Acesso Compartilhado , selecione uma data de expiração para a política de acesso compartilhado que este exemplo cria.

10. Selecione Criar Credencial para gerar uma assinatura de acesso compartilhado e credencial no SSMS.

11. Selecione OK para fechar a caixa de diálogo Conectar a uma Assinatura da Microsoft.

12. Na caixa Arquivo de Backup , altere o nome do arquivo de backup se desejar.

13. Selecione OK para fechar a caixa de diálogo Selecionar um destino de backup .

14. Selecione OK para iniciar o backup.

15. Quando o backup for concluído com sucesso, selecione OK para fechar o diálogo.

Usar Transact-SQL

Crie um backup completo do banco de dados executando a BACKUP DATABASE instrução. Especificar:

• 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 <with_options> [ , ...o ] ];

Opcionalmente, especifique uma ou mais WITH opções. Algumas opções básicas WITH são descritas aqui. Para informações sobre todas as WITH opções, veja BACKUP.

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

• { COMPACTAÇÃO | SEM_COMPACTAÇÃO }. O SQL Server especifica se a compressão de backup é realizada no backup, sobrescrevendo o padrão em nível de servidor.

• CRIPTOGRAFIA (ALGORITMO, SERVIDOR CERTIFICATE | ASYMMETRIC KEY). No SQL Server 2014 ou posterior, especifica o algoritmo de criptografia a ser usado e o certificado ou chave assimétrica a ser usado para garantir a criptografia.

• DESCRIÇÃO = { 'texto' | @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 você não especificar NAME, está em branco.

Por padrão, BACKUP acrescenta o backup a um conjunto de mídias existente, preservando os conjuntos de backup existentes. Para especificar explicitamente essa configuração, use a opção NOINIT . Para obter informações sobre como acrescentar a conjuntos de backup existentes, consulte Conjuntos de mídia, famílias de mídia e conjuntos de backup (SQL Server).

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 FORMAT cláusula quando usar mídia pela primeira vez, ou quando quiser sobrescrever todos os dados existentes. Opcionalmente, atribua à nova mídia um nome e uma descrição.

Exemplos

Para os exemplos a seguir, crie um banco de dados de teste usando o seguinte código Transact-SQL:

USE master;
GO

CREATE DATABASE [SQLTestDB];
GO

USE [SQLTestDB];
GO

CREATE TABLE SQLTest
(
    ID INT NOT NULL PRIMARY KEY,
    c1 VARCHAR (100) NOT NULL,
    dt1 DATETIME DEFAULT GETDATE() NOT NULL
);
GO

USE [SQLTestDB];
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1');
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2');
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3');
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4');
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5');
GO

SELECT *
FROM SQLTest;
GO

A. Fazer backup em um dispositivo de disco

O exemplo a seguir faz backup do banco de dados completo SQLTestDB em disco. Ele usa FORMAT para criar um novo conjunto de mídia.

USE SQLTestDB;
GO

BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
WITH FORMAT,
     MEDIANAME = 'SQLServerBackups',
     NAME = 'Full Backup of SQLTestDB';
GO

B. Fazer backup em um dispositivo de fita

O exemplo a seguir faz backup do banco de dados completo SQLTestDB em fita. Ele acrescenta o backup aos backups anteriores.

USE SQLTestDB;
GO

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

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

O exemplo a seguir cria um dispositivo de backup lógico para uma unidade de fita. Em seguida, o exemplo faz backup do banco de dados completo SQLTestDB para esse dispositivo.

-- Create a logical backup device,
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO

EXECUTE sp_addumpdevice 'tape', 'SQLTestDB_Bak_Tape', '\\.\tape0';

USE SQLTestDB;
GO

BACKUP DATABASE SQLTestDB
TO SQLTestDB_Bak_Tape
WITH FORMAT,
     MEDIANAME = 'SQLTestDB_Bak_Tape',
     MEDIADESCRIPTION = '\\.\tape0',
     NAME = 'Full Backup of SQLTestDB';
GO

PowerShell

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

Se você abrir uma janela do PowerShell dentro do SSMS para conectar ao Mecanismo de Banco de Dados do SQL Server, pode omitir a parte das credenciais porque sua credencial no SSMS estabelece automaticamente a conexão entre o PowerShell e o Mecanismo 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 <myDatabase> para o local de backup padrão da instância de servidor Computer\Instance. Opcionalmente, este exemplo especifica -BackupAction Database.

Para obter exemplos de sintaxe completa, consulte Backup-SqlDatabase.

$credential = Get-Credential

Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential

B. Backup completo para o Azure

O exemplo a seguir cria um backup completo do banco de dados <myDatabase> na instância <myServer> para o Armazenamento de Blobs. Uma política de acesso armazenado é criada com direitos de leitura, gravação e listagem. A credencial do SQL Server, https://<myStorageAccount>.blob.core.windows.net/<myContainer>, é criada usando uma assinatura de acesso compartilhada associada à política de acesso armazenado. O comando usa o $backupFile parâmetro para especificar o local (URL) e o nome do arquivo de backup.

$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential

Tarefas relacionadas

• Criar um backup de banco de dados diferencial (SQL Server)
• Restaurar um backup de banco de dados usando o SSMS
• Restaure um backup de banco de dados sob o modelo de recuperação simples
• Restaurar banco de dados para ponto de falha – recuperação completa
• Restaurar um banco de dados para um novo local (SQL Server)
• Usar o Assistente de Plano de Manutenção

Conteúdo relacionado

• Solucionar 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ídia, famílias de mídia e conjuntos de backup (SQL Server)
• sys.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 completos do banco de dados (SQL Server)