Criar um backup de banco de dados completo
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
BACKUPnã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.
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.
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
mastere clique em Nova Consulta para abrir uma janela de consulta com uma conexão com seu banco de dadosSQLTestDB.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.
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.
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' | @textvariable }: 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
Como usar 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
Tarefas relacionadas
- Fazer backup de um banco de dados (SQL Server)
- Criar um backup diferencial de banco de dados (SQL Server)
- Restore a Database Backup Using SSMS
- Restaurar um backup de banco de dados no modelo de recuperação simples (Transact-SQL)
- Restaurar um banco de dados até o ponto de falha no modelo de recuperação completa (Transact-SQL)
- Restaurar um banco de dados em um novo local (SQL Server)
- Usar o Assistente de Plano de Manutenção
Conteúdo relacionado
- Solução de problemas de operações de backup e restauração do SQL Server
- Backup Overview (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)
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de