Criar um backup de banco de dados completo

Artigo
01/09/2024

Aplica-se a: SQL Server

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

Para obter mais informações, veja Backup e restauração do SQL Server com o Armazenamento de Blobs do 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.

Antes de prosseguir, confira uma visão geral e aprofundamento sobre os conceitos e tarefas de backup em Visão geral do backup (SQL Server).

Recomendações

À medida que um banco de dados aumenta de tamanho, os backups completos do banco de dados levam mais tempo para serem concluídos e exigem mais espaço de armazenamento. Para bancos de dados grandes, considere complementar backups de banco de dados completos com uma série de backups de bancos de dados diferenciais.
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 serão acumuladas muito rapidamente, resultando em logs de erros imensos que dificultam 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, confira 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, confira Opções de ALTER DATABASE SET (Transact-SQL).

A partir do SQL Server 2012 (11.x), as opções PASSWORD e MEDIAPASSWORD não estão mais disponíveis 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. O serviço SQL Server deve ler e gravar no dispositivo. A conta sob a qual o serviço SQL Server é executado deve ter permissões de gravação no dispositivo de backup. 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. Os problemas no arquivo físico do dispositivo de backup podem não aparecer até que o backup seja usado ou uma tentativa de restauração seja feita.

Como usar o SQL Server Management Studio.

Observação

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.

Depois de se conectar à instância apropriada do Mecanismo de Banco de Dados do Microsoft SQL Server, no Pesquisador de Objetos, expanda a árvore do servidor.
Expanda Bancos de Dadose selecione um banco de dados de usuário ou expanda Bancos de Dados de Sistema e selecione um banco de dados de sistema.
Clique com o botão direito do mouse no banco de dados do qual deseja fazer backup, aponte para Tarefas e selecione Fazer Backup....
Na caixa de diálogo Fazer Backup do Banco de Dados, o banco de dados selecionado aparece na lista suspensa (que você pode alterar para qualquer outro banco de dados no servidor).
Na lista suspensa Tipo de backup, selecione o tipo de backup – o padrão é Completo.

Importante

É necessário executar pelo menos um backup de banco de dados completo antes de ser possível executar um backup diferencial ou de log de transações.
Em Componente de Backup, clique em Banco de Dados.
Na seção Destino, examine a localização padrão para o arquivo de backup (na pasta ../mssql/data).

Você pode usar a lista suspensa Fazer Backup em para selecionar um dispositivo diferente. Selecione Adicionar para adicionar objetos de backup e ou destinos. Você pode retirar 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 exibir o conteúdo de um destino de backup existente, selecione-o e clique em Conteúdo.
(opcional) Examine as outras configurações disponíveis nas páginas Opções de Mídia e Opções de Backup.

Para saber mais sobre as várias opções de backup, confira a página Geral, a página Opções de Mídia e a página Opções de Backup.
Selecione OK para iniciar o backup.
Quando o backup for concluído com êxito, selecione OK para fechar a caixa de diálogo do SQL Server Management Studio.

Informações adicionais

Após criar um backup de banco de dados completo, você pode criar um backup de banco de dados diferencial ou um backup de log de transações.
(opcional) Marque 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 é independente da sequência de backups do SQL Server convencionais. Para obter mais informações, confira Backups somente cópia (SQL Server). Um backup somente cópia não está disponível para o tipo de backup Diferencial.
A opção Substituir mídia estará desabilitada na página Opções de Mídia se você estiver fazendo backup para uma URL.

Exemplos

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

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 NOT NULL DEFAULT getdate()
   );
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

R. Backup completo no disco no local padrão

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

Depois de se conectar à instância apropriada do Mecanismo de Banco de Dados do Microsoft SQL Server, no Pesquisador de Objetos, expanda a árvore do servidor.
Expanda Banco de Dados, clique com o botão direito do mouse em SQLTestDB, aponte para Tarefas e selecione Fazer Backup....
Selecione OK.
Quando o backup for concluído com êxito, selecione OK para fechar a caixa de diálogo do SQL Server Management Studio.

Fazer backup do SQL

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

Neste exemplo, o banco de dados SQLTestDB terá o backup feito em um disco em uma localização de sua escolha.

Depois de se conectar à instância apropriada do Mecanismo de Banco de Dados do Microsoft SQL Server, no Pesquisador de Objetos, expanda a árvore do servidor.
Expanda Banco de Dados, clique com o botão direito do mouse em SQLTestDB, aponte para Tarefas e selecione Fazer Backup....
Na página Geral , na seção Destino , selecione a opção Disco na lista suspensa Fazer backup em: .
Clique em Remover até que todos os arquivos de backup existentes sejam removidos.
Clique em Adicionar e a caixa de diálogo Selecionar Destino do Backup será aberta.
Insira um caminho e um nome de arquivo válidos na caixa de texto Nome do arquivo e use .bak como extensão para simplificar a classificação desse arquivo.
Selecione OK, depois OK novamente para iniciar o backup.
Quando o backup for concluído com êxito, selecione OK para fechar a caixa de diálogo do SQL Server Management Studio.

Alterar local do BD

C. Criar um backup criptografado

Neste exemplo, será feito backup do banco de dados SQLTestDB com criptografia no local de backup padrão.

Depois de se conectar à instância apropriada do Mecanismo de Banco de Dados do Microsoft SQL Server, no Pesquisador de Objetos, expanda a árvore do servidor.
Expanda Bancos de dados, Bancos de Dados do Sistema, clique com o botão direito do mouse em master e clique em Nova Consulta para abrir uma janela de consulta com uma conexão com seu banco de dados SQLTestDB.

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

-- Create the master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';  

-- 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 = '23987hxJ#KL95234nl0zBe'

-- Create the certificate encrypted by the master key
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';

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....
Na página Opções de Mídia da seção Substituir mídia, clique em Fazer backup em um novo conjunto de mídias e apagar todos os conjuntos de backup existentes.
Na página Opções de Backup da seção Criptografia , marque a caixa de seleção Criptografar backup .
Na lista suspensa Algoritmo, clique em AES 256.
Na lista suspensa Certificado ou Chave Assimétrica , selecione MyCertificate.
Selecione OK.

Backup criptografado

D. Fazer backup no Armazenamento de Blobs do Azure

O exemplo cria um backup completo do banco de dados do SQLTestDB para o 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 compartilhado para você. Esse exemplo falhará se o contêiner tiver uma assinatura de acesso compartilhado existente.

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

Depois de se conectar à instância apropriada do Mecanismo de Banco de Dados do Microsoft SQL Server, no Pesquisador de Objetos, expanda a árvore do servidor.
Expanda Banco de Dados, clique com o botão direito do mouse em SQLTestDB, aponte para Tarefas e selecione Fazer Backup....
Na página Geral , na seção Destino , selecione a opção URL na lista suspensa Fazer backup em: .
Clique em Adicionar e a caixa de diálogo Selecionar Destino do Backup será aberta.
Se você tiver registrado anteriormente o contêiner de armazenamento do Azure que deseja usar com o SQL Server Management Studio, selecione-o. Caso contrário, selecione Novo contêiner para registrar um novo contêiner.
Na caixa de diálogo Conectar-se a uma Assinatura da Microsoft, entre na sua conta.
Na caixa de texto suspensa Selecionar Conta de Armazenamento, selecione sua conta de armazenamento.
Na caixa de texto suspensa Selecionar Contêiner de Blobs, selecione seu contêiner de blobs.
Na caixa de calendário suspensa Expiração da Política de Acesso Compartilhado, selecione uma data de validade para a política de acesso compartilhado que você criou nesse exemplo.
Selecione Criar Credencial para gerar uma assinatura de acesso compartilhado e uma credencial no SQL Server Management Studio.
Selecione OK para fechar a caixa de diálogo Conectar-se a uma Assinatura da Microsoft.
Na caixa de texto Arquivo de Backup, modifique o nome do arquivo de backup (opcional).
Selecione OK para fechar a caixa de diálogo Selecionar um destino de backup.
Selecione OK para iniciar o backup.
Quando o backup for concluído com êxito, selecione OK para fechar a caixa de diálogo do SQL Server Management Studio.

Observação

No momento, não há suporte para o backup no Armazenamento de Blobs do Azure usando identidades gerenciadas.

Usando o Transact-SQL

Crie um backup completo de banco de dados executando a instrução BACKUP DATABASE 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 with_options [ ,...o ] ] ;

Opção	Descrição
database	É 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, confira Dispositivos de backup (SQL Server).
WITH with_options [ ,...o ]	Usado para especificar uma ou mais opções, o. Para obter informações sobre os fundamentos de opções, consulte a etapa 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, confira BACKUP (Transact-SQL).

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

{ COMPRESSION | NO_COMPRESSION }: somente no SQL Server 2008 (10.0.x) Enterprise e em versões posteriores, especifica se a compactação de backup é executada nesse backup, substituindo o padrão no nível do 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 a 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 for especificado, ele ficará em branco.

Por padrão, BACKUP acrescenta o backup a um conjunto de mídias existente, preservando os conjuntos de backup existentes. Para especificar isso explicitamente, use a opção NOINIT. Para obter informações sobre como acrescentar conjuntos de backup existentes, consulte Conjuntos de mídias, 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 cláusula FORMAT quando estiver usando a mídia pela primeira vez ou quando quiser substituir todos os dados existentes. Opcionalmente, atribua à nova mídia um nome e uma descrição.

Importante

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

Exemplos

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

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 NOT NULL DEFAULT GETDATE()
)
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

R. Fazer backup em um dispositivo de disco

O exemplo a seguir faz backup de banco de dados completo SQLTestDB em um disco, usando 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, anexando 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 completo do banco de dados SQLTestDB nesse dispositivo.

-- Create a logical backup device,
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC 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

Usando o PowerShell

Use o cmdlet Backup-SqlDatabase . Para indicar explicitamente 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.

Observação

Esses exemplos exigem o módulo SqlServer. Para determinar se ele está instalado, execute Get-Module -Name SqlServer. Para instalar, execute Install-Module -Name SqlServer em uma sessão de administrador do PowerShell.

Para obter mais informações, consulte SQL Server PowerShell Provider.

Importante

Se estiver abrindo uma janela do PowerShell no SQL Server Management Studio para se conectar a uma instalação do SQL Server, você poderá omitir a parte da credencial, pois sua credencial no SSMS é usada automaticamente para estabelecer a conexão entre o PowerShell e sua instância do SQL Server.

Exemplos

R. 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. Como opção, esse exemplo especifica -BackupAction Database.

Para obter os exemplos de sintaxe completa, confira Backup-SqlDatabase.

$credential = Get-Credential

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

B. Backup completo no Azure

O exemplo a seguir cria um backup completo do banco de dados <myDatabase> na instância <myServer> no Armazenamento de Blobs do 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/<myContainer>, 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.

$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

Compartilhar via

Criar um backup de banco de dados completo

Limitações e restrições

Recomendações

Segurança

Permissões

Como usar o SQL Server Management Studio.

Informações adicionais

Exemplos

R. Backup completo no disco no local padrão

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

C. Criar um backup criptografado

D. Fazer backup no Armazenamento de Blobs do Azure

Usando o Transact-SQL

Exemplos

R. Fazer backup em um dispositivo de disco

B. Fazer backup em um dispositivo de fita

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

Usando o PowerShell

Exemplos

R. Backup completo (local)

B. Backup completo no Azure

Tarefas relacionadas

Comentários

Recursos adicionais

Compartilhar via

Criar um backup de banco de dados completo

Limitações e restrições

Recomendações

Segurança

Permissões

Como usar o SQL Server Management Studio.

Informações adicionais

Exemplos

R. Backup completo no disco no local padrão

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

C. Criar um backup criptografado

D. Fazer backup no Armazenamento de Blobs do Azure

Usando o Transact-SQL

Exemplos

R. Fazer backup em um dispositivo de disco

B. Fazer backup em um dispositivo de fita

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

Usando o PowerShell

Exemplos

R. Backup completo (local)

B. Backup completo no Azure

Tarefas relacionadas

Conteúdo relacionado

Comentários

Recursos adicionais