Fazer backup e restaurar bancos de dados do SQL ServerBack Up and Restore of SQL Server Databases

APLICA-SE A: simSQL Server nãoBanco de Dados SQL do Azure nãoSQL Data Warehouse do Azure nãoParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Este artigo descreve os benefícios do backup dos bancos de dados do SQL ServerSQL Server bancos de dados, as condições de backup e restauração básicas, apresenta estratégias de backup e restauração para SQL ServerSQL Server e considerações de segurança sobre backup e restauração do SQL ServerSQL Server .This article describes the benefits of backing up SQL ServerSQL Server databases, basic backup and restore terms, and introduces backup and restore strategies for SQL ServerSQL Server and security considerations for SQL ServerSQL Server backup and restore.

Procurando instruções passo a passo?Looking for step by step instructions? Este tópico não fornece as etapas específicas de como fazer um backup.This topic does not provide any specific steps for how to do a back up! Se desejar ir diretamente para as etapas de como fazer backup, role a tela para baixo nesta página até a seção de links, organizada por tarefas de backup, e escolha se deseja usar o SSMS ou T-SQL.If you want to get right to actually backing up, scroll down this page to the links section, organized by backup tasks and whether you want to use SSMS or T-SQL.

O componente de backup e restauração do SQL Server oferece uma proteção essencial para dados críticos armazenados em bancos de dados do SQL ServerSQL Server .The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL ServerSQL Server databases. Para minimizar o risco de perda de dados catastrófica, você precisa fazer backup dos bancos de dados para preservar as modificações feitas nos dados regularmente.To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. Uma estratégia de backup e restauração bem-planejada ajuda a proteger bancos de dados contra perda de dados causada por várias falhas.A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Teste sua estratégia restaurando um conjunto de backups e recuperando depois seu banco de dados para se preparar para responder com eficiência a um desastre.Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.

Além do armazenamento local para guardar os backups, o SQL Server também oferece suporte ao backup e à restauração no serviço de armazenamento de Blob do Windows Azure.In addition to local storage for storing the backups, SQL Server also supports backup to and restore from the Windows Azure Blob Storage Service. Para obter mais informações, veja Backup e restauração do SQL Server com o Serviço de Armazenamento de Blobs do Microsoft Azure.For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service. Para os arquivos de banco de dados armazenados usando o serviço de armazenamento de Blob do Microsoft Azure, o SQL Server 2016 (13.x)SQL Server 2016 (13.x) fornece a opção de usar instantâneos do Azure para backups quase imediatos e restaurações mais rápidas.For database files stored using the Microsoft Azure Blob storage service, SQL Server 2016 (13.x)SQL Server 2016 (13.x) provides the option to use Azure snapshots for nearly instantaneous backups and faster restores. Para obter mais informações, consulte Backups de instantâneo de arquivo para arquivos de banco de dados no Azure.For more information, see File-Snapshot Backups for Database Files in Azure.

Por que fazer backup?Why back up?

  • O backup dos bancos de dados do SQL ServerSQL Server , a execução de procedimentos de restauração de teste nos backups e o armazenamento de cópias de backups em um local externo seguro evita a perda de dados potencialmente catastrófica.Backing up your SQL ServerSQL Server databases, running test restores procedures on your backups, and storing copies of backups in a safe, off-site location protects you from potentially catastrophic data loss. Realizar backup é a única maneira de proteger seus dados.Backing up is the only way to protect your data.

    Com backups válidos de um banco de dados, você pode recuperar seus dados de muitas falhas, como:With valid backups of a database, you can recover your data from many failures, such as:

    • Falha de mídia.Media failure.
    • Por exemplo, erros de usuário, que removem uma tabela por engano.User errors, for example, dropping a table by mistake.
    • Por exemplo, problemas de hardware, uma unidade de disco danificada ou perda permanente de um servidor.Hardware failures, for example, a damaged disk drive or permanent loss of a server.
    • Desastres naturais.Natural disasters. Ao usar o Backup do SQL Server para serviço de armazenamento de Blob do Windows Azure, é possível criar um backup externo em uma região diferente daquela do seu local, para usar no caso de um desastre natural afetar seu local.By using SQL Server Backup to Windows Azure Blob storage service, you can create an off-site backup in a different region than your on-premises location, to use in the event of a natural disaster affecting your on-premises location.
  • Além disso, os backups de um banco de dados são úteis para fins administrativos rotineiros, como copiar um banco de dados de um servidor para outro, configurar o espelhamento do banco de dados ou Grupos de disponibilidade AlwaysOnAlways On availability groups e fazer arquivamento.Additionally, backups of a database are useful for routine administrative purposes, such as copying a database from one server to another, setting up Grupos de disponibilidade AlwaysOnAlways On availability groups or database mirroring, and archiving.

Glossário de termos de backupGlossary of backup terms

fazer backup [verbo]back up [verb]
O processo de criação de um backup [substantivo] copiando registros de dados de um SQL ServerSQL Server banco de dados ou registros de log do seu log de transações.The process of creating a backup [noun] by copying data records from a SQL ServerSQL Server database, or log records from its transaction log.

backup [substantivo]backup [noun]
Uma cópia dos dados que podem ser usados para restaurar e recuperar os dados após uma falha.A copy of data that can be used to restore and recover the data after a failure. Os backups de um banco de dados também podem ser usados para restaurar uma cópia do banco de dados em um novo local.Backups of a database can also be used to restore a copy the database to a new location.

dispositivo debackup backup device
Um disco ou dispositivo de fita no qual os backups do SQL Server serão gravados e nos quais eles poderão ser restaurados.A disk or tape device to which SQL Server backups are written and from which they can be restored. Os backups do SQL Server também podem ser gravados em um serviço de armazenamento do Blob do Microsoft Azure. O formato de URL é usado para especificar o destino e o nome do arquivo de backup.SQL Server backups can also be written to a Windows Azure Blob storage service, and URL format is used to specify the destination and the name of the backup file.. Para obter mais informações, veja Backup e restauração do SQL Server com o Serviço de Armazenamento de Blobs do Microsoft Azure.For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.

mídia de backupbackup media
Uma ou mais fitas ou arquivos de disco nos quais um ou mais backups foram gravados.One or more tapes or disk files to which one or more backups have been written.

backup de dadosdata backup
Um backup de dados em um banco de dados completo (um backup de banco de dados), um banco de dados parcial (um backup parcial) ou um conjunto de arquivos de dados ou grupos de arquivos (um backup de arquivo).A backup of data in a complete database (a database backup), a partial database (a partial backup), or a set of data files or filegroups (a file backup).

backup de banco de dadosdatabase backup
Um backup de um banco de dados.A backup of a database. Os backups completos de banco de dados representam todo o banco de dados no momento em que o backup é concluído.Full database backups represent the whole database at the time the backup finished. Os backups de banco de dados diferenciais contêm somente alterações feitas no banco de dados desde seu backup completo de banco de dados mais recente.Differential database backups contain only changes made to the database since its most recent full database backup.

backup diferencialdifferential backup
Um backup de dados que se baseia no backup completo mais recente de um banco de dados completo ou parcial ou um conjunto de arquivos de dados ou grupos de arquivos (a base diferencial) que contém somente os dados alterados desde essa base.A data backup that is based on the latest full backup of a complete or partial database or a set of data files or filegroups (the differential base) and that contains only the data that has changed since that base.

backup completofull backup
Um backup de dados que contém todos os dados em um banco de dados ou em um conjunto de grupos de arquivos ou arquivos, além de log suficiente para permitir a recuperação desses dados.A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

backup de loglog backup
Um backup de logs de transações que inclui todos os registros de log dos quais não foi feito backup em um backup de log anterior.A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (modelo de recuperação completa)(full recovery model)

recuperaçãorecover
Para retornar um banco de dados a um estado estável e consistente.To return a database to a stable and consistent state.

recuperaçãorecovery
Uma fase de inicialização de banco de dados ou de restauração com recuperação que coloca o banco de dados em um estado de transação consistente.A phase of database startup or of a restore with recovery that brings the database into a transaction-consistent state.

modelo de recuperaçãorecovery model
Uma propriedade de banco de dados que controla a manutenção do log de transações em um banco de dados.A database property that controls transaction log maintenance on a database. Existem três modelos de recuperação: simples, completo e bulk-logged.Three recovery models exist: simple, full, and bulk-logged. O modelo de recuperação de banco de dados determina seus requisitos de backup e de restauração.The recovery model of database determines its backup and restore requirements.

restaurarrestore
Um processo multifase que copia todos os dados e páginas de log de um backup do SQL ServerSQL Server para um banco de dados especificado e, em seguida, efetua roll forward de todas as transações registradas no backup, aplicando as alterações registradas para avançar os dados no tempo.A multi-phase process that copies all the data and log pages from a specified SQL ServerSQL Server backup to a specified database, and then rolls forward all the transactions that are logged in the backup by applying logged changes to bring the data forward in time.

Estratégias de backup e restauraçãoBackup and restore strategies

O backup e a restauração dos dados devem ser personalizados em um ambiente específico e devem funcionar com os recursos disponíveis.Backing up and restoring data must be customized to a particular environment and must work with the available resources. Portanto, um uso confiável de backup e restauração para recuperação requer uma estratégia de backup e restauração.Therefore, a reliable use of backup and restore for recovery requires a backup and restore strategy. Uma estratégia de backup e restauração bem-planejada maximiza a disponibilidade dos dados e minimiza a perda de dados, considerando, ao mesmo tempo, seus requisitos empresariais específicos.A well-designed backup and restore strategy maximizes data availability and minimizes data loss, while considering your particular business requirements.

Importante

Coloque o banco de dados e os backups em dispositivos separados.Place the database and backups on separate devices. Caso contrário, se o dispositivo que contém o banco de dados falhar, seus backups ficarão indisponíveis.Otherwise, if the device containing the database fails, your backups will be unavailable. Colocar os dados e os backups em dispositivos separados também aprimora o desempenho de E/S tanto para gravar backups quanto para o uso em produção do banco de dados.**Placing the data and backups on separate devices also enhances the I/O performance for both writing backups and the production use of the database.**

Uma estratégia de backup e restauração contém uma parte de backup e uma parte de restauração.A backup and restore strategy contains a backup portion and a restore portion. A parte de backup da estratégia define o tipo e a frequência dos backups, a natureza e velocidade do hardware exigido para eles, como os backups serão testados e em que local e como a mídia de backup deve ser armazenada (incluindo considerações de segurança).The backup part of the strategy defines the type and frequency of backups, the nature, and speed of the hardware that is required for them, how backups are to be tested, and where and how backup media is to be stored (including security considerations). A parte de restauração da estratégia define quem é responsável pela execução da restauração e como a restauração deve ser executada para atender às metas de disponibilidade do banco de dados e minimizar perda de dados.The restore part of the strategy defines who is responsible for performing restores and how restores should be performed to meet your goals for availability of the database and for minimizing data loss. Recomendamos que você documente seus procedimentos de backup e restauração e mantenha uma cópia da documentação em seu livro de execuções.We recommend that you document your backup and restore procedures and keep a copy of the documentation in your run book.

O design de uma estratégia de backup e restauração eficaz requer planejamento, implementação e teste cuidadosos.Designing an effective backup and restore strategy requires careful planning, implementation, and testing. O teste é obrigatório.Testing is required. Não existirá uma estratégia de backup até que você tenha restaurado com êxito os backups em todas as combinações incluídas na estratégia de restauração.You do not have a backup strategy until you have successfully restored backups in all the combinations that are included in your restore strategy. Você deve considerar uma variedade de fatores.You must consider a variety of factors. Entre elas estão as seguintes:These include the following:

  • As metas de produção de sua organização para os bancos de dados, especialmente os requisitos para disponibilidade e proteção contra perda de dados.The production goals of your organization for the databases, especially the requirements for availability and protection of data from loss.

  • A natureza de cada um dos seus bancos de dados: o tamanho, os padrões de uso, a natureza de seu conteúdo, os requisitos dos dados, e assim por diante.The nature of each of your databases: its size, its usage patterns, the nature of its content, the requirements for its data, and so on.

  • Restrições de recursos, como hardware, pessoal, espaço para armazenagem de mídia de backup, a segurança física da mídia armazenada, e assim por diante.Constraints on resources, such as: hardware, personnel, space for storing backup media, the physical security of the stored media, and so on.

Impacto do modelo de recuperação no backup e na restauraçãoImpact of the recovery model on backup and restore

As operações de backup e restauração ocorrem dentro do contexto de um modelo de recuperação.Backup and restore operations occur within the context of a recovery model. Um modelo de recuperação é uma propriedade de banco de dados que controla a forma de gerenciamento do log de transações.A recovery model is a database property that controls how the transaction log is managed. Além disso, o modelo de recuperação de um banco de dados determina para quais tipos de backups e cenários de restauração o banco de dados oferece suporte.Also, the recovery model of a database determines what types of backups and what restore scenarios are supported for the database. Geralmente, um banco de dados usa o modelo de recuperação simples ou o modelo de recuperação completa.Typically a database uses either the simple recovery model or the full recovery model. O modelo de recuperação completa pode ser suplementado alternando para o modelo de recuperação bulk-logged antes das operações em massa.The full recovery model can be supplemented by switching to the bulk-logged recovery model before bulk operations. Para obter uma introdução a esses modelos de recuperação e como eles afetam o gerenciamento do log de transações, consulte O log de transação (SQL Server)For an introduction to these recovery models and how they affect transaction log management, see The Transaction Log (SQL Server)

A melhor escolha do modelo de recuperação para o banco de dados depende de seus requisitos empresariais.The best choice of recovery model for the database depends on your business requirements. Para evitar gerenciamento de log de transações e simplificar o backup e a restauração, use o modelo de recuperação simples.To avoid transaction log management and simplify backup and restore, use the simple recovery model. Para minimizar exposição à perda de trabalho, às custas de uma sobrecarga administrativa, use o modelo de recuperação completa.To minimize work-loss exposure, at the cost of administrative overhead, use the full recovery model. Para obter informações sobre o efeito dos modelos de recuperação sobre o backup e a restauração, consulte Visão geral do backup (SQL Server).For information about the effect of recovery models on backup and restore, see Backup Overview (SQL Server).

Planejar a estratégia de backupDesign your backup strategy

Depois de selecionar um modelo de recuperação que satisfaça seus requisitos empresariais para um banco de dados específico, você precisa planejar e implementar uma estratégia de backup correspondente.After you have selected a recovery model that meets your business requirements for a specific database, you have to plan and implement a corresponding backup strategy. A melhor estratégia de backup depende de uma série de fatores, dos quais os seguintes são especialmente significativos:The optimal backup strategy depends on a variety of factors, of which the following are especially significant:

  • Quantas horas ao dia os aplicativos precisam acessar o banco de dados?How many hours a day do applications have to access the database?

    Se houver um período de pouca atividade previsível, recomendamos que você agende backups de banco de dados completos para aquele período.If there is a predictable off-peak period, we recommend that you schedule full database backups for that period.

  • Com que frequência as alterações e atualizações deverão ocorrer?How frequently are changes and updates likely to occur?

    Se as alterações forem frequentes, considere o seguinte:If changes are frequent, consider the following:

    • No modelo de recuperação simples, agende backups diferenciais entre os backups de banco de dados completos.Under the simple recovery model, consider scheduling differential backups between full database backups. Um backup diferencial captura só as alterações desde o último backup completo do banco de dados.A differential backup captures only the changes since the last full database backup.

    • No modelo de recuperação completa, você deve agendar backups de log frequentes.Under the full recovery model, you should schedule frequent log backups. O agendamento de backups diferenciais entre backups completos pode reduzir o tempo de restauração reduzindo o número de backups de log a serem restaurados após a restauração dos dados.Scheduling differential backups between full backups can reduce restore time by reducing the number of log backups you have to restore after restoring the data.

  • As alterações ocorrem geralmente em uma pequena parte do banco de dados ou em uma grande parte do banco de dados?Are changes likely to occur in only a small part of the database or in a large part of the database?

    Para um banco de dados grande no qual mudanças estão concentradas em uma parte dos arquivos ou grupos de arquivos, backups parciais e backups de arquivo podem ser úteis.For a large database in which changes are concentrated in a part of the files or filegroups, partial backups and or file backups can be useful. Para obter mais informações, consulte Backups parciais (SQL Server) e Backups completos de arquivo (SQL Server).For more information, see Partial Backups (SQL Server) and Full File Backups (SQL Server).

  • Quanto espaço em disco é necessário para um backup completo de banco de dados?How much disk space will a full database backup require?

Estimar o tamanho de um backup de banco de dados completoEstimate the size of a full database backup

Antes de implementar uma estratégia de backup e restauração, calcule quanto espaço em disco um backup de banco de dados completo usará.Before you implement a backup and restore strategy, you should estimate how much disk space a full database backup will use. A operação de backup copia os dados no banco de dados para o arquivo de backup.The backup operation copies the data in the database to the backup file. O backup contém só os dados reais no banco de dados e não qualquer espaço não utilizado.The backup contains only the actual data in the database and not any unused space. Portanto, o backup é geralmente menor do que o próprio banco de dados.Therefore, the backup is usually smaller than the database itself. Você pode estimar o tamanho de um backup de banco de dados completo usando o procedimento armazenado do sistema sp_spaceused.You can estimate the size of a full database backup by using the sp_spaceused system stored procedure. Para obter mais informações, veja sp_spaceused (Transact-SQL).For more information, see sp_spaceused (Transact-SQL).

Agendar backupsSchedule backups

A execução do backup tem um efeito mínimo sobre as transações em andamento; portanto, as operações de backup podem ser realizadas durante a operação regular.Performing a backup operation has minimal effect on transactions that are running; therefore, backup operations can be run during regular operations. Você pode executar um backup do SQL ServerSQL Server com um efeito mínimo sobre as cargas de trabalho de produção.You can perform a SQL ServerSQL Server backup with minimal effect on production workloads.

Para obter informações sobre restrições de simultaneidade durante o backup, consulte Visão geral do backup (SQL Server).For information about concurrency restrictions during backup, see Backup Overview (SQL Server).

Depois de decidir os tipos de backups necessários e a frequência de execução de cada tipo, recomendamos que você agende backups regulares como parte de um plano de manutenção de banco de dados para o banco de dados.After you decide what types of backups you require and how frequently you have to perform each type, we recommend that you schedule regular backups as part of a database maintenance plan for the database. Para obter informações sobre planos de manutenção e como criá-los para fazer backups de banco de dados e backups de log, consulte Use the Maintenance Plan Wizard.For information about maintenance plans and how to create them for database backups and log backups, see Use the Maintenance Plan Wizard.

Teste seus backups.Test your backups!

Não existirá uma estratégia de restauração até que você tenha testado seus backups.You do not have a restore strategy until you have tested your backups. É muito importante testar sua estratégia de backup completamente para cada um dos bancos de dados, restaurando uma cópia do banco de dados em um sistema de teste.It is very important to thoroughly test your backup strategy for each of your databases by restoring a copy of the database onto a test system. É necessário testar a restauração de cada tipo de backup que você pretende usar.You must test restoring every type of backup that you intend to use.

Recomendamos que você mantenha um manual de operações para cada banco de dados.We recommend that you maintain an operations manual for each database. Esse manual operacional deve documentar o local dos backups, os nomes do dispositivo de backup (se houver) e o tempo necessário para restaurar os backups de teste.This operations manual should document the location of the backups, backup device names (if any), and the amount of time that is required to restore the test backups.

Monitorar o progresso com xEventMonitor progress with xEvent

Operações de backup e restauração podem levar um tempo considerável devido ao tamanho de um banco de dados e à complexidade das operações envolvidas.Backup and restore operations can take a considerable amount of time due to the size of a database and the complexity of the operations involved. Quando ocorrem problemas com a operação, você pode usar o evento estendido backup_restore_progress_trace para monitorar o progresso em tempo real.When issues arise with either operation, you can use the backup_restore_progress_trace extended event to monitor progress live. Para obter mais informações sobre eventos estendidos, veja eventos estendidos.For more information about extended events, see extended events.

Aviso

Usar o evento estendido backup_restore_progress_trace pode causar um problema de desempenho e consumir uma quantidade significativa de espaço em disco.Using the backup_restore_progress_trace extended event can cause a performance issue and consume a significant amount of disk space. Use por curtos períodos de tempo, tenha cuidado e teste bem antes de implementar na produção.Use for short periods of time, exercise caution, and test thoroughly before implementing in production.

-- Create the backup_restore_progress_trace extended event esssion
CREATE EVENT SESSION [BackupRestoreTrace] ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file(SET filename=N'BackupRestoreTrace')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

-- Start the event session  
ALTER EVENT SESSION [BackupRestoreTrace]  
ON SERVER  
STATE = start;  
GO  

-- Stop the event session  
ALTER EVENT SESSION [BackupRestoreTrace]  
ON SERVER  
STATE = stop;  
GO  

Exemplo de saída do evento estendidoSample output from extended event

Exemplo de saída xevent de backup Exemplo de saída xevent de restauraçãoExample of back up xevent output Example of restore xevent output

Mais informações sobre tarefas de backupMore about backup tasks

Trabalhando com dispositivos e mídias de backupWorking with backup devices and backup media

Criando backupsCreating backups

Observação:Note! Para backups parciais ou somente cópia, use a instrução Transact-SQLTransact-SQLBACKUP com a opção PARTIAL ou COPY_ONLY, respectivamente.For partial or copy-only backups, you must use the Transact-SQLTransact-SQLBACKUP statement with the PARTIAL or COPY_ONLY option, respectively.

Usando SSMSUsing SSMS

Usando o T-SQLUsing T-SQL

Restaurar backups de dadosRestore data backups

Usando SSMSUsing SSMS

Usando o T-SQLUsing T-SQL

Restaurar logs de transações (Modelo de Recuperação Completa)Restore transaction logs (Full Recovery Model)

Usando SSMSUsing SSMS

Usando o T-SQLUsing T-SQL

Mais informações e recursosMore information and resources

Visão geral do backup (SQL Server) Backup Overview (SQL Server)
Visão geral de restauração e recuperação (SQL Server) Restore and Recovery Overview (SQL Server)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
Backup e restauração de bancos de dados do Analysis Services Backup and Restore of Analysis Services Databases
Fazer backup e restaurar índices e catálogos de texto completo Back Up and Restore Full-Text Catalogs and Indexes
Fazer backup e restaurar bancos de dados replicados Back Up and Restore Replicated Databases
O log de transações (SQL Server) The Transaction Log (SQL Server)
Modelos de recuperação (SQL Server) Recovery Models (SQL Server)
Conjuntos de mídias, famílias de mídia e conjuntos de backup (SQL Server)Media Sets, Media Families, and Backup Sets (SQL Server)