Backup / Restore - via T-SQL

Neste post vou descrever o método de backup compactado com código Transact-SQL com o mesmo efeito da postagem anterior de modo visual. A vantagem em codificar é de agendar a execução por qualquer aplicativo disponível, como o Agendador do Windows ou mesmo o SQL Agent nas versões em que este está instalado. E para quem estiver desenvolvendo um programa de backup, também será bastante útil para otimizar a execução.

Vale ressaltar que a versão do SQL Express não possui a compactação, visto que a base somente vai até 10Gb. O próprio backup compactado é desnecessário neste caso.

Na figura 1, apresento 5 comandos práticos para backup / restore / attach de base. Estou usando os exemplos em duas instâncias diferentes do SQL Server: 2014 (que é Express) e outra 2019 (que é Developer). Fiz as próprias descrições nos comentários do comando e relaciono abaixo o texto para você testar no seu Microsoft SQL Server Management Studio. 

O código aqui, se aplica a bases simples, sem clusterização ou agrupamento de arquivos. A sintaxe neste casos são bem simples, sem precisar alongar muito a explicação. Vale apenas alguns simples comentários de cada script:

1) Este primeiro script está sendo rodado em uma instância SQL Server Express (SQL2014). Não existe opção de compactação. Se colocar no comando a cláusula COMPRESSION, o Express acusará o erro e não procederá com o backup. Ao final do backup, o arquivo .bak terá aproximadamente o mesmo tamanho da base (mdf/ldf). Veja DBSword.bak da figura 2 (o tamanho desta base já é pequeno, somente pra exemplo).

-- 1) Script para backup SEM COMPACTACAO

BACKUP DATABASE [DBSword]  -- Nome da base

 TO DISK = 'C:\Sword_Backup\DBSword.bak'  -- Nome do diretório e arquivo físico

 WITH

   NAME = 'DBSword.bak',  -- Nome do conjunto de backup

   STATS = 5  -- Exibe uma mensagem sempre que o % for concluído, usado para medir o progresso (5% em 5%)

GO

2) Neste script restaurei a base do SQL2014 (Express) para SQL2019 (Developer) com uma nomenclatura diferente só para comparar. Aqui faço o uso do COMPRESSION no comando e podemos ver na figura 2 o arquivo DB_Sword.bak com cerca de 20% do tamanho original (bases maiores comprimem mais ainda). Este arquivo compactado pode ser restaurado diretamente, dispensando o uso de programas de compactação. Vale lembrar que um backup de base de uma instância de versão mais recente (SQL2019) não pode ser restaurada em versão anterior (SQL2014).

-- 2) Script para backup COMPACTADO pelo SQL Server

BACKUP DATABASE [DB_Sword]  -- Nome da base

 TO DISK = 'C:\Sword_Backup\DB_Sword.bak'  -- Nome do diretório e arquivo físico do backup

 WITH 

   NAME = 'DB_Sword.bak',  -- Nome do conjunto de backup

   STATS = 5,  -- Exibe uma mensagem sempre que % for concluído, usado para medir o progresso (de 5% em 5%)

   COMPRESSION -- Efetua a compactação do arquivo de backup

GO

3) Aqui temos a rotina de restauração da base. Funciona independente do COMPRESSION. 

 -- 3) Script para restore

 RESTORE DATABASE [DBSword_2] -- Nome da base

  FROM DISK = 'C:\Sword_Backup\DBSword.Bak' -- Nome do diretório e arquivo físico do backup

  WITH 

    STATS = 5,  -- mede o progresso em cada percentual (de 5% em 5%)

    REPLACE,     -- Substitui os arquivos físicos, caso eles já existam

    MOVE 'DBSword_Log' TO 'C:\Sword_SQL\DBSword.ldf',  -- Associa o nome lógico ao LDF 

    MOVE 'DBSword' TO 'C:\Sword_SQL\DBSword.mdf'  -- Associa o nome lógico dos  ao MDF

GO

4) e 5) As bases também podem ser copiadas e restauradas por de/attach. Neste caso, não geramos um .bak, mas copiamos diretamente o .mdf/.ldf, o que economiza mais espaço do que o backup compactado. Você não pode copiar o mdf/ldf enquanto o serviço estiver ligado, a não ser que faça um sp_detach_db para que a base seja desconectada da instância em execução (ou pare o serviço do SQL Server). Depois copia-se o mdf/ldf para outra máquina e faz o sp_attach_db para conectar na nova (ou mesma) instância. A copia durante o serviço ligado causa inconsistência na restauração, visto que o ldf já está desatualizado em relação ao mdf, por isso a necessidade de parar o serviço ou "desatachar" a base.

-- 4) Desligar uma base ("Deatachar") 

sp_detach_db 'DBSword_2'

GO

-- 5) "Atachar" base com MDF e LDF

sp_attach_db 'DBSword_2', -- Nome da base

  'C:\Sword_SQL\DBSword.mdf',  -- Nome do diretório e arquivo físico do MDF (Master Database File)

  'C:\Sword_SQL\DBSword.ldf'   -- Nome do diretório e arquivo físico do LDF (Log Database File)

GO

Figura 1: Scripts de backup / restore / attach no SQL Server Management Studio 

Figura 2: Mídia gravada na opção sem compactação (DBSword.bak) e compactada (DB_Sword.bak)

Caso seja necessário mais explicações, deixe seu comentário e poderei melhorar um pouco mais o texto com informações mais relevantes que por ventura estejam faltando.

Mais opções e detalhes para efetuar backup via Transact-SQL, acesse:

https://learn.microsoft.com/pt-br/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver16