Como automatizar o processo de restauração de múltiplos bancos de dados

Aqui está a explicação detalhada do que esse script faz, criando um "mercado de conhecimento" sobre cada parte do script e os conceitos-chave para que você possa entender e utilizá-lo de forma eficaz. Isso servirá como um guia completo para quem precisa usar o script ou adaptá-lo.

### *Visão Geral do Propósito do Script:*

Este script SQL Server foi projetado para *automatizar o processo de restauração de múltiplos bancos de dados* a partir de um diretório contendo arquivos de backup. Ele identifica todos os arquivos .bak na pasta, lê os metadados necessários dos backups e restaura cada banco de dados para um local especificado, utilizando os nomes lógicos corretos para os arquivos de dados e logs.

O script também lida com as *diferenças nos caminhos dos arquivos* entre o servidor de origem (onde o backup foi feito) e o servidor de destino (onde o banco de dados está sendo restaurado), garantindo que os arquivos sejam movidos para os locais corretos.

---

### *Divisão do Script e Conceitos-Chave:*

#### 1. *Ativando o xp_cmdshell*:

- *Propósito*: Permitir que o SQL Server execute comandos do sistema operacional, como listar arquivos em um diretório.

- *Comando*:

     EXEC sp_configure 'xp_cmdshell', 1;

     RECONFIGURE;

- *Explicação*: O script precisa acessar comandos do sistema de arquivos para listar os arquivos de backup no diretório especificado.

#### 2. *Declaração de Variáveis*:

- *Propósito*: Essas variáveis armazenam os caminhos importantes das pastas e outras informações que serão usadas ao longo do script.

- *Principais Variáveis*:

- @BackupFolder: Pasta onde os arquivos de backup (.bak) estão localizados.

- @DataFolder: Pasta onde os arquivos de dados serão restaurados.

- @LogFolder: Pasta onde os arquivos de log serão restaurados.

   DECLARE @BackupFolder NVARCHAR(255) = 'C:\Backups\'

   DECLARE @DataFolder NVARCHAR(255) = 'D:\SQLData\'

   DECLARE @LogFolder NVARCHAR(255) = 'D:\SQLLogs\'

*Conceito*:

- *Backup*: Um arquivo .bak contém uma cópia de um banco de dados que pode ser usada para restaurar o banco de dados em caso de falha ou migração.

- *Arquivos de Dados e Log*: Cada banco de dados SQL Server consiste em arquivos de dados (geralmente .mdf ou .ndf) e arquivos de log (geralmente .ldf). A cláusula MOVE no comando RESTORE relocaliza esses arquivos para as pastas especificadas no servidor de destino.

#### 3. *Recuperar Lista de Arquivos de Backup*:

- *Propósito*: Usar o xp_cmdshell para recuperar os nomes de todos os arquivos de backup (.bak) na pasta especificada e armazená-los em uma tabela temporária.

- *Comando*:

     SET @cmd = 'dir ' + @BackupFolder + '*.bak /b'

     INSERT INTO #BackupFiles (FileName)

     EXEC xp_cmdshell @cmd

- *Conceito*:

- *Tabela Temporária*: #BackupFiles é usada para armazenar temporariamente os nomes dos arquivos de backup.

- *xp_cmdshell*: Este procedimento de sistema executa comandos do sistema operacional de dentro do SQL Server. Aqui, ele lista todos os arquivos .bak.

#### 4. *Usar um Cursor para Iterar pelos Arquivos de Backup*:

- *Propósito*: O cursor percorre cada arquivo de backup encontrado na pasta, processando cada um por vez.

- *Conceito-chave*:

- *Cursor*: Um objeto de banco de dados usado para recuperar, manipular e processar um conjunto de resultados linha a linha.

     DECLARE BackupCursor CURSOR FOR

     SELECT FileName

     FROM #BackupFiles

     WHERE FileName IS NOT NULL

#### 5. *Recuperar Metadados dos Backups*:

- *Propósito*: Usar RESTORE HEADERONLY e RESTORE FILELISTONLY para recuperar metadados sobre o backup, como o nome original do banco de dados e a estrutura dos arquivos (nomes lógicos e físicos dos arquivos de dados e log).

- *Comandos*:

     INSERT INTO #HeaderInfo (DatabaseName)

     EXEC ('RESTORE HEADERONLY FROM DISK = ''' + @BackupFolder + @FileName + '''')

     INSERT INTO #FileList (LogicalName, PhysicalName, Type)

     EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @BackupFolder + @FileName + '''')

- *Conceitos*:

- *RESTORE HEADERONLY*: Recupera metadados sobre o backup, como o nome do banco de dados, tipo de backup, data de criação, etc.

- *RESTORE FILELISTONLY*: Recupera os nomes lógicos e físicos dos arquivos do banco de dados (dados e log) armazenados no backup.

#### 6. *Construir o Comando de Restauração Dinâmica*:

- *Propósito*: Para cada backup, construir dinamicamente e executar o comando RESTORE DATABASE com cláusulas MOVE para ajustar os caminhos dos arquivos.

- *Exemplo*:

     SET @RestoreSQL = '

     RESTORE DATABASE [' + @OriginalDatabaseName + '] 

     FROM DISK = ''' + @BackupFolder + @FileName + ''' 

     WITH MOVE ''' + @LogicalDataName + ''' TO ''' + @DataFolder + RIGHT(@PhysicalDataName, CHARINDEX('\', REVERSE(@PhysicalDataName))-1) + ''', 

          MOVE ''' + @LogicalLogName + ''' TO ''' + @LogFolder + RIGHT(@PhysicalLogName, CHARINDEX('\', REVERSE(@PhysicalLogName))-1) + ''', 

          REPLACE;'

- *Conceitos*:

- *RESTORE DATABASE*: Restaura o banco de dados a partir do arquivo de backup.

- *Cláusula MOVE*: Redireciona os arquivos de dados e log para novos locais no servidor de destino.

- *Cláusula REPLACE*: Sobrescreve qualquer banco de dados existente com o mesmo nome no servidor de destino.

#### 7. *Execução do Comando de Restauração*:

- *Propósito*: Executar o comando de restauração gerado dinamicamente.

- *Comando*:

     EXEC sp_executesql @RestoreSQL

- *Conceito*:

- *sp_executesql*: Permite que o SQL Server execute dinamicamente uma string de código SQL.

---

### *Saída e Resultados Esperados*:

- *Bancos de Dados Restaurados*: Cada arquivo .bak na pasta especificada será restaurado como um banco de dados no servidor de destino. O nome original do banco de dados será preservado.

- *Mudanças de Localização dos Arquivos*: Os locais físicos dos arquivos de dados e log serão modificados conforme os caminhos @DataFolder e @LogFolder fornecidos no script.

- *Automação*: O processo é totalmente automatizado, não exigindo intervenção manual após a execução do script.

### *Termos Importantes*:

- *Backup (.bak)*: Um arquivo de backup que contém os dados e arquivos de log de um banco de dados para restauração.

- *Nome Lógico*: O nome interno de um arquivo de banco de dados dentro do SQL Server (ex.: MeuBanco_Dados).

- *Nome Físico*: O caminho completo e o nome do arquivo do banco de dados no disco (ex.: D:\SQLData\MeuBanco.mdf).

- *xp_cmdshell*: Comando usado para executar comandos em nível de sistema operacional a partir do SQL Server.

- *RESTORE*: Um comando SQL Server que restaura um banco de dados a partir de um arquivo de backup.

- *Cláusula MOVE*: Redireciona os arquivos restaurados para diferentes locais no servidor de destino.

---

### *Conclusão*:

Este script oferece uma forma automatizada e robusta de restaurar vários bancos de dados a partir de arquivos de backup, lidando com diferentes caminhos de arquivos entre os servidores de origem e destino. O uso de SQL dinâmico garante flexibilidade, e o script pode ser modificado conforme as necessidades do ambiente.

Se precisar de mais esclarecimentos ou ajustes, é só avisar!

#### . *Script completo*:

-- Enable xp_cmdshell if necessary

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE;

-- Declare variables for folder paths

DECLARE @BackupFolder NVARCHAR(255) = 'C:\Backups\' -- Folder containing backup files

DECLARE @DataFolder NVARCHAR(255) = 'D:\SQLData\' -- New folder for data files on the destination server

DECLARE @LogFolder NVARCHAR(255) = 'D:\SQLLogs\' -- New folder for log files on the destination server

DECLARE @FileName NVARCHAR(255)

DECLARE @OriginalDatabaseName NVARCHAR(255)

DECLARE @LogicalDataName NVARCHAR(255)

DECLARE @LogicalLogName NVARCHAR(255)

DECLARE @PhysicalDataName NVARCHAR(255)

DECLARE @PhysicalLogName NVARCHAR(255)

DECLARE @RestoreSQL NVARCHAR(MAX)

DECLARE @cmd NVARCHAR(255)

-- Create a temporary table to store file names

CREATE TABLE #BackupFiles (FileName NVARCHAR(255))

-- Use xp_cmdshell to list all .bak files in the folder and insert into the temporary table

SET @cmd = 'dir ' + @BackupFolder + '*.bak /b'

INSERT INTO #BackupFiles (FileName)

EXEC xp_cmdshell @cmd

-- Cursor to loop through all .bak files

DECLARE BackupCursor CURSOR FOR

SELECT FileName

FROM #BackupFiles

WHERE FileName IS NOT NULL

OPEN BackupCursor

FETCH NEXT FROM BackupCursor INTO @FileName

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Create a temporary table to hold only relevant columns from RESTORE HEADERONLY

    CREATE TABLE #HeaderInfo (

        DatabaseName NVARCHAR(128) -- Only retrieve DatabaseName

    )

    -- Dynamically execute RESTORE HEADERONLY and only select the DatabaseName column

    INSERT INTO #HeaderInfo (DatabaseName)

    EXEC ('RESTORE HEADERONLY FROM DISK = ''' + @BackupFolder + @FileName + '''')

    -- Extract the database name

    SELECT @OriginalDatabaseName = DatabaseName FROM #HeaderInfo

    -- Create a temporary table to hold only the relevant columns from RESTORE FILELISTONLY

    CREATE TABLE #FileList (

        LogicalName NVARCHAR(128),

        PhysicalName NVARCHAR(260),

        Type CHAR(1)

    )

    -- Dynamically capture only the LogicalName, PhysicalName, and Type columns for the files

    INSERT INTO #FileList (LogicalName, PhysicalName, Type)

    EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @BackupFolder + @FileName + '''')

    -- Get the logical and physical names for the data and log files

    SELECT @LogicalDataName = LogicalName, @PhysicalDataName = PhysicalName

    FROM #FileList

    WHERE Type = 'D' -- Data file

    SELECT @LogicalLogName = LogicalName, @PhysicalLogName = PhysicalName

    FROM #FileList

    WHERE Type = 'L' -- Log file

    -- Create the dynamic restore SQL statement with the MOVE clause to change the physical paths

    SET @RestoreSQL = '

    RESTORE DATABASE [' + @OriginalDatabaseName + '] 

    FROM DISK = ''' + @BackupFolder + @FileName + ''' 

    WITH MOVE ''' + @LogicalDataName + ''' TO ''' + @DataFolder + RIGHT(@PhysicalDataName, CHARINDEX('\', REVERSE(@PhysicalDataName))-1) + ''', 

         MOVE ''' + @LogicalLogName + ''' TO ''' + @LogFolder + RIGHT(@PhysicalLogName, CHARINDEX('\', REVERSE(@PhysicalLogName))-1) + ''', 

         REPLACE;'

    -- Execute the dynamic restore command

    EXEC sp_executesql @RestoreSQL

    -- Clean up for the next iteration

    DROP TABLE #FileList

    DROP TABLE #HeaderInfo

    FETCH NEXT FROM BackupCursor INTO @FileName

END

CLOSE BackupCursor

DEALLOCATE BackupCursor

-- Drop the temporary table for backup files

DROP TABLE #BackupFiles

-- Disable xp_cmdshell if necessary

-- EXEC sp_configure 'xp_cmdshell', 0;

-- RECONFIGURE;