BACKUP (Transact-SQL)

Artigo
12/28/2023

Faz o backup de um Banco de Dados SQL.

Selecionar um produto

Na linha a seguir, selecione o nome do produto em que você tem interesse e somente as informações do produto serão exibidas.

Para obter mais informações sobre as convenções de sintaxe, confira Convenções de sintaxe Transact-SQL.

* SQL Server *

Instância Gerenciada de SQL

Analytics Platform
System (PDW)

SQL Server

Faz backup de um banco de dados completo do SQL Server para criar um backup de banco de dados ou um ou mais arquivos ou grupos de arquivos do banco de dados para criar um backup de arquivo (BACKUP DATABASE). Além disso, no modelo de recuperação completa ou no modelo de recuperação bulk-logged, faz o backup do log de transações do banco de dados para criar um backup de log (BACKUP LOG).

Sintaxe

--Back up a whole database
BACKUP DATABASE { database_name | @database_name_var }
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { DIFFERENTIAL
           | <general_WITH_options> [ ,...n ] } ]
[;]

--Back up specific files or filegroups
BACKUP DATABASE { database_name | @database_name_var }
 <file_or_filegroup> [ ,...n ]
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]

--Create a partial backup
BACKUP DATABASE { database_name | @database_name_var }
 READ_WRITE_FILEGROUPS [ , <read_only_filegroup> [ ,...n ] ]
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]

--Back up the transaction log (full and bulk-logged recovery models)
BACKUP LOG
  { database_name | @database_name_var }
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { <general_WITH_options> | <log_specific_options> } [ ,...n ] ]
[;]

--Back up all the databases on an instance of SQL Server (a server)

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP ON
[;]

BACKUP SERVER
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { METADATA_ONLY
           | <general_WITH_options> [ ,...n ] } ]
[;]

--Back up a group of databases
ALTER DATABASE <database>
SET SUSPEND_FOR_SNAPSHOT_BACKUP ON

ALTER DATABASE <...>
SET SUSPEND_FOR_SNAPSHOT_BACKUP ON
...

BACKUP GROUP {<database> [,... ]}
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { METADATA_ONLY
           | <general_WITH_options> [ ,...n ] } ]
[;]

<backup_device>::=
 {
  { logical_device_name | @logical_device_name_var }
 | {   DISK
     | TAPE
     | URL } =
     { 'physical_device_name' | @physical_device_name_var | 'NUL' }
 }

<MIRROR TO clause>::=
 MIRROR TO <backup_device> [ ,...n ]

<file_or_filegroup>::=
 {
   FILE = { logical_file_name | @logical_file_name_var }
 | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
 }

<read_only_filegroup>::=
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }

<general_WITH_options> [ ,...n ]::=
--Backup Set Options
   COPY_ONLY
 | [ COMPRESSION [ ALGORITHM = { MS_XPRESS | accelerator_algorithm } ] | NO_COMPRESSION ]
 | DESCRIPTION = { 'text' | @text_variable }
 | NAME = { backup_set_name | @backup_set_name_var }
 | CREDENTIAL
 | ENCRYPTION
 | FILE_SNAPSHOT
 | { EXPIREDATE = { 'date' | @date_var }
        | RETAINDAYS = { days | @days_var } }
 | { METADATA_ONLY | SNAPSHOT }

--Media Set Options
   { NOINIT | INIT }
 | { NOSKIP | SKIP }
 | { NOFORMAT | FORMAT }
 | MEDIADESCRIPTION = { 'text' | @text_variable }
 | MEDIANAME = { media_name | @media_name_variable }
 | BLOCKSIZE = { blocksize | @blocksize_variable }

--Data Transfer Options
   BUFFERCOUNT = { buffercount | @buffercount_variable }
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

--Error Management Options
   { NO_CHECKSUM | CHECKSUM }
 | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

--Compatibility Options
   RESTART

--Monitoring Options
   STATS [ = percentage ]

--Tape Options
   { REWIND | NOREWIND }
 | { UNLOAD | NOUNLOAD }

--Encryption Options
 ENCRYPTION (ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } , encryptor_options ) <encryptor_options> ::=
   SERVER CERTIFICATE = Encryptor_Name | SERVER ASYMMETRIC KEY = Encryptor_Name

<log_specific_options> [ ,...n ]::=
--Log-specific Options
   { NORECOVERY | STANDBY = undo_file_name }
 | NO_TRUNCATE

Argumentos

DATABASE

Especifica um backup completo do banco de dados. Se uma lista de arquivos e grupos de arquivos estiver especificada, será feito o backup apenas desses arquivos e grupos de arquivos. Durante um backup completo ou diferencial do banco de dados, o SQL Server faz backup suficiente do log de transações para produzir um banco de dados consistente quando o backup é restaurado.

Ao restaurar um backup criado por BACKUP DATABASE (um backup de dados), o backup inteiro é restaurado. Somente um backup de log pode ser restaurado em uma transação ou momento específico dentro do backup.

Observação

Apenas um backup completo do banco de dados pode ser executado no banco de dados master.

LOG

Especifica um backup apenas do log de transações. O backup do log é feito a partir do último backup do log executado com êxito até o final do log atual. Para poder criar o primeiro backup de log, você deve criar um backup completo.

É possível restaurar um backup de log em uma transação ou momento específico dentro do backup especificando WITH STOPAT, STOPATMARK ou STOPBEFOREMARK na instrução RESTORE LOG.

Observação

Após um backup de log típico, alguns registros do log de transações se tornam inativos, a menos que você especifique WITH NO_TRUNCATE ou COPY_ONLY. O log é truncado depois que todos os registros contidos em um ou mais arquivos de log virtual se tornam ativos. Se o log não estiver sendo truncado após backups de log de rotina, algo pode estar atrasando o truncamento do log. Para obter mais informações, consulte Fatores que podem atrasar o truncamento de log.

GROUP (<database>,...n)

Introduzido no SQL Server 2022 (16.x).

Fazer backup de um grupo de bancos de dados. Usa o backup de instantâneo. Requer WITH METADATA_ONLY. Confira Criar um backup de instantâneo do Transact-SQL.

SERVER

Introduzido no SQL Server 2022 (16.x).

Fazer backup de todos os bancos de dados em uma instância do SQL Server. Usa o backup de instantâneo. Requer WITH METADATA_ONLY. Confira Criar um backup de instantâneo do Transact-SQL.

METADATA_ONLY

Introduzido no SQL Server 2022 (16.x).

Necessário para o backup de instantâneo. BACKUP SERVER ou BACKUP GROUP.... Confira Criar um backup de instantâneo do Transact-SQL.

METADATA_ONLY é sinônimo de SNAPSHOT. A VDI (interface de dispositivo virtual) usa SNAPSHOT. Para saber mais sobre a VDI, confira Referência da VDI (interface de dispositivo virtual).

{ database_name | @database_name_var }

É o banco de dados do qual é feito o backup do log de transações, do banco de dados parcial ou do banco de dados completo. Se for fornecido como uma variável (@database_name_var), esse nome poderá ser especificado como uma constante de cadeia de caracteres (@database_name_var=database name) ou como uma variável de tipo de dados de cadeia de caracteres, exceto para os tipos de dados ntext ou text.

Observação

O backup do banco de dados espelho em uma parceria de espelhamento de banco de dados não pode ser feito.

<> file_or_filegroup [ ,...n ]

Usado apenas com BACKUP DATABASE, especifica um arquivo ou grupo de arquivos do banco de dados a ser incluído em um backup de arquivo, ou especifica um arquivo ou grupo de arquivos somente leitura a ser incluído em um backup parcial.

ARQUIVO = { logical_file_name | @logical_file_name_var }

É o nome lógico de um arquivo ou variável cujo valor é igual ao nome lógico de um arquivo que deve ser incluído no backup.

GRUPO DE ARQUIVOS = { logical_filegroup_name | @logical_filegroup_name_var }

É o nome lógico de um grupo de arquivos ou variável cujo valor é igual ao nome lógico de um grupo de arquivos que deve ser incluído no backup. No modelo de recuperação simples, um backup de grupo de arquivos é permitido apenas para grupos de arquivos somente leitura.

Observação

Considere usar backups de arquivo quando o tamanho do banco de dados e as exigências de desempenho tornarem um backup de banco de dados impraticável. O dispositivo NUL pode ser usado para testar o desempenho dos backups, mas não deve ser usado em ambientes de produção.

n
É um espaço reservado que indica que vários arquivos e grupos de arquivos podem ser especificados em uma lista separada por vírgulas. O número é ilimitado.

Para obter mais informações, consulte Backups completos de arquivos (SQL Server) e Fazer backup de arquivos e grupos de arquivos.

READ_WRITE_FILEGROUPS [ , GRUPO DE ARQUIVOS = { logical_filegroup_name | @logical_filegroup_name_var } [ ,...n ] ]

Especifica um backup parcial. Um backup parcial inclui todos os arquivos de leitura/gravação em um banco de dados: o grupo de arquivos primário e quaisquer grupos de arquivos secundários de leitura/gravação e também quaisquer arquivos ou grupos de arquivos somente leitura especificados.

READ_WRITE_FILEGROUPS

Especifica que o backup de todos os grupos de arquivos de leitura/gravação seja feito no backup parcial. Se o banco de dados for somente leitura, READ_WRITE_FILEGROUPS incluirá apenas o grupo de arquivos primário.

Importante

A listagem explícita de grupos de arquivos de leitura/gravação usando FILEGROUP em vez de READ_WRITE_FILEGROUPS cria um backup de arquivo.

GRUPO DE ARQUIVOS = { logical_filegroup_name | @logical_filegroup_name_var }

É o nome lógico de um grupo de arquivos somente leitura ou de uma variável cujo valor é igual ao nome lógico de um grupo de arquivos somente leitura que deve ser incluído no backup parcial. Para obter mais informações, confira "<file_or_filegroup>", acima neste artigo.

n
É um espaço reservado que indica que vários grupos de arquivos somente leitura podem ser especificados em uma lista separada por vírgulas.

Para obter mais informações sobre backups parciais, consulte Backups parciais.

PARA <backup_device> [ ,...n ]

Indica que o conjunto de acompanhamento de dispositivos de backup é um conjunto de mídias não espelhado ou o primeiro dos espelhos dentro de um conjunto de mídias espelhado (para o qual uma ou mais cláusulas MIRROR TO são declaradas).

<backup_device>
Especifica um dispositivo de backup lógico ou físico a ser usado para a operação de backup.

{ logical_device_name | @logical_device_name_var }

Aplica-se a: SQL Server
É o nome lógico do dispositivo de backup no qual o backup do banco de dados é feito. O nome lógico deve seguir as regras para identificadores. Se for fornecida como uma variável (@logical_device_name_var), o nome do dispositivo de backup poderá ser especificado como uma constante de cadeia de caracteres (nome do dispositivo de backup lógico de @logical_device_name_var=) ou como uma variável de qualquer tipo de dados de cadeia de caracteres, com exceção dos tipos de dados ntext ou text.

{ DISCO | FICA | URL} = { 'physical_device_name' | @physical_device_name_var | 'NUL' }

Aplica-se a: SQL Server (URL começando com o SQL Server 2012 (11.x) SP1 CU2)

Especifica um arquivo de disco, um dispositivo de fita ou uma URL.

O formato de URL é usado para criar backups no Armazenamento de Blobs do Microsoft Azure ou no armazenamento de objetos compatível com o S3. Para obter mais informações e exemplos, confira:

Backup e restauração do SQL Server com o Armazenamento de Blobs do Microsoft Azure. Para obter um tutorial, confira Tutorial: backup e restauração do SQL Server para o Armazenamento de Blobs do Microsoft Azure.
O backup e a restauração no armazenamento compatível com S3 foram introduzidos no SQL Server 2022 (16.x). Confira Backup e restauração do SQL Server com armazenamento de objetos compatível com S3. Confira também a opção Backup do SQL Server na URL para armazenamento de objetos compatível com S3.

Observação

O dispositivo de disco NUL descartará todas as informações enviadas para ele e apenas deve ser usado para teste. Isso não se destina ao uso em produção.

Importante

Do SQL Server 2012 (11.x) SP1 CU2 ao SQL Server 2014 (12.x), somente é possível fazer backup em um só dispositivo durante o backup em uma URL para o Armazenamento de Blobs do Azure. Para fazer backup em vários dispositivos ao fazer backup em uma URL, use o SQL Server 2016 (13.x) e posterior e tokens SAS (Assinatura de Acesso Compartilhado). Para obter exemplos sobre como criar uma Assinatura de Acesso Compartilhado, consulte Backup do SQL Server em uma URL e Simplificando a criação de credenciais do SQL com tokens SAS (Assinatura de Acesso Compartilhado) no Armazenamento do Azure com o PowerShell.

Um dispositivo de disco não precisa existir antes de ser especificado em uma instrução BACKUP. Se o dispositivo físico existir e a opção INIT não estiver especificada na instrução BACKUP, o backup será anexado ao dispositivo.

Observação

O dispositivo NUL descartará todas as entradas enviadas para esse arquivo; no entanto, o backup ainda marcará todas as páginas como copiadas em backup.

Para obter mais informações, consulte Dispositivos de backup.

Observação

A opção TAPE será removida em uma futura versão do SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam.

n
É um espaço reservado que indica que até 64 dispositivos de backup podem ser especificados em uma lista separada por vírgulas.

ESPELHO PARA <backup_device> [ ,...n ]

Especifica um conjunto de até três dispositivos de backup secundário, que espelharão os dispositivos de backup especificados na cláusula TO. A cláusula MIRROR TO deve especificar o mesmo número e tipo dos dispositivos de backup que a cláusula TO. O número máximo de cláusulas MIRROR TO é três.

Essa opção está disponível somente na edição Enterprise do SQL Server.

Observação

Para MIRROR TO = DISK, o BACKUP determina automaticamente o tamanho do bloco apropriado para dispositivos de disco com base no tamanho do setor do disco. Se o disco MIRROR TO for formatado com um tamanho de setor diferente que o disco especificado como o dispositivo de backup principal, o comando de backup falhará. Para espelhar backups em dispositivos com tamanhos de setor diferentes, o parâmetro BLOCKSIZE deve ser especificado e deve ser definido como o tamanho de setor mais alto entre todos os dispositivos de destino. Para saber mais sobre o tamanho de bloco, confira "BLOCKSIZE", mais adiante neste tópico.

<backup_device>
Confira "<backup_device>", anteriormente nesta seção.

n
É um espaço reservado que indica que até 64 dispositivos de backup podem ser especificados em uma lista separada por vírgulas. O número de dispositivos na cláusula MIRROR TO deve ser igual ao número de dispositivos na cláusula TO.

Para obter mais informações, confira "Famílias de mídia em conjuntos de mídias espelhados", na seção Comentários, mais adiante neste artigo.

[ next-mirror-to ]
É um espaço reservado que indica que uma única instrução BACKUP pode conter até três cláusulas MIRROR TO, além da única cláusula TO.

Opções WITH

Especifica opções a serem usadas com uma operação de backup.

CREDENTIAL

Aplica-se a: SQL Server (começando com o SQL Server 2012 (11.x) SP1 CU2).

Usado somente ao criar um backup no Armazenamento de Blobs do Azure.

FILE_SNAPSHOT

Aplica-se a: SQL Server (começando com SQL Server 2016 (13.x)).

Usado para criar um instantâneo dos arquivos de banco de dados do Azure quando todos os arquivos de banco de dados do SQL Server são armazenados usando o Armazenamento de Blobs do Azure. Para obter mais informações, consulte Arquivos de dados do SQL Server no Microsoft Azure. Backup de Instantâneo do SQL Server usa instantâneos do Azure dos arquivos de banco de dados (arquivos de log e de dados) em um estado consistente. Um conjunto consistente de instantâneos do Azure compõem um backup e são registrados no arquivo de backup. A única diferença entre o BACKUP DATABASE TO URL WITH FILE_SNAPSHOT e o BACKUP LOG TO URL WITH FILE_SNAPSHOT é que o último também trunca o log de transações, ao contrário do primeiro. Com o Backup do Instantâneo SQL Server, após o backup completo inicial exigido pelo SQL Server para estabelecer a cadeia de backup, somente um único backup de log de transações é necessário para restaurar um banco de dados para o ponto no tempo do backup de log de transações. Além disso, apenas dois backups de log de transações são necessários para restaurar um banco de dados para um ponto no tempo entre a hora dos dois backups de log de transações.

DIFFERENTIAL

Usado apenas com BACKUP DATABASE, especifica que o backup de banco de dados ou de arquivo deve consistir apenas das partes do banco de dados ou arquivo alterado desde o último backup completo. Um backup diferencial normalmente usa menos espaço do que um backup completo. Use essa opção para que todos os backups de log individuais executados desde o último backup não precisem ser aplicados.

Observação

Por padrão, BACKUP DATABASE cria um backup completo.

Para obter mais informações, veja Backups diferenciais.

ENCRYPTION

Usado para especificar a criptografia de um backup. Você pode especificar um algoritmo de criptografia para criptografar o backup ou especificar NO_ENCRYPTION para que o backup não seja criptografado. A criptografia é a prática recomendada para ajudar a proteger arquivos de backup. A lista de algoritmos que você pode especificar é:

AES_128
AES_192
AES_256
TRIPLE_DES_3KEY
NO_ENCRYPTION

Se você decidir criptografar o backup, também precisará especificar o criptografador usando as opções do criptografador:

SERVER CERTIFICATE = Encryptor_Name
SERVER ASYMMETRIC KEY = Encryptor_Name

O SERVER CERTIFICATE e o SERVER ASYMMETRIC KEY são um certificado e uma chave assimétrica criados no banco de dados master. Para saber mais, confira CREATE CERTIFICATE e CREATE ASYMMETRIC KEY respectivamente.

Aviso

Quando a criptografia é usada em conjunto com o argumento FILE_SNAPSHOT, o próprio arquivo de metadados é criptografado com o algoritmo de criptografia especificado e o sistema verifica se a TDE (Transparent Data Encryption) foi concluída para o banco de dados. Nenhuma criptografia adicional ocorre para os dados em si. O backup falhará se o banco de dados não tiver sido criptografado ou se a criptografia não tiver sido concluída antes da emissão da instrução de backup.

Opções de conjunto de backup

Essas opções funcionam no conjunto de backup criado por esta operação de backup.

Observação

Para especificar um conjunto de backup para uma operação de restauração, use a opção FILE = <backup_set_file_number>. Para obter mais informações sobre como especificar um conjunto de backup, consulte "Especificando um conjunto de backup" em Argumentos RESTORE.

COPY_ONLY

Especifica que o backup é um backup somente cópia, o que não afeta a sequência normal de backups. Um backup somente cópia é criado independentemente de seus backups convencionais agendados regularmente. Um backup somente cópia não afeta o backup global e restaura procedimentos do banco de dados.

Backups somente cópia devem ser usados em situações em que um backup é feito com um objetivo especial, como fazer backup de log antes de uma restauração de arquivo online. Normalmente, um backup de log somente cópia é usado uma vez e excluído em seguida.

Quando usado com BACKUP DATABASE, a opção COPY_ONLY cria um backup completo que não pode servir como uma base diferencial. O bitmap diferencial não é atualizado e backups diferenciais se comportam como se o backup somente cópia não existisse. Backups diferenciais subseqüentes usam o backup completo convencional mais recente como base.

Importante

Se DIFFERENTIAL e COPY_ONLY são usados juntos, COPY_ONLY é ignorado e um backup diferencial é criado.
Quando usada com BACKUP LOG, a opção COPY_ONLY cria um backup de log somente cópia, que não trunca o log de transações. O backup de log somente cópia não tem nenhum efeito na cadeia de logs e outros backups de log se comportam como se o backup somente cópia não existisse.

Para obter mais informações, veja Backups somente cópia.

[ COMPRESSION [ ALGORITHM = ( { MS_XPRESS | accelerator_algorithm } ) ] | NO_COMPRESSION ]

Especifica se a compactação de backup é executada neste backup, substituindo o padrão de nível de servidor.

Na instalação, o comportamento padrão é sem compactação de backup. No entanto, esse padrão pode ser alterado com a definição da opção backup compression default de configuração do servidor. Para obter informações sobre como exibir o valor atual dessa opção, consulte Exibir ou alterar propriedades do servidor.

Para obter informações sobre como usar a compactação de backup com bancos de dados habilitados para TDE (Transparent Data Encryption), consulte a seção Comentários.

COMPRESSION
Habilita explicitamente a compactação de backup.

NO_COMPRESSION
Desabilita explicitamente a compactação de backup.

O SQL Server 2022 (16.x) introduz o ALGORITHM, que identifica um algoritmo de compressão da operação. O padrão é MS_XPRESS. Se você configurou o Descarregamento e a aceleração integrados, use um acelerador fornecido pela solução. Por exemplo, se você configurou a Intel® QuickAssist Technology (QAT) para SQL Server, o exemplo a seguir conclui o backup com a solução aceleradora, com a biblioteca QATzip usando QZ_DEFLATE com o nível de compactação 1.

BACKUP DATABASE <database_name> TO DISK WITH COMPRESSION (ALGORITHM = QAT_DEFLATE)

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.

NOME = { backup_set_name | @backup_set_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.

{ EXPIRADO = 'data' | DIAS DE RETENÇÃO = dias }

Especifica quando o conjunto de backup desse backup pode ser substituído. Se essas duas opções forem usadas, RETAINDAYS terá precedência sobre EXPIREDATE.

Se nenhuma opção for especificada, a data de expiração será determinada pela definição da configuração de media retention. Para obter mais informações, veja Opções de configuração do servidor.

Importante

Essas opções apenas impedem que o SQL Server substitua um arquivo. Fitas podem ser apagadas por outros métodos e os arquivos de disco podem ser excluídos pelo sistema operacional. Para obter mais informações sobre verificação de validade, consulte SKIP e FORMAT neste tópico.

EXPIREDATE = { 'date' | @date_var }
Especifica quando o conjunto de backup vence e pode ser substituído. Se for fornecida como uma variável (@date_var), essa data deverá seguir o formato de datetime do sistema configurado e ser especificado como um dos seguintes:

Uma constante de cadeia de caracteres (@date_var = date)
Uma variável do tipo de dados de cadeia de caracteres (exceto para os tipos de dados ntext ou text)
Um smalldatetime
Uma variável datetime

Por exemplo:

'Dec 31, 2020 11:59 PM'
'1/1/2021'

Para obter informações sobre como especificar valores de datetime, consulte Tipos de data e hora.

Observação

Para ignorar a data de expiração, use a opção SKIP.

RETAINDAYS = { days | @days_var }
Especifica o número de dias que devem decorrer para que este conjunto de mídias de backup possa ser substituído. Se for fornecida como uma variável (@days_var), ela precisará ser especificada como um inteiro.

{ METADATA_ONLY | SNAPSHOT }

Aplica-se a: SQL Server 2022 (16.x)

METADATA_ONLY e SNAPSHOT são sinônimos.

Opções de conjunto de mídias

Essas opções funcionam no conjunto de mídias como um todo.

{ NOINIT | INIT }

Controla se a operação de backup anexa ou substitui os conjuntos de backup existentes na mídia de backup. O padrão é anexar ao backup mais recente definido na mídia (NOINIT).

Observação

Para obter informações sobre as interações entre { NOINIT | INIT } e { NOSKIP | SKIP }, consulte Comentários, mais adiante neste tópico.

NOINIT
Indica que o conjunto de backup é anexado ao conjunto de mídias especificado, preservando conjuntos de backup existentes. Se uma senha de mídia estiver definida para o conjunto de mídias, a senha deverá ser fornecida. NOINIT é o padrão.

Para obter mais informações, consulte Conjuntos de mídias, famílias de mídia e conjuntos de backup.

INIT
Especifica que todos os conjuntos de backup devem ser substituídos, mas preserva o cabeçalho de mídia. Se INIT estiver especificado, qualquer conjunto de backup existente naquele dispositivo será substituído, se as condições permitirem. Por padrão, BACKUP verificará as seguintes condições e não substituirá a mídia de backup se qualquer uma delas existir:

Um conjunto de backup ainda não expirou. Para obter mais informações, consulte as opções EXPIREDATE e RETAINDAYS.
O nome do conjunto de backup fornecido na instrução BACKUP, se fornecido, não corresponde ao nome na mídia de backup. Para obter mais informações, consulte a opção NAME, anteriormente nesta seção.

Para substituir essas verificações, use a opção SKIP.

Para obter mais informações, consulte Conjuntos de mídias, famílias de mídia e conjuntos de backup.

{ NOSKIP | SKIP }

Controla se a operação de backup verifica a data e a hora de validade dos conjuntos de backup existentes na mídia antes de substituí-los.

Observação

Para obter informações sobre as interações entre { NOINIT | INIT } e { NOSKIP | SKIP }, consulte "Comentários", mais adiante neste tópico.

NOSKIP
Instrui a instrução BACKUP a verificar a data de validade de todos os conjuntos de backup na mídia antes de permitir que eles sejam substituídos. Esse é o comportamento padrão.

SKIP
Desabilita a verificação de validade e nome do conjunto de backup que normalmente é executada pela instrução BACKUP para impedir a substituição de conjuntos de backup. Para obter informações sobre as interações entre { INIT | NOINIT } e { NOSKIP | SKIP }, confira "Comentários", mais adiante neste artigo. Para exibir as datas de validade de conjuntos de backup, consulte a coluna expiration_date da tabela de histórico backupset.

{ NOFORMAT | FORMAT }

Especifica se o cabeçalho da mídia deve ser gravado nos volumes usados para esta operação de backup, substituindo qualquer cabeçalho de mídia e conjuntos de backup existentes.

NOFORMAT
Especifica que a operação de backup preserva o cabeçalho da mídia e os conjuntos de backup existentes nos volumes de mídia usados para esta operação de backup. Esse é o comportamento padrão.

FORMAT
Especifica que um novo conjunto de mídias deve ser criado. FORMAT faz com que a operação de backup grave um novo cabeçalho de mídia em todos os volumes de mídia usados para a operação de backup. O conteúdo existente do volume se torna inválido, porque qualquer cabeçalho de mídia e conjuntos de backup existentes são substituídos.

Importante

Use FORMAT com cautela. A formatação de qualquer volume de um conjunto de mídias renderiza todo o conjunto de mídias como inutilizável. Por exemplo, se você iniciar uma única fita pertencente a um conjunto de mídias distribuído existente, todo o conjunto de mídias será renderizado como inútil.

A especificação de FORMAT implica SKIP; SKIP não precisa ser declarado explicitamente.

MEDIADESCRIPTION = { text | @text_variable }

Especifica a descrição de texto de forma livre do conjunto de mídias, com um máximo de 255 caracteres.

NOME_MEDIA = { media_name | @media_name_variable }

Especifica o nome da mídia de todo o conjunto de mídias de backup. O nome da mídia deve ter menos de 128 caracteres. Se MEDIANAME for especificado, deverá corresponder ao nome de mídia especificado anteriormente já existente nos volumes de backup. Se não estiver especificado ou se a opção SKIP estiver especificada, não haverá nenhuma verificação do nome da mídia.

TAMANHO BLOCO = { tamanho do bloco | @blocksize_variable }

Especifica o tamanho do bloco físico, em bytes. Os tamanhos com suporte são 512, 1024, 2048, 4096, 8192, 16384, 32768 e 65536 (64 KB) bytes. O padrão é 65536 para dispositivos de fita e 512 para outros dispositivos. Normalmente, essa opção é desnecessária porque BACKUP seleciona automaticamente um tamanho de bloco apropriado ao dispositivo. A declaração explícita de um tamanho de bloco substitui a seleção automática de tamanho de bloco.

Se estiver fazendo um backup que planeja copiar e restaurar de um CD-ROM, especifique BLOCKSIZE=2048.

Observação

Normalmente, essa opção afeta o desempenho apenas durante a gravação em dispositivos de fita.

Opções de transferência de dados

CONTAGEM DE BUFFERS = { contagem de buffers | @buffercount_variable }

Especifica o número total de buffers de E/S a ser usado para a operação de backup. É possível especificar qualquer inteiro positivo. No entanto, grandes números de buffers podem provocar erros de "memória insuficiente" devido a espaço de endereço virtual inadequado no processo Sqlservr.exe.

O espaço total usado pelos buffers é determinado por: BUFFERCOUNT * MAXTRANSFERSIZE.

Observação

Para obter informações importantes sobre como usar a opção BUFFERCOUNT, confira o blog BufferCount data transfer option can lead to OOM condition (Opção incorreta de transferência de dados de BufferCount pode levar à condição OOM).

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable }

Especifica a maior unidade de transferência em bytes a ser usada entre SQL Server e a mídia de backup. Os valores possíveis são múltiplos de 65536 bytes (64 KB), estendendo-se até 4194304 bytes (4 MB).

Durante a criação de backups usando o Serviço Gravador do SQL, se o banco de dados tiver configurado FILESTREAM ou incluir grupos de arquivos com otimização de memória, o MAXTRANSFERSIZE no momento de uma restauração deverá ser maior ou igual a MAXTRANSFERSIZE que foi usado quando o backup foi criado.

Para bancos de dados habilitados para TDE (Transparent Data Encryption) com um único arquivo de dados, o MAXTRANSFERSIZE padrão é 65.536 (64 KB). Para bancos de dados não criptografados com TDE, o padrão MAXTRANSFERSIZE é 1048576 (1 MB) ao usar o backup em DISK e 65536 (64 KB) ao usar VDI ou TAPE. Para obter mais informações sobre como usar a compactação de backup com bancos de dados criptografados com TDE, consulte a seção Comentários.

Opções de gerenciamento de erros

Essas opções permitem determinar se as somas de verificação de backup estão habilitadas para a operação de backup e se a operação é interrompida quando um erro é encontrado.

{ NO_CHECKSUM | CHECKSUM }

Controla se somas de verificação de backup estão habilitadas.

NO_CHECKSUM
Desabilita explicitamente a geração de somas de verificação de backup (e a validação de somas de verificação de página). Esse é o comportamento padrão.

CHECKSUM
Especifica que a operação de backup verifica cada página quanto à soma de verificação e a página interrompida, se estiver habilitada e disponível, e gera uma soma de verificação para o backup inteiro.

O uso de somas de verificação de backup pode afetar a carga de trabalho e a taxa de transferência de backup.

Para obter mais informações, consulte Possíveis erros de mídia durante backup e restauração.

{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

Controla se uma operação de backup para ou continua depois de encontrar um erro de soma de verificação de página.

STOP_ON_ERROR
Instrui BACKUP a falhar se uma soma de verificação de página não estiver correta. Esse é o comportamento padrão.

CONTINUE_AFTER_ERROR
Instrui BACKUP a continuar apesar de encontrar erros como somas de verificação inválidas ou páginas interrompidas.

Se você não conseguir fazer backup da parte final do log usando a opção NO_TRUNCATE quando o banco de dados estiver danificado, tente fazer um backup da parte final do log especificando CONTINUE_AFTER_ERROR em vez de NO_TRUNCATE.

Para obter mais informações, consulte Possíveis erros de mídia durante backup e restauração.

Opções de compatibilidade

RESTART

A partir do SQL Server 2008 (10.0.x), não tem nenhum efeito. Esta opção é aceita pela versão para compatibilidade com versões anteriores do SQL Server.

Opções de monitoramento

ESTATÍSTICAS [ = porcentagem ]

Exibe uma mensagem sempre que outro percentual for concluída e é usado para medir o progresso. Se percentage for omitido, o SQL Server exibirá uma mensagem após a conclusão de cada 10%.

A opção STATS informa a porcentagem concluída de acordo com o limite de relatório do próximo intervalo. Esse limite é aproximadamente a porcentagem especificada. Por exemplo, com STATS=10, se a quantidade concluída for 40%, a opção poderá exibir 43%. Para conjuntos de backup grandes, isso não é um problema, porque a porcentagem concluída muda muito lentamente entre chamadas de E/S concluídas.

Opções de fita

Essas opções são usadas apenas para dispositivos TAPE. Se um dispositivo que não seja de fita estiver sendo usado, essas opções serão ignoradas.

{ REWIND | NOREWIND }

REWIND
Especifica que SQL Server solta e rebobina a fita. REWIND é o padrão.

NOREWIND
Especifica que o SQL Server mantém a fita aberta após a operação de backup. É possível usar essa opção para ajudar a melhorar o desempenho ao executar várias operações de backup em uma fita.

NOREWIND implica NOUNLOAD e essas opções são incompatíveis dentro de uma única instrução BACKUP.

Observação

Se você usar NOREWIND, a instância do SQL Server reterá a propriedade da unidade de fita até que uma instrução BACKUP ou RESTORE em execução no mesmo processo use a opção REWIND ou UNLOAD ou que a instância do servidor seja encerrada. Manter a fita aberta evita que outros processos a acessem. Para obter informações sobre como exibir uma lista de fitas abertas e fechar uma fita aberta, consulte Dispositivos de backup.

{ UNLOAD | NOUNLOAD }

Observação

UNLOAD e NOUNLOAD são configurações de sessão que persistem durante a vida da sessão ou até que ela seja redefinida com a especificação da alternativa.

UNLOAD
Especifica que a fita é rebobinada e descarregada automaticamente quando o backup for concluído. UNLOAD é o padrão quando uma sessão começa.

NOUNLOAD
Especifica que, após a operação BACKUP, a fita permanece carregada na unidade de fita.

Observação

Para um backup em um dispositivo de backup em fita, a opção BLOCKSIZE afeta o desempenho da operação de backup. Normalmente, essa opção afeta o desempenho apenas durante a gravação em dispositivos de fita.

Opções específicas de log

Essas opções são usadas apenas com BACKUP LOG.

Observação

Se você não quiser fazer backups de log, use o modelo de recuperação simples. Para saber mais, confira Modelos de recuperação.

{ NÃO RECUPERAÇÃO | ESPERA = undo_file_name }

NORECOVERY
Faz backup do final do log e deixa o banco de dados no estado de RESTORING. NORECOVERY é útil ao executar failover em um banco de dados secundário ou ao salvar o final do log antes de uma operação RESTORE.

Para executar um backup de log de melhor esforço que ignora o truncamento do log e, em seguida, coloca o banco de dados no estado RESTORING atomicamente, use as opções NO_TRUNCATE e NORECOVERY em conjunto.

STANDBY = standby_file_name
Faz BACKUP do final do log e deixa o banco de dados em um estado STANDBY e somente leitura. A cláusula STANDBY grava dados em espera (executando reversão, mas com a opção de restaurações adicionais). O uso da opção STANDBY é equivalente a BACKUP LOG WITH NORECOVERY seguido por um RESTORE WITH STANDBY.

O uso do modo em espera exige um arquivo em espera, especificado por standby_file_name, cujo local é armazenado no log do banco de dados. Se o arquivo especificado já existir, o Mecanismo de Banco de Dados o substituirá. Se o arquivo não existir, ele será criado pelo Mecanismo de Banco de Dados. O arquivo em espera se torna parte do banco de dados.

Esse arquivo contém as alterações revertidas que deverão ser revertidas se operações RESTORE LOG forem aplicadas subseqüentemente. Deve haver espaço em disco suficiente para que o arquivo em espera cresça para que possa conter todas as páginas distintas do banco de dados que foi modificado pela reversão de transações não confirmadas.

NO_TRUNCATE

Especifica que o log de transações não deve ser não truncado e faz com que o Mecanismo de Banco de Dados tente fazer backup, independentemente do estado do banco de dados. Consequentemente, um backup feito com NO_TRUNCATE pode conter metadados incompletos. Essa opção permite fazer backup do log de transações em situações em que o banco de dados está danificado.

A opção NO_TRUNCATE de BACKUP LOG é equivalente a especificar COPY_ONLY e CONTINUE_AFTER_ERROR.

Sem a opção NO_TRUNCATE, o banco de dados deve estar no estado ONLINE. Se o banco de dados estiver no estado SUSPENDED, você poderá criar um backup especificando NO_TRUNCATE. Mas se o banco de dados estiver no estado OFFLINE ou EMERGENCY, BACKUP não terá permissão, mesmo com NO_TRUNCATE. Para obter mais informações sobre estados do banco de dados, consulte Estados do banco de dados.

Sobre o trabalho com backups do SQL Server

Esta seção introduz os seguintes conceitos de backup essenciais:

Tipos de backup Truncamento do log de transações Formatando a mídia de backup Trabalhando com dispositivos de backup e conjuntos de mídia Restaurando backups do SQL Server

Observação

Para obter uma introdução ao backup no SQL Server, consulte Visão geral de backup.

Tipos de backup

Os tipos de backup com suporte dependem do modelo de recuperação do banco de dados, da seguinte maneira:

Todos os modelos de recuperação oferecem suporte a backups completos e diferenciais de dados.

Escopo do backup	Tipos de backup
Banco de dados inteiro	Backups de banco de dados abrangem o banco de dados inteiro. Opcionalmente, cada backup de banco de dados pode servir como a base de uma série de um ou mais backups de banco de dados diferenciais.
Banco de dados parcial	Backups parciais abrangem grupos de arquivos de leitura/gravação e, possivelmente, um ou mais arquivos ou grupos de arquivos somente leitura. Opcionalmente, cada backup parcial pode servir como a base de uma série de um ou mais backups parciais diferenciais.
Arquivo ou grupo de arquivos	Backups de arquivos abrangem um ou mais arquivos ou grupos de arquivos e são relevantes apenas para bancos de dados que contêm vários grupos de arquivos. No modelo de recuperação simples, os backups de arquivos são essencialmente restritos a grupos de arquivos secundários somente leitura. Opcionalmente, cada backup de arquivo pode servir como a base de uma série de um ou mais backups de arquivos diferenciais.

No modelo de recuperação completa ou modelo de recuperação bulk-logged, os backups convencionais também incluem backups de log de transações sequenciais (ou backups de log), que são obrigatórios. Cada backup de log abrange a parte do log de transações que estava ativa quando o backup foi criado e inclui todos os registros de log cujo backup não foi feito em um backup de log anterior.

Para minimizar a exposição de perda de trabalho, à custa de sobrecarga administrativa, você deve agendar backups de log freqüentes. 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.

Recomendamos que você coloque backups de log em um volume separado dos backups de banco de dados.

Observação

Para poder criar o primeiro backup de log, você deve criar um backup completo.
Um backup somente cópia é um backup completo para finalidades especiais ou um backup de log que é independente da sequência normal de backups convencionais. Para criar um backup somente cópia, especifique a opção COPY_ONLY na instrução BACKUP. Para obter mais informações, veja Backups somente cópia.

Truncamento do log de transações

Para evitar o preenchimento do log de transações de um banco de dados, os backups rotineiros são essenciais. No modelo de recuperação simples, o truncamento do log ocorre automaticamente depois que o backup do banco de dados é feito, e no modelo de recuperação completa, depois que o backup do log de transações é feito. No entanto, às vezes o processo de truncamento pode ser demorado. Para ver informações sobre os fatores que podem adiar o truncamento de log, consulte O log de transações.

Observação

As opções BACKUP LOG WITH NO_LOG e WITH TRUNCATE_ONLY foram descontinuadas. Se você estiver usando o modelo de recuperação completa ou bulk-logged e dever remover a cadeia de backup de log de um banco de dados, alterne para o modelo de recuperação simples. Para obter mais informações, veja Exibir ou alterar o modelo de recuperação de um banco de dados.

Formatando mídia de backup

A mídia de backup será formatada por uma instrução BACKUP se, e apenas se, qualquer uma das seguintes situações for verdadeira:

A opção FORMAT foi especificada.
A mídia estiver vazia.
A operação estiver gravando uma fita de continuação.

Trabalhar com dispositivos de backup e conjuntos de mídia

Dispositivos de backup em um conjunto de mídias distribuído (um conjunto de distribuição)

Um conjunto de distribuição é um conjunto de arquivos de disco nos quais os dados são divididos em blocos e distribuídos em uma ordem fixa. O número de dispositivos de backup usados em um conjunto de distribuição deve permanecer o mesmo (a não ser que a mídia seja reinicializada com FORMAT).

O exemplo a seguir grava um backup do banco de dados AdventureWorks2022 em um novo conjunto de mídias distribuído que usa três arquivos de disco.

BACKUP DATABASE AdventureWorks2022
TO DISK = 'X:\SQLServerBackups\AdventureWorks1.bak',
DISK = 'Y:\SQLServerBackups\AdventureWorks2.bak',
DISK = 'Z:\SQLServerBackups\AdventureWorks3.bak'
WITH FORMAT,
  MEDIANAME = 'AdventureWorksStripedSet0',
  MEDIADESCRIPTION = 'Striped media set for AdventureWorks2022 database';
GO

Após a definição de um dispositivo de backup como parte de um conjunto de distribuição, ele não pode ser usado como um backup de dispositivo único a menos que FORMAT seja especificado. Do mesmo modo, um dispositivo de backup que contém backups não distribuídos não pode ser usado em um conjunto de distribuição a menos que FORMAT seja especificado. Para dividir um conjunto de backup distribuído, use FORMAT.

Se MEDIANAME nem MEDIADESCRIPTION estiver especificado quando um cabeçalho de mídia for gravado, o campo de cabeçalho de mídia correspondente ao item em branco estará vazio.

Trabalhar com um conjunto de mídias espelhado

Normalmente, backups não são espelhados e instruções BACKUP simplesmente incluem uma cláusula TO. No entanto, um total de quatro espelhos é possível por conjunto de mídias. Em um conjunto de mídias espelhado, a operação de backup grava em vários grupos de dispositivos de backup. Cada grupo de dispositivos de backup compõe um único espelho dentro do conjunto de mídias espelhado. Todos os espelhos devem usar a mesma quantidade e tipo de dispositivos físicos de backup que devem ter as mesmas propriedades.

Para fazer backup em um conjunto de mídias espelhado, todos os espelhos devem estar presentes. Para fazer backup em um conjunto de mídias espelhado, especifique a cláusula TO para especificar o primeiro espelho e especifique uma cláusula MIRROR TO para cada espelho adicional.

Para um conjunto de mídias espelhado, cada cláusula MIRROR TO deve listar o mesmo número e tipo de dispositivos que a cláusula TO. O exemplo a seguir grava em um conjunto de mídias espelhado que contém dois espelhos e usa três dispositivos por espelho:

BACKUP DATABASE AdventureWorks2022
TO DISK = 'X:\SQLServerBackups\AdventureWorks1a.bak',
  DISK = 'Y:\SQLServerBackups\AdventureWorks2a.bak',
  DISK = 'Z:\SQLServerBackups\AdventureWorks3a.bak'
MIRROR TO DISK='X:\SQLServerBackups\AdventureWorks1b.bak',
  DISK = 'Y:\SQLServerBackups\AdventureWorks2b.bak',
  DISK = 'Z:\SQLServerBackups\AdventureWorks3b.bak';
GO

Importante

Este exemplo foi criado para permitir teste em seu sistema local. Na prática, fazer backup em vários dispositivos na mesma unidade deve afetar o desempenho e eliminar a redundância para a qual conjuntos de mídias espelhados foram projetados.

Famílias de mídia em conjuntos de mídia espelhados

Cada dispositivo de backup especificado na cláusula TO de uma instrução BACKUP corresponde a uma família de mídia. Por exemplo, se as cláusulas TO listarem três dispositivos, a instrução BACKUP gravará dados nas três famílias de mídia. Em um conjunto de mídias espelhado, cada espelho deve conter uma cópia de cada família de mídia. É por isso que o número de dispositivos deve ser idêntico em cada espelho.

Quando vários dispositivos são listados para cada espelho, a ordem dos dispositivos determina qual família de mídia é gravada em um determinado dispositivo. Por exemplo, em cada uma das listas de dispositivos, o segundo dispositivo corresponde à segunda família de mídia. Para os dispositivos do exemplo acima, a correspondência entre dispositivos e famílias de mídia é mostrada na tabela a seguir.

Espelho	Família de mídia 1	Família de mídia 2	Família de mídia 3
0	`Z:\AdventureWorks1a.bak`	`Z:\AdventureWorks2a.bak`	`Z:\AdventureWorks3a.bak`
1	`Z:\AdventureWorks1b.bak`	`Z:\AdventureWorks2b.bak`	`Z:\AdventureWorks3b.bak`

O backup de uma família de mídia sempre deve ser feito no mesmo dispositivo dentro de um espelho específico. Portanto, a cada vez que você usar um conjunto de mídias existente, liste os dispositivos de cada espelho na mesma ordem em que foram especificados quando o conjunto de mídias foi criado.

Para obter mais informações sobre conjuntos de mídias espelhados, consulte Conjuntos de mídias de backup espelhados. Para obter mais informações sobre conjuntos de mídia e famílias de mídia em geral, consulte Conjuntos de mídia, famílias de mídia e conjuntos de backup.

Restaurar backups do SQL Server

Para restaurar um banco de dados e, opcionalmente, recuperá-lo para fazê-lo ficar online ou restaurar um arquivo ou grupo de arquivos, use a instrução Transact-SQL RESTORE ou as tarefas Restore do SQL Server Management Studio. Para obter mais informações, confira Visão geral de restauração e recuperação.

Considerações adicionais sobre as opções de BACKUP

Interação de SKIP, NOSKIP, INIT e NOINIT

Esta tabela descreve interações entre as opções { NOINIT | INIT } e { NOSKIP | SKIP }.

Observação

Se a mídia de fita estiver vazia ou se o arquivo de backup em disco não existir, todas essas interações gravarão um cabeçalho de mídia e continuarão. Se a mídia não estiver vazia e não tiver um cabeçalho de mídia válido, essas operações fornecerão comentários informando que essa não é uma mídia MTF válida e terminarão a operação de backup.

Opção Pular	NOINIT	INIT
NOSKIP	Se o volume contiver um cabeçalho de mídia válido, ele verificará o nome da mídia correspondente ao `MEDIANAME` fornecido, se houver. Se houver correspondência, anexará o conjunto de backup preservando todos os conjuntos de backup existentes. Se o volume não contiver um cabeçalho de mídia válido, ocorrerá um erro.	Se o volume contiver um cabeçalho de mídia válido, executará as seguintes verificações: Se `MEDIANAME` for especificado, ele verificará se o nome da mídia fornecido corresponde ao nome do cabeçalho de mídia.¹ Verifica se não há nenhum conjunto de backup vigente na mídia. Se houver, terminará o backup. Se essas verificações passarem, substituirá qualquer conjunto de backup na mídia preservando apenas o cabeçalho da mídia. Se o volume não contiver um cabeçalho de mídia válido, ele gerará um cabeçalho com o uso do `MEDIANAME` e `MEDIADESCRIPTION` especificados, se houver.
SKIP	Se o volume contiver um cabeçalho de mídia válido, acrescentará o conjunto de backup, preservando todos os conjuntos de backup existentes.	Se o volume contiver um cabeçalho de mídia válido², ele substituirá todos os conjuntos de backup na mídia, preservando apenas o cabeçalho da mídia. Se a mídia estiver vazia, ela gerará um cabeçalho de mídia usando o `MEDIANAME` e `MEDIADESCRIPTION` especificados, se houver.

¹ O usuário deve pertencer à função de banco de dados ou de servidor fixa apropriada para executar uma operação de backup.

² A validade inclui o número de versão de MTF e outras informações de cabeçalho. Se a versão especificada não tiver suporte ou for um valor inesperado, ocorrerá um erro.

Compatibilidade

Cuidado

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

BACKUPRESTART dá suporte à opção de fornecer compatibilidade com versões anteriores do SQL Server. Mas RESTART não tem nenhum efeito.

Comentários

Backups de banco de dados ou de log podem ser anexados a qualquer dispositivo de disco ou de fita, permitindo que um banco de dados e seus logs de transações sejam mantidos em um local físico.

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

Você não pode fazer backup de um banco de dados nos seguintes estados:

Restaurando
Standby
Somente leitura

Operações de backup entre plataformas, mesmo entre tipos diferentes de processadores, podem ser executadas desde que a ordenação do banco de dados tenha suporte no sistema operacional.

Começando com o SQL Server 2016 (13.x), definir MAXTRANSFERSIZE maior que 65.536 (64 KB) habilita um algoritmo de compactação otimizada para bancos de dados criptografados com Transparent Data Encryption (TDE) que primeiro descriptografa uma página, compacta-a e, depois, criptografa-a novamente. Se MAXTRANSFERSIZE não for especificado ou se MAXTRANSFERSIZE = 65536 (64 KB) for usado, a compactação de backup com bancos de dados criptografados com TDE compactará diretamente as páginas criptografadas e poderá não resultar em taxas de compactação satisfatórias. Para obter mais informações, consulte Compactação de backup para bancos de dados habilitados para TDE.

Do SQL Server 2019 (15.x) CU5 em diante, a configuração de MAXTRANSFERSIZE não é mais necessária para habilitar esse algoritmo de compactação otimizado com TDE. Se o comando backup for especificado, WITH COMPRESSION ou a configuração de servidor padrão de compactação de backup for definida como 1, MAXTRANSFERSIZE aumentará automaticamente para 128 K para habilitar o algoritmo otimizado. Se MAXTRANSFERSIZE for especificado no comando de backup com um valor > 64 K, o valor fornecido será respeitado. Em outras palavras, o SQL Server nunca diminui automaticamente o valor, apenas o aumenta. Se você precisar fazer backup de um banco de dados criptografado com TDE com MAXTRANSFERSIZE = 65536, será preciso especificar WITH NO_COMPRESSION ou garantir que a configuração de servidor padrão de compactação de backup seja definida como 0.

Observação

Há alguns casos em que o padrão MAXTRANSFERSIZE é maior que 64 K:

Quando o banco de dados tem vários arquivos de dados criados, ele usa MAXTRANSFERSIZE> 64 K.
Ao realizar o backup em URL para o Armazenamento de Blobs do Azure, o padrão é MAXTRANSFERSIZE = 1048576 (1 MB).
Ao executar backup em URL para armazenamento de objetos compatível com S3, o padrão MAXTRANSFERSIZE = 10485760 (10 MB).

Mesmo que uma destas condições se aplique, você deverá definir explicitamente MAXTRANSFERSIZE como maior que 64 K no comando de backup para obter o algoritmo de compactação de backup otimizado, a menos que esteja no SQL Server 2019 (15.x) CU5 ou posterior.

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 do log com muita frequência, essas mensagens de êxito serão acumuladas muito rapidamente, resultando em logs de erros grandes que podem dificultar a localização de outras mensagens. Nessas situações, você pode suprimir essas entradas de log usando o sinalizador de rastreamento 3226, caso a automação ou o monitoramento dependa dessas entradas. Para obter mais informações, consulte Sinalizadores de rastreamento.

Interoperabilidade

O SQL Server usa um processo de backup online para permitir que um backup de banco de dados seja feito enquanto o banco de dados ainda está uso. Durante um backup, a maior parte das operações é possível. Por exemplo, instruções INSERT, UPDATE ou DELETE são permitidas durante uma operação de backup

Operações que não podem ser executadas durante um backup de banco de dados ou de log de transações incluem:

Operações de gerenciamento de arquivos, como a instrução ALTER DATABASE com as opções ADD FILE ou REMOVE FILE.
Operações de redução do banco de dados ou de arquivos. Isso inclui operações de redução automática.

Se uma operação de backup se sobrepuser a um gerenciamento ou DBCC SHRINK operação de arquivos, ocorrerá um conflito. Independentemente de qual operação em conflito começou primeiro, a segunda operação aguardará até que o tempo limite do bloqueio definido pela primeira operação seja esgotado (o período limite é controlado pela configuração de tempo limite de uma sessão). Se o bloqueio for liberado durante o período de tempo limite, a segunda operação continuará. Se o tempo limite do bloqueio for esgotado, a segunda operação falhará.

Metadados

SQL Server inclui as seguintes tabelas de histórico de backup que controlam as atividades do banco de dados:

Quando uma restauração é executada, se o conjunto de backup ainda não tiver sido registrado no banco de dados msdb, as tabelas de histórico de backup poderão ser modificadas.

Segurança

Começando pelo SQL Server 2012 (11.x), as opções PASSWORD e MEDIAPASSWORD foram descontinuadas para a criação de backups. Ainda é possível restaurar 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. A conta de inicialização do SQL Server precisa ter permissões de leitura e gravação para o dispositivo de backup e a pasta em que os arquivos de backup são gravados. 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 na tentativa de backup ou restauração.

Exemplos

Esta seção contém os seguintes exemplos:

R. Fazer backup de um banco de dados completo
B. Fazer backup do banco de dados e do log
C. Criar um backup completo dos grupos de arquivos secundários
D. Criar um backup de arquivo diferencial dos grupos de arquivos secundários
E. Criar e fazer backup em um conjunto de mídias espelhado unifamiliar
F. Criar e fazer backup em um conjunto de mídias espelhado multifamiliar
G. Fazer backup em um conjunto de mídias espelhado existente
H. Criar um backup compactado em um novo conjunto de mídias
I. Fazer backup no Armazenamento de Blobs do Azure
J. [Fazer backup no armazenamento de objetos compatível com S3] ((#j backup de armazenamento de objetos compatíveis com s3)
K. Acompanhar o progresso da instrução de backup

Observação

Os tópicos de instruções de backup contêm exemplos adicionais. Para obter mais informações, veja Visão geral do backup.

R. Fazer backup de um banco de dados completo

O exemplo a seguir faz backup do banco de dados AdventureWorks2022 em um arquivo de disco.

BACKUP DATABASE AdventureWorks2022
 TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
    WITH FORMAT;
GO

B. Fazer backup do banco de dados e do log

O exemplo a seguir faz backup do banco de dados de exemplo AdventureWorks2022 que, por padrão, usa o modelo de recuperação simples. Para oferecer suporte a backups de log, o banco de dados AdventureWorks2022 é modificado para usar o modelo de recuperação completa.

Em seguida, o exemplo usa sp_addumpdevice para criar um dispositivo de backup lógico para fazer backup dos dados, AdvWorksData, e cria outro dispositivo de backup lógico para fazer backup do log, AdvWorksLog.

Em seguida, o exemplo cria um backup de dados completo em AdvWorksData e, após um período de atividades de atualização, faz o backup do log em AdvWorksLog.

-- To permit log backups, before the full database backup, modify the database
-- to use the full recovery model.
USE master;
GO
ALTER DATABASE AdventureWorks2022
    SET RECOVERY FULL;
GO
-- Create AdvWorksData and AdvWorksLog logical backup devices.
USE master
GO
EXEC sp_addumpdevice 'disk', 'AdvWorksData',
'Z:\SQLServerBackups\AdvWorksData.bak';
GO
EXEC sp_addumpdevice 'disk', 'AdvWorksLog',
'X:\SQLServerBackups\AdvWorksLog.bak';
GO

-- Back up the full AdventureWorks2022 database.
BACKUP DATABASE AdventureWorks2022 TO AdvWorksData;
GO
-- Back up the AdventureWorks2022 log.
BACKUP LOG AdventureWorks2022
    TO AdvWorksLog;
GO

Observação

Para um banco de dados de produção, faça backup do log regularmente. Os backups de log devem ser frequentes o suficiente para fornecer proteção contra perda de dados.

C. Criar um backup completo de arquivos dos grupos de arquivos secundários

O exemplo a seguir cria um backup de arquivo completo de todos os arquivos dos dois grupos de arquivos secundários.

--Back up the files in SalesGroup1:
BACKUP DATABASE Sales
    FILEGROUP = 'SalesGroup1',
    FILEGROUP = 'SalesGroup2'
    TO DISK = 'Z:\SQLServerBackups\SalesFiles.bck';
GO

D. Criar um backup diferencial de arquivos dos grupos de arquivos secundários

O exemplo a seguir cria um backup diferencial de cada arquivo nos dois grupos de arquivos secundários.

--Back up the files in SalesGroup1:
BACKUP DATABASE Sales
    FILEGROUP = 'SalesGroup1',
    FILEGROUP = 'SalesGroup2'
    TO DISK = 'Z:\SQLServerBackups\SalesFiles.bck'
    WITH
      DIFFERENTIAL;
GO

E. Criar e fazer backup em um conjunto de mídias espelhado unifamiliar

O exemplo a seguir cria um conjunto de mídias espelhado que contêm uma única família de mídia e quatro espelhos nos quais faz backup do banco de dados AdventureWorks2022.

BACKUP DATABASE AdventureWorks2022
TO TAPE = '\\.\tape0'
MIRROR TO TAPE = '\\.\tape1'
MIRROR TO TAPE = '\\.\tape2'
MIRROR TO TAPE = '\\.\tape3'
WITH
    FORMAT,
    MEDIANAME = 'AdventureWorksSet0';

F. Criar e fazer backup em um conjunto de mídias espelhado multifamiliar

O exemplo a seguir cria um conjunto de mídias espelhado no qual cada espelho consiste em duas famílias de mídia. Em seguida, o exemplo faz backup do banco de dados AdventureWorks2022 nos dois espelhos.

BACKUP DATABASE AdventureWorks2022
TO TAPE = '\\.\tape0', TAPE = '\\.\tape1'
MIRROR TO TAPE = '\\.\tape2', TAPE = '\\.\tape3'
WITH
    FORMAT,
    MEDIANAME = 'AdventureWorksSet1';

G. Fazer backup em um conjunto de mídias espelhado existente

O exemplo a seguir anexa um conjunto de backup ao conjunto de mídias criado no exemplo anterior.

BACKUP LOG AdventureWorks2022
TO TAPE = '\\.\tape0', TAPE = '\\.\tape1'
MIRROR TO TAPE = '\\.\tape2', TAPE = '\\.\tape3'
WITH
    NOINIT,
    MEDIANAME = 'AdventureWorksSet1';

Observação

NOINIT, que é o padrão, é mostrado aqui para maior clareza.

H. Criar um backup compactado em um novo conjunto de mídias

O exemplo a seguir formata a mídia, criando um conjunto de mídias, e executa um backup completo compactado do banco de dados AdventureWorks2022.

BACKUP DATABASE AdventureWorks2022 TO DISK='Z:\SQLServerBackups\AdvWorksData.bak'
WITH
    FORMAT,
    COMPRESSION;

I. Fazer backup no Armazenamento de Blobs do Microsoft Azure

Este exemplo executa um backup de banco de dados completo no Armazenamento de Blobs do Sales Azure. O nome da Conta de armazenamento é mystorageaccount. O contêiner é chamado myfirstcontainer. Uma política de acesso armazenada já foi criada com direitos de leitura, gravação, exclusão e lista. 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. Para obter informações sobre o backup do SQL Server no Armazenamento de Blobs do Azure, consulte Backup e restauração do SQL Server com o Armazenamento de Blobs do Azure e Backup do SQL Server na URL.

BACKUP DATABASE Sales
TO URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales.bak'
WITH STATS = 5;

Você também pode fazer backup de seu banco de dados em várias faixas, e ficaria assim:

BACKUP DATABASE Sales
TO URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales-01.bak',
URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales-02.bak',
URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales-03.bak',
URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales-04.bak'
WITH COPY_ONLY;

J. Fazer backup no armazenamento de objetos compatível com S3

Aplica-se a: SQL Server 2022 (16.x)

Este exemplo executa um backup completo do banco de dados Sales em uma plataforma de armazenamento de objetos compatível com o S3. O nome da credencial não é necessário na instrução ou para corresponder ao caminho exato da URL, mas executará uma pesquisa para a credencial adequada na URL fornecida. Para obter mais informações, confira Backup e restauração do SQL Server com armazenamento de objetos compatíveis com o S3.

BACKUP DATABASE Sales
TO      URL = 's3://10.10.10.10:8787/sqls3backups/sales_01.bak'
,       URL = 's3://10.10.10.10:8787/sqls3backups/sales_02.bak'
,       URL = 's3://10.10.10.10:8787/sqls3backups/sales_03.bak'
WITH    FORMAT
,       STATS               = 10
,       COMPRESSION;

K. Acompanhar o progresso da instrução de backup

A seguinte consulta retorna informações sobre as instruções de backup atualmente em execução:

SELECT query = a.text, start_time, percent_complete,
    eta = dateadd(second,estimated_completion_time/1000, getdate())
FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%';

SQL Server

* Instância Gerenciada de SQL *

Analytics Platform
System (PDW)

Instância Gerenciada do Azure SQL

Faz backup de um banco de dados SQL em uma Instância Gerenciada de SQL do Azure. A Instância Gerenciada de SQL do Azure tem backups automáticos. Você pode criar backups COPY_ONLY de bancos de dados completos. Não há suporte para backups de instantâneo de arquivo, log e diferencial.

Também se aplica à Instância Gerenciada de SQL habilitada pelo Azure Arc.

Sintaxe

BACKUP DATABASE { database_name | @database_name_var }
  TO URL = { 'physical_device_name' | @physical_device_name_var }[ ,...n ]
  WITH COPY_ONLY [, { <general_WITH_options> } ]
[;]

<general_WITH_options> [ ,...n ]::=

--Media Set Options
   MEDIADESCRIPTION = { 'text' | @text_variable }
 | MEDIANAME = { media_name | @media_name_variable }
 | BLOCKSIZE = { blocksize | @blocksize_variable }

--Data Transfer Options
   BUFFERCOUNT = { buffercount | @buffercount_variable }
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

--Error Management Options
   { NO_CHECKSUM | CHECKSUM }
 | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

--Compatibility Options
   RESTART

--Monitoring Options
   STATS [ = percentage ]

--Encryption Options
 ENCRYPTION (ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } , encryptor_options ) <encryptor_options> ::=
   SERVER CERTIFICATE = Encryptor_Name | SERVER ASYMMETRIC KEY = Encryptor_Name

Argumentos

DATABASE

Especifica um backup completo do banco de dados. Durante um backup de banco de dados, a Instância Gerenciada de SQL do Azure faz backup suficiente do log de transações para produzir um banco de dados consistente quando o backup é restaurado.

Importante

Um backup de banco de dados criado em uma instância gerenciada só pode ser restaurado em outra Instância Gerenciada de SQL do Azure ou para uma instância do SQL Server 2022. Isso ocorre porque a Instância Gerenciada de SQL tem uma versão de banco de dados interna mais recente em comparação com outras versões do SQL Server. Para obter mais informações, examine Restaurar um backup de banco de dados da Instância Gerenciada de SQL para o SQL Server 2022.

Ao restaurar um backup criado por BACKUP DATABASE (um backup de dados), o backup inteiro é restaurado. Para fazer a restauração dos backups automáticos da Instância Gerenciada de SQL, confira Restaurar um banco de dados em Instância Gerenciada de SQL do Azure.

{ database_name | @database_name_var }

É o banco de dados do qual é feito o backup do banco de dados completo. Se for fornecido como uma variável (@database_name_var), esse nome poderá ser especificado como uma constante de cadeia de caracteres (@database_name_var=database name) ou como uma variável de tipo de dados de cadeia de caracteres, exceto para os tipos de dados ntext ou text.

Para obter mais informações, consulte Backups completos de arquivos (SQL Server) e Fazer backup de arquivos e grupos de arquivos.

TO URL

Especifica a URL a ser usada para a operação de backup. O formato da URL é usado para criar backups no serviço de armazenamento do Microsoft Azure.

Importante

Para fazer backup em vários dispositivos ao fazer backup em uma URL, é necessário usar os tokens SAS (Assinatura de Acesso Compartilhado). Para obter exemplos sobre como criar uma Assinatura de Acesso Compartilhado, consulte Backup do SQL Server em uma URL e Simplificando a criação de credenciais do SQL com tokens SAS (Assinatura de Acesso Compartilhado) no Armazenamento do Azure com o PowerShell.

n
É um espaço reservado que indica que até 64 dispositivos de backup podem ser especificados em uma lista separada por vírgulas.

Opções WITH

Especifica opções a serem usadas com uma operação de backup.

ENCRYPTION

AES_128
AES_192
AES_256
TRIPLE_DES_3KEY
NO_ENCRYPTION

Se você optar pela criptografia, também precisará especificar o criptografador usando as opções do criptografador:

SERVER CERTIFICATE = <Encryptor_Name>
SERVER ASYMMETRIC KEY = <Encryptor_Name>

Opções de conjunto de backup

COPY_ONLY

Especifica que o backup é um backup somente cópia, o que não afeta a sequência normal de backups. Um backup somente cópia é criado independentemente dos backups automáticos do Banco de Dados SQL do Azure. Para obter mais informações, veja Backups somente cópia.

{ COMPRESSÃO | NO_COMPRESSION }

Especifica se a compactação de backup é executada neste backup, substituindo o padrão de nível de servidor.

O comportamento padrão é sem compactação de backup. No entanto, esse padrão pode ser alterado com a definição da opção backup compression default de configuração do servidor. Para obter informações sobre como exibir o valor atual dessa opção, consulte Exibir ou alterar propriedades do servidor.

COMPRESSION
Habilita explicitamente a compactação de backup.

NO_COMPRESSION
Desabilita explicitamente a compactação de backup.

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.

NOME = { backup_set_name | @_backup| set_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.

MEDIADESCRIPTION = { text | @text_variable }

Especifica a descrição de texto de forma livre do conjunto de mídias, com um máximo de 255 caracteres.

NOME_MEDIA = { media_name | @media_name_variable }

Especifica o nome da mídia de todo o conjunto de mídias de backup. O nome da mídia não deve ter mais de 128 caracteres. Se MEDIANAME for especificado, ele deverá corresponder ao nome da mídia especificado anteriormente já existente nos volumes de backup. Se não estiver especificado ou se a opção SKIP estiver especificada, não haverá nenhuma verificação do nome da mídia.

TAMANHO BLOCO = { tamanho do bloco | @blocksize_variable }

Opções de transferência de dados

CONTAGEM DE BUFFERS = { contagem de buffers | @buffercount_variable }

O espaço total usado pelos buffers é determinado por: BUFFERCOUNT * MAXTRANSFERSIZE.

Observação

Para obter informações importantes sobre como usar a BUFFERCOUNT opção, consulte a postagem no blog A opção de transferência de dados BufferCount incorreta pode levar à condição OOM.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable }

Observação

MAXTRANSFERSIZE especifica a maior unidade de transferência e não garante que cada operação de gravação transfira o maior tamanho especificado. MAXTRANSFERSIZE para operações de gravação de backups de log de transações distribuídos está definido como 64 KB.

Opções de gerenciamento de erros

Essas opções permitem determinar se as somas de verificação de backup estão habilitadas para a operação de backup e se a operação é interrompida quando um erro é encontrado.

{ NO_CHECKSUM | CHECKSUM }

Controla se somas de verificação de backup estão habilitadas.

NO_CHECKSUM
Desabilita explicitamente a geração de somas de verificação de backup (e a validação de somas de verificação de página). Esse é o comportamento padrão.

O uso de somas de verificação de backup pode afetar a carga de trabalho e a taxa de transferência de backup.

Para obter mais informações, consulte Possíveis erros de mídia durante backup e restauração.

{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

Controla se uma operação de backup para ou continua depois de encontrar um erro de soma de verificação de página.

STOP_ON_ERROR
Instrui BACKUP a falhar se uma soma de verificação de página não estiver correta. Esse é o comportamento padrão.

CONTINUE_AFTER_ERROR
Instrui BACKUP a continuar apesar de encontrar erros como somas de verificação inválidas ou páginas interrompidas.

Para obter mais informações, consulte Possíveis erros de mídia durante backup e restauração.

Opções de compatibilidade

RESTART

Não tem nenhum efeito. Esta opção é aceita pela versão para compatibilidade com versões anteriores do SQL Server.

Opções de monitoramento

ESTATÍSTICAS [ = porcentagem ]

Exibe uma mensagem sempre que outro percentual for concluída e é usado para medir o progresso. Se percentage for omitido, o SQL Server exibirá uma mensagem após a conclusão de cada 10%.

Limitações da Instância Gerenciada de SQL

O tamanho máximo da faixa de backup é 195 GB (tamanho máximo do blob). Aumente o número de faixas no comando de backup para reduzir o tamanho da faixa individual e permaneça dentro desse limite.

Segurança

Permissões

As permissões BACKUP DATABASE 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 na URL 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.

Exemplos

O exemplo executa um backup COPY_ONLY no Armazenamento de Blobs do Sales Microsoft Azure. O nome da Conta de armazenamento é mystorageaccount. O contêiner é chamado myfirstcontainer. Uma política de acesso armazenado foi criada com direitos de leitura, gravação, exclusão e lista. 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. Para obter informações sobre o backup do SQL Server no Armazenamento de Blobs do Azure, consulte Backup e restauração do SQL Server com o Armazenamento de Blobs do Microsoft Azure e Backup do SQL Server na URL.

BACKUP DATABASE Sales
TO URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_20160726.bak'
WITH STATS = 5, COPY_ONLY;

Você também pode fazer backup de seu banco de dados em várias faixas, e ficaria assim:

BACKUP DATABASE Sales
TO URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales-01.bak',
URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales-02.bak',
URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales-03.bak',
URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales-04.bak'
WITH COPY_ONLY;

Restaurar banco de dados

SQL Server

Instância Gerenciada de SQL

* Analytics
Platform System (PDW) *

Sistema de plataforma de análise

Cria um backup de um banco de dados do PDW (Analytics Platform System) e armazena o backup fora do dispositivo em um local de rede especificado pelo usuário. Use esta instrução com RESTORE DATABASE - Analytics Platform System para recuperação de desastre ou para copiar um banco de dados de um dispositivo para outro.

Antes de começar, confira "Adquirir e configurar um servidor de backup" na Documentação de produto do Analytics Platform System (PDW).

Há dois tipos de backup no PDW (Analytics Platform System). Um backup completo de banco de dados é um backup de um banco de dados inteiro do PDW (Analytics Platform System). Um backup diferencial de banco de dados inclui apenas as alterações feitas desde o último backup completo. Um backup de um banco de dados de usuário inclui os usuários do banco de dados e as funções do banco de dados. Um backup do banco de dados mestre master nclui os logons.

Para obter mais informações sobre backups de banco de dados do PDW (Analytics Platform System), confira "Backup e restauração" no Documentação de produto do Analytics Platform System (PDW).

Sintaxe

--Create a full backup of a user database or the master database.
BACKUP DATABASE database_name
    TO DISK = '\\UNC_path\backup_directory'
    [ WITH [ ( ]<with_options> [ ,...n ][ ) ] ]
[;]

--Create a differential backup of a user database.
BACKUP DATABASE database_name
    TO DISK = '\\UNC_path\backup_directory'
    WITH [ ( ] DIFFERENTIAL
    [ , <with_options> [ ,...n ] [ ) ]
[;]

<with_options> ::=
    DESCRIPTION = 'text'
    | NAME = 'backup_name'

Argumentos

database_name

O nome do banco de dados no qual o backup será criado. O banco de dados pode ser o master ou um banco de dados de usuário.

TO DISK = '\\UNC_path\backup_directory'

O caminho e o diretório de rede em que o PDW (Analytics Platform System) gravará os arquivos de backup. Por exemplo, \\\xxx.xxx.xxx.xxx\backups\2012\Monthly\08.2012.Mybackup.

O caminho para o nome do diretório de backup já precisa existir e estar especificado como um caminho UNC totalmente qualificado.
O diretório de backup, backup_directory, não pode existir antes da execução do comando de backup. O PDW (Analytics Platform System) criará o diretório de backup.
O caminho para o diretório de backup não pode ser um caminho local e não pode ser um local em um dos nós de dispositivo do PDW (Analytics Platform System).
O tamanho máximo do caminho UNC e do nome do diretório de backup é de 200 caracteres.
O servidor ou o host precisa ser especificado como um endereço IP. Não é possível especificá-lo como o nome de host ou do servidor.

DESCRIÇÃO = 'texto'

Especifica uma descrição textual do backup. O comprimento máximo do texto é de 255 caracteres.

A descrição é armazenada nos metadados e será exibida quando o cabeçalho do backup for restaurado com RESTORE HEADERONLY.

NOME = '_name de backup'

Especifica o nome do backup. O nome do backup pode ser diferente do nome do banco de dados.

Os nomes podem ter no máximo de 128 caracteres.
Não é possível incluir um caminho.
É necessário começar com um caractere de letra ou de número ou com um sublinhado (_). Os caracteres especiais permitidos são sublinhado (_), hífen (-) ou espaço (). Os nomes de backup não podem terminar com um caractere de espaço.
A instrução falhará se backup_name já existir no local especificado.

Esse nome é armazenado nos metadados e será exibido quando o cabeçalho do backup for restaurado com RESTORE HEADERONLY.

DIFFERENTIAL

Especifica a execução de um backup diferencial de um banco de dados de usuário. Se omitido, o padrão será um backup completo do banco de dados. O nome do backup diferencial não precisa corresponder ao nome do backup completo. Para controlar o diferencial e seu backup completo correspondente, considere usar o mesmo nome com 'completo' ou 'diferencial' anexado.

Por exemplo:

BACKUP DATABASE Customer TO DISK = '\\xxx.xxx.xxx.xxx\backups\CustomerFull';

BACKUP DATABASE Customer TO DISK = '\\xxx.xxx.xxx.xxx\backups\CustomerDiff' WITH DIFFERENTIAL;

Permissões

Requer a permissão BACKUP DATABASE ou a associação à função de banco de dados fixa db_backupoperator. O banco de dados master não pode ser submetido a backup simplesmente por um usuário normal que tenha sido adicionado à função de banco de dados fixa db_backupoperator. O banco de dados master somente pode ser submetido a backup pela sa, pelo administrador da malha ou pelos membros da função de servidor fixa sysadmin.

Requer uma conta do Windows que tenha permissão para acessar, criar e gravar no diretório de backup. O nome de conta do Windows e a senha também precisam ser armazenados no PDW (Analytics Platform System). Para adicionar essas credenciais de rede ao PDW (Analytics Platform System), use o procedimento armazenado sp_pdw_add_network_credentials – Azure Synapse Analytics.

Para obter mais informações sobre o gerenciamento de credenciais no PDW (Analytics Platform System), confira a seção Segurança.

Tratamento de erros

Erros de BACKUP DATABASE nas seguintes condições:

As permissões de usuário não são suficientes para executar um backup.
O PDW (Analytics Platform System) não tem as permissões corretas para o local de rede no qual backup será armazenado.
O banco de dados não existe.
O diretório de destino já existe no compartilhamento de rede.
O compartilhamento de rede de destino não está disponível.
O compartilhamento de rede de destino não tem espaço suficiente para o backup. O comando BACKUP DATABASE não confirma a existência de espaço em disco suficiente antes de iniciar o backup, possibilitando a geração de um erro de falta de espaço em disco durante a execução de BACKUP DATABASE. Quando ocorrer falta de espaço em disco suficiente, o PDW (Analytics Platform System) reverterá o comando BACKUP DATABASE. Para diminuir o tamanho do banco de dados, execute DBCC SHRINKLOG (PDW (Analytics Platform System))
Tentativa de iniciar um backup em uma transação.

Comentários

Antes de executar um backup de banco de dados, use DBCC SHRINKLOG (PDW (Analytics Platform System)) para diminuir o tamanho do banco de dados.

Um backup do PDW (Analytics Platform System) é armazenado como um conjunto de vários arquivos no mesmo diretório.

Um backup diferencial geralmente leva menos tempo do que um backup completo e pode ser executado com mais frequência. Quando vários backups diferenciais se baseiam no mesmo backup completo, cada diferencial inclui todas as alterações do backup diferencial anterior.

Se você cancelar um comando BACKUP, o PDW (Analytics Platform System) removerá o diretório de destino e todos os arquivos criados para o backup. Se o PDW (Analytics Platform System) perder a conectividade de rede com o compartilhamento, não será possível concluir a reversão.

Os backups completos e os backups diferenciais são armazenados em diretórios separados. Não são impostas convenções de nomenclatura para especificar que um backup completo e um backup diferencial se pertencem. Controle isso usando suas próprias convenções de nomenclatura. Como alternativa, você pode controlar isso usando a opção WITH DESCRIPTION para adicionar uma descrição e, em seguida, usando a instrução RESTORE HEADERONLY para recuperar a descrição.

Limitações

Não é possível executar um backup diferencial do banco de dados master. Apenas os backups completos do banco de dados master são compatíveis.

Não há suporte para backups de log de transações do banco de dados do sistema master.

Os arquivos de backup são armazenados em um formato adequado para a restauração do backup em um dispositivo do PDW (Analytics Platform System) usando a instrução RESTORE DATABASE - Analytics Platform System.

O backup com a instrução BACKUP DATABASE não pode ser usado para transferir dados ou informações de usuário para bancos de dados de SMP do SQL Server. Para essa funcionalidade, você pode usar o recurso de cópia de tabela remota. Para obter mais informações, confira "Cópia de tabela remota" na Documentação do produto do Analytics Platform System (PDW).

O PDW (Analytics Platform System) usa a tecnologia de backup do SQL Server para fazer backup e restaurar bancos de dados. As opções de backup do SQL Server são pré-configuradas para usar a compactação de backup. Não é possível definir as opções de backup, como compactação, soma de verificação, tamanho de bloco e contagem de buffer.

Apenas um backup de banco de dados ou uma restauração pode ser executada no dispositivo de cada vez. O PDW (Analytics Platform System) coloca os comandos de backup ou restauração na fila até que o comando de backup ou de restauração atual seja concluído.

O dispositivo de destino para restaurar o backup precisa ter pelo menos o mesmo número de nós de computação que o dispositivo de origem. O destino pode ter mais nós de computação do que o dispositivo de origem, mas não pode ter menos.

O PDW (Analytics Platform System) não rastreia o local e os nomes de backups, pois os backups são armazenados fora do dispositivo.

O PDW (Analytics Platform System) acompanha o êxito ou a falha dos backups de banco de dados.

Um backup diferencial só será permitido se o último backup completo tiver sido concluído com êxito. Por exemplo, suponha que na segunda-feira você crie um backup completo do banco de Sales dados e o backup seja concluído com êxito. Então, na terça-feira, você cria um backup completo do Sales banco de dados e ele falha. Após essa falha, não será possível criar um backup diferencial com base no backup completo de segunda-feira. Primeiro você deverá criar um backup completo com êxito antes de criar um backup diferencial.

Metadados

Essas exibições de gerenciamento dinâmico contêm informações sobre todos os backup, as restauração e as operações de carregamento. As informações persistem entre os reinícios do sistema.

Desempenho

Para executar um backup, o PDW (Analytics Platform System) primeiro faz backup dos metadados e, em seguida, executa um backup paralelo dos dados do banco de dados armazenados nos nós de computação. Os dados são copiados diretamente de cada nó de computação para o diretório de backup. Para obter o melhor desempenho para mover dados dos nós de computação para o diretório de backup, o PDW (Analytics Platform System) controla o número de nós de computação que estão copiando dados simultaneamente.

Bloqueio

Coloca um bloqueio ExclusiveUpdate no objeto DATABASE.

Segurança

Os backups do PDW (Analytics Platform System) não são armazenados no dispositivo. Portanto, sua equipe de TI é responsável por gerenciar todos os aspectos de segurança do backup. Por exemplo, isso inclui gerenciar a segurança dos dados de backup, a segurança do servidor usado para armazenar os backups e a segurança da infraestrutura de rede que conecta o servidor de backup ao dispositivo do PDW (Analytics Platform System).

Gerenciar credenciais de rede

O acesso da rede ao diretório de backup baseia-se na segurança de compartilhamento de arquivos padrão do sistema operacional. Antes de executar um backup, você precisa criar ou designar uma conta do Windows que será usada para autenticar o PDW (Analytics Platform System) no diretório de backup. Essa conta do Windows precisa ter permissão para acessar, criar e gravar no diretório de backup.

Importante

Para reduzir os riscos de segurança para seus dados, é recomendável designar uma conta do Windows exclusivamente para a finalidade de executar operações de backup e restauração. Permita que essa conta tenha permissões para o local de backup e não tenha para nenhum outro lugar.

Você precisa armazenar o nome de usuário e a senha no PDW (Analytics Platform System) executando o procedimento armazenado sp_pdw_add_network_credentials – Azure Synapse Analytics. O PDW (Analytics Platform System) usa o Gerenciador de Credenciais do Windows para armazenar e criptografar os nomes de usuário e as senhas no nó de controle e nos nós de computação. As credenciais não são submetidas a backup com o comando BACKUP DATABASE.

Para remover as credenciais de rede do PDW (Analytics Platform System), confira sp_pdw_remove_network_credentials – Azure Synapse Analytics.

Para listar todas as credenciais de rede armazenadas no PDW (Analytics Platform System), use a exibição de gerenciamento dinâmico sys.dm_pdw_network_credentials.

Exemplos

a. Adicionar as credenciais de rede para o local de backup

Para criar um backup, o PDW (Analytics Platform System) precisa ter permissão de leitura/gravação para o diretório de backup. O exemplo a seguir mostra como adicionar as credenciais para um usuário. O PDW (Analytics Platform System) armazenará essas credenciais e as usará para as operações de backup e restauração.

Importante

Por motivos de segurança, é recomendável criar uma conta de domínio exclusivamente para a finalidade de executar backups.

EXEC sp_pdw_add_network_credentials 'xxx.xxx.xxx.xxx', 'domain1\backupuser', '*****';

B. Remover as credenciais de rede do local de backup

O exemplo a seguir mostra como remover as credenciais de um usuário de domínio do PDW (Analytics Platform System).

EXEC sp_pdw_remove_network_credentials 'xxx.xxx.xxx.xxx';

C. Criar um backup completo de um banco de dados de usuário

O exemplo a seguir cria um backup completo do banco de dados de usuário de faturas. O PDW (Analytics Platform System) criará o diretório Invoices2013 e salvará os arquivos de backup no diretório \\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Full.

BACKUP DATABASE Invoices TO DISK = '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Full';

D. Criar um backup diferencial de um banco de dados de usuário

O exemplo a seguir cria um backup diferencial, que inclui todas as alterações feitas desde o último backup completo do banco de dados de Invoices. O PDW (Analytics Platform System) criará o \\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Diff diretório para armazenar os arquivos. A descrição 'Invoices 2013 differential backup' será armazenada com as informações de cabeçalho do backup.

O backup diferencial só será executado com êxito se o último backup completo do banco de dados de faturas for concluído com êxito.

BACKUP DATABASE Invoices TO DISK = '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Diff'
    WITH DIFFERENTIAL,
    DESCRIPTION = 'Invoices 2013 differential backup';

E. Criar um backup completo do banco de dados mestre

O exemplo a seguir cria um backup completo do banco de dados master e o armazena no diretório \\\xxx.xxx.xxx.xxx\backups\2013\daily\20130722\master, onde o IP é um endereço IP de rede.

BACKUP DATABASE master TO DISK = '\\xxx.xxx.xxx.xxx\backups\2013\daily\20130722\master';

O banco de dados master armazena as informações de logon do dispositivo. Para fazer backup das informações de login do dispositivo, você precisa fazer backup do master banco de dados.

O exemplo a seguir cria um backup completo do banco de dados master.

BACKUP DATABASE master TO DISK = '\\xxx.xxx.xxx.xxx\backups\2013\daily\20130722\master'
WITH (
    DESCRIPTION = 'Master Backup 20130722',
    NAME = 'login-backup'
)
;

RESTORE DATABASE – Parallel Data Warehouse

Compartilhar via

BACKUP (Transact-SQL)

Selecionar um produto

SQL Server

Sintaxe

Argumentos

DATABASE

LOG

GROUP (<database>,...n)

SERVER

METADATA_ONLY

{ database_name | @database_name_var }

<> file_or_filegroup [ ,...n ]

ARQUIVO = { logical_file_name | @logical_file_name_var }

GRUPO DE ARQUIVOS = { logical_filegroup_name | @logical_filegroup_name_var }

READ_WRITE_FILEGROUPS [ , GRUPO DE ARQUIVOS = { logical_filegroup_name | @logical_filegroup_name_var } [ ,...n ] ]

READ_WRITE_FILEGROUPS

GRUPO DE ARQUIVOS = { logical_filegroup_name | @logical_filegroup_name_var }

PARA <backup_device> [ ,...n ]

{ logical_device_name | @logical_device_name_var }

{ DISCO | FICA | URL} = { 'physical_device_name' | @physical_device_name_var | 'NUL' }

ESPELHO PARA <backup_device> [ ,...n ]

Opções WITH

CREDENTIAL

FILE_SNAPSHOT

DIFFERENTIAL

ENCRYPTION

Opções de conjunto de backup

COPY_ONLY

[ COMPRESSION [ ALGORITHM = ( { MS_XPRESS | accelerator_algorithm } ) ] | NO_COMPRESSION ]

DESCRIÇÃO = { 'texto' | @text_variable }

NOME = { backup_set_name | @backup_set_var }

{ EXPIRADO = 'data' | DIAS DE RETENÇÃO = dias }

{ METADATA_ONLY | SNAPSHOT }

Opções de conjunto de mídias

{ NOINIT | INIT }

{ NOSKIP | SKIP }

{ NOFORMAT | FORMAT }

MEDIADESCRIPTION = { text | @text_variable }

NOME_MEDIA = { media_name | @media_name_variable }

TAMANHO BLOCO = { tamanho do bloco | @blocksize_variable }

Opções de transferência de dados

CONTAGEM DE BUFFERS = { contagem de buffers | @buffercount_variable }

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable }

Opções de gerenciamento de erros

{ NO_CHECKSUM | CHECKSUM }

{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

Opções de compatibilidade

RESTART

Opções de monitoramento

ESTATÍSTICAS [ = porcentagem ]

Opções de fita

{ REWIND | NOREWIND }

{ UNLOAD | NOUNLOAD }

Opções específicas de log

{ NÃO RECUPERAÇÃO | ESPERA = undo_file_name }

NO_TRUNCATE

Sobre o trabalho com backups do SQL Server

Tipos de backup

Truncamento do log de transações

Formatando mídia de backup

Trabalhar com dispositivos de backup e conjuntos de mídia

Dispositivos de backup em um conjunto de mídias distribuído (um conjunto de distribuição)

Trabalhar com um conjunto de mídias espelhado

Famílias de mídia em conjuntos de mídia espelhados

Restaurar backups do SQL Server

Considerações adicionais sobre as opções de BACKUP

Interação de SKIP, NOSKIP, INIT e NOINIT

Compatibilidade

Comentários

Interoperabilidade

Metadados

Segurança

Permissões

Exemplos

R. Fazer backup de um banco de dados completo

B. Fazer backup do banco de dados e do log

C. Criar um backup completo de arquivos dos grupos de arquivos secundários

D. Criar um backup diferencial de arquivos dos grupos de arquivos secundários

E. Criar e fazer backup em um conjunto de mídias espelhado unifamiliar