SP_Restore Database

Posted on março 10, 2011

1


 

Sempre nos preocupamos com nossa rotina de backup, mas e nossa rotina de restore? Na maioria das vezes só nos preocupamos com ela no momento em que estamos com problema , um trabalho bem desgastante, cansativo e na maioria das vezes doloroso é voltar backup de bases com grandes backups, ainda mais sé tivermos rotinas nas quais temos muitos backups de log para restaurar. Para facilitar nossa vida eu e o Luti (http://luticm.blogspot.com/) criamos um script que realiza o mapeamento de todos os backups necessários para restaurar a base de dados.

A procedure foi dividida com duas opções, quando passamos 0, o script faz a busca dos backups realizados nas views msdb.dbo.backupset e msdb.dbo.backupmediafamily e realiza o script completo para o restore da base de dados. Mas e quando queremos levar o BACKUP da base para outro servidor ou instância de dados? Então especificamos na procedure 1, que realiza a criação do mesmo script, só que dessa vez com a opção WITH MOVE no restore.

 

Segue procedure com explicações e exemplo de utilização:

/*

Procedure que mapeia todos os backup de um banco de dados necessários para realizar o restore do banco de dados.

Essa procedure apresenta dois tipos de restore. O restore do banco de dados WITH MOVE e o restore normal. Ela apresenta uma variável @Move que diz

que tipo de RESTORE DATABASE você deseja realizar

*/

 

/*

Restore Automático Banco de Dados

 

Dados de Entrada: @nome_db – Nome do Banco de Dados

                  @Move – 1 = Restore Database With Move

                        – 0 = Restore Database

 

–exemplo

EXEC sp_restore ‘[DatabaseName]’,1|0

*/

 

  

–Procedure Restore Automático

CREATE PROCEDURE sp_restore

 

–declaração de variáveis entrada de informações

@nome_db NVARCHAR(30),

@Move BIT

AS

 

–declaração de variáveis

DECLARE @bkp_ini DATETIME

DECLARE @backup_set_id_inicio INT

DECLARE @backup_set_id_fim INT

 

–capturando o último backup

SELECT @backup_set_id_inicio = MAX(backup_set_id) –inicialização da variável backup_set_id_inicio

FROM  msdb.dbo.backupset

WHERE database_name = @nome_db AND type = ‘D’

–fechando o restore com WITH RECOVERY

IF @backup_set_id_fim IS NULL SET @backup_set_id_fim = 999999999 ;

 

–restore automático

–montagem das informações do RESTORE

WITH Backups AS

(

SELECT

      backup_set_id,

      b.type,

      CASE

            WHEN B.type IN (‘D’, ‘I’) THEN ‘RESTORE DATABASE ‘ + @nome_db + ‘ FROM DISK = ”’ 

               + mf.physical_device_name + ”’ WITH NORECOVERY ‘               

        WHEN B.type = ‘L’ THEN ‘RESTORE LOG ‘ + @nome_db + ‘ FROM DISK = ”’ 

               + mf.physical_device_name + ”’ WITH NORECOVERY’

    END AS Instrucao,

    DENSE_RANK() OVER (PARTITION BY B.TYPE ORDER BY B.Backup_Set_id desc) AS Ranking,

    @Move AS WithMove   

FROM    msdb.dbo.backupset b,

        msdb.dbo.backupmediafamily mf

WHERE   b.media_set_id = mf.media_set_id

        AND b.database_name = @nome_db

        AND b.backup_set_id >= @backup_set_id_inicio AND b.backup_set_id < @backup_set_id_fim

        AND b.type in (‘L’, ‘D’, ‘I’)

UNION

SELECT 999999999 AS backup_set_id, ‘Z’, ‘RESTORE DATABASE ‘ + @nome_db + ‘ WITH RECOVERY’, 1, 0

),

–restore automático with move

Arquivos AS (

      SELECT

            *,

            @backup_set_id_inicio as  Backup_set_id,

            ‘D’ AS [Type],

            1 AS WithMove

      FROM (

      SELECT ‘,MOVE ‘ +

               ”” + logical_name + ”” + ‘ TO ‘ + ”’***\’ + RIGHT(physical_name, CHARINDEX(‘\’,REVERSE(physical_name)) 1) + ””

      FROM    msdb.dbo.backupset b,

                  msdb.dbo.backupmediafamily mf,

                  msdb.dbo.backupfile bf

      WHERE   b.media_set_id = mf.media_set_id AND

                  b.backup_set_id = bf.backup_set_id

                  AND b.database_name = @nome_db

                  AND b.backup_set_id = @backup_set_id_inicio   

      FOR XML PATH()) X(Caminho)

)

–montagem das informações

SELECT

      Instrucao + ISNULL(A.Caminho, )

FROM Backups AS B

left outer JOIN Arquivos AS A

ON B.backup_set_id = A.backup_set_id

      AND B.WithMove = A.WithMove

      AND B.type = A.Type

WHERE (B.type IN (‘D’, ‘I’, ‘Z’) AND Ranking = 1)

      OR (B.type = ‘L’ and B.backup_set_id > (SELECT MAX(C.backup_set_id) FROM Backups AS C WHERE C.TYPE IN (‘I’, ‘D’) AND Ranking = 1))

ORDER BY B.type, B.Ranking DESC

GO

 

 

 Exemplo de Utilização:

EXEC sp_restore ‘[DatabaseName]’,1|0

Posted in: Script's