Vote utilisateur: 5 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles activesEtoiles actives
 

Un script de restore database ms sql server avec récupération de la dernière sauvegarde effectuée sur le serveur (ou récupération directe d'une sauvegarde via un chemin).

Ce script est capable de récupérer les différents fichiers de la sauvegarde et de construire une commande de restauration en changeant la destination des fichiers cibles 

 

Il ne vous restera plus qu'à valoriser les noms de la base source, de la copie et le chemin du répertoire où restorer les fichiers mdf et ldf :

USE [master]
  GO
 
 
  CREATE PROC [dbo].[sp_RestoreDB]
    @p_strDBNameTo SYSNAME,
    @p_strDBNameFrom SYSNAME,
    @p_strFQNRestoreFileName VARCHAR(255) = '',
    @p_Debug BIT=0
  AS 
  BEGIN
    DECLARE 
    @v_strDBFilename VARCHAR(100),
    @v_strDBLogFilename VARCHAR(100),
    @v_strDBDataFile VARCHAR(100),
    @v_strDBLogFile VARCHAR(100),
    @v_strExecSQL NVARCHAR(2000),
    @v_strExecSQL1 NVARCHAR(2000),
    @v_strMoveSQL NVARCHAR(4000),
    @v_strREPLACE NVARCHAR(50),
    @v_strTEMP NVARCHAR(1000),
    @v_strListSQL NVARCHAR(4000),
    @v_strRestFLSQL NVARCHAR(4000),
    @v_strServerVersion NVARCHAR(20)
 
    SET @v_strREPLACE = ''  
    IF exists (SELECT name FROM sys.databases WHERE name = @p_strDBNameTo)
    SET @v_strREPLACE = ', REPLACE'
 
     IF @p_strFQNRestoreFileName='' 
    BEGIN
     SET @p_strFQNRestoreFileName= (SELECT TOP (1) physical_device_name
        FROM         
          msdb.dbo.backupmediafamily AS BUMF 
          INNER JOIN msdb.dbo.backupmediaset AS BUMS ON BUMF.media_set_id = BUMS.media_set_id 
          INNER JOIN msdb.dbo.backupfile AS BUF 
          INNER JOIN msdb.dbo.backupset AS BUS ON BUF.backup_set_id = BUS.backup_set_id ON BUMS.media_set_id = BUS.media_set_id
        WHERE type='D' and logical_name=@p_strDBNameFrom
    ORDER BY backup_start_date DESC)  
    END
 
    --PRINT 'Physical File To Restore : ' + @p_strFQNRestoreFileName
 
    SET @v_strListSQL = ''
    SET @v_strListSQL = @v_strListSQL + 'IF (EXISTS (SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''##FILE_LIST''))'
    SET @v_strListSQL = @v_strListSQL + 'BEGIN'
    SET @v_strListSQL = @v_strListSQL + '  DROP TABLE ##FILE_LIST '
    SET @v_strListSQL = @v_strListSQL + 'END '
 
    SET @v_strListSQL = @v_strListSQL + 'CREATE TABLE ##FILE_LIST ('
    SET @v_strListSQL = @v_strListSQL + '  LogicalName VARCHAR(64),'
    SET @v_strListSQL = @v_strListSQL + '  PhysicalName VARCHAR(130),'
    SET @v_strListSQL = @v_strListSQL + '  [Type] VARCHAR(1),'
    SET @v_strListSQL = @v_strListSQL + '  FileGroupName VARCHAR(64),'
    SET @v_strListSQL = @v_strListSQL + '  Size DECIMAL(20, 0),'
    SET @v_strListSQL = @v_strListSQL + '  MaxSize DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '  FileID bigint,'
    SET @v_strListSQL = @v_strListSQL + '  CreateLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '  DropLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '  UniqueID UNIQUEIDENTIFIER,'
    SET @v_strListSQL = @v_strListSQL + '  ReadOnlyLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '  ReadWriteLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '  BackupSizeInBytes DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '  SourceBlockSize INT,'
    SET @v_strListSQL = @v_strListSQL + '  filegroupid INT,'
    SET @v_strListSQL = @v_strListSQL + '  loggroupguid UNIQUEIDENTIFIER,'
    SET @v_strListSQL = @v_strListSQL + '  differentialbaseLSN DECIMAL(25,0),'
    SET @v_strListSQL = @v_strListSQL + '  differentialbaseGUID UNIQUEIDENTIFIER,'
    SET @v_strListSQL = @v_strListSQL + '  isreadonly BIT,'
    SET @v_strListSQL = @v_strListSQL + '  ispresent BIT'
 
    SELECT @v_strServerVersion =CAST(SERVERPROPERTY ('PRODUCTVERSION') AS NVARCHAR) 
 
    IF @v_strServerVersion LIKE '1%.%' 
    BEGIN
      SET @v_strListSQL = @v_strListSQL + ', TDEThumbpr DECIMAL'
      PRINT 'SQL Server Version : ' + @v_strServerVersion
    END
 
 
 
    SET @v_strListSQL = @v_strListSQL + ')'
    --PRINT @v_strListSQL 
    EXEC (@v_strListSQL)
 
    SET @v_strRestFLSQL = 'RESTORE FILELISTONLY FROM DISK = N''' + @p_strFQNRestoreFileName + ''''
 
    --PRINT @v_strRestFLSQL
 
    INSERT INTO ##FILE_LIST EXEC (@v_strRestFLSQL)
 
 
 
    DECLARE curFileLIst CURSOR FOR 
    SELECT 'MOVE N''' + LogicalName + ''' TO N''' + REPLACE(PhysicalName, @p_strDBNameFrom, @p_strDBNameTo) + ''''
     FROM ##FILE_LIST
 
    SET @v_strMoveSQL = ''
 
    OPEN curFileList 
    FETCH NEXT FROM curFileList INTO @v_strTEMP
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @v_strMoveSQL = @v_strMoveSQL + @v_strTEMP + ', '
    FETCH NEXT FROM curFileList INTO @v_strTEMP
    END
 
    CLOSE curFileList
    DEALLOCATE curFileList
 
    PRINT 'Killing active connections to the "' + @p_strDBNameTo + '" database'
 
    -- Create the sql to kill the active database connections
    SET @v_strExecSQL = ''
    SELECT  @v_strExecSQL = @v_strExecSQL + 'kill ' + CONVERT(CHAR(10), spid) + ' '
    FROM   master.dbo.sysprocesses
    WHERE  DB_NAME(dbid) = @p_strDBNameTo AND DBID  0 AND spid  @@SPID
 
    EXEC (@v_strExecSQL)
 
    PRINT 'Restoring "' + @p_strDBNameTo + '" database from "' + @p_strFQNRestoreFileName
 
    SET @v_strExecSQL = 'RESTORE DATABASE [' + @p_strDBNameTo + ']'
    SET @v_strExecSQL = @v_strExecSQL + ' FROM DISK = ''' + @p_strFQNRestoreFileName + ''''
    SET @v_strExecSQL = @v_strExecSQL + ' WITH FILE = 1,'
    SET @v_strExecSQL = @v_strExecSQL + @v_strMoveSQL
    SET @v_strExecSQL = @v_strExecSQL + ' NOREWIND, '
    SET @v_strExecSQL = @v_strExecSQL + ' NOUNLOAD '
    SET @v_strExecSQL = @v_strExecSQL + @v_strREPLACE
 
 
    PRINT '---------------------------'
    PRINT @v_strExecSQL
    PRINT '---------------------------'
 
    IF @p_Debug=0
    BEGIN
     EXEC SP_EXECUTESQL @v_strExecSQL
    END
  END
 
 
  GO