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 

 

Ajouter vos commentaires

Poster un commentaire en tant qu'invité

0 / 30000 Restriction des caractères
Votre texte doit contenir entre 10 et 30000 caractères
Vos commentaires sont soumis à la modération de l'administrateur.
conditions d'utilisation.

Participant à cette conversation

  • Une nouvelle version pour faire une restore à partir du dernier Full et du dernier différentiel (paramétrable)

    USE [master]
    GO

    /****** Object: StoredProcedure [dbo].[sp_RestoreDB] Script Date: 09/21/2012 14:40:38 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RestoreDB]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[sp_RestoreDB] GO

    USE [master] GO

    /****** Object: StoredProcedure [dbo].[sp_RestoreDB] Script Date: 09/21/2012 14:40:38 ******/ SET ANSI_NULLS ON GO

    SET QUOTED_IDENTIFIER ON GO

    CREATE PROC [dbo].[sp_RestoreDB] @p_strDBNameTo SYSNAME, @p_strDBNameFrom SYSNAME, @p_strFQNRestoreFileName VARCHAR(255) = '', @p_strRSTDifferential BIT = 1, @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_strRecoverySQL NVARCHAR(100), @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 database_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 Backup Full of "' + @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 SET @v_strExecSQL = @v_strExecSQL + ' NORECOVERY'

    PRINT '---------------------------' PRINT @v_strExecSQL PRINT '---------------------------'

    IF @p_Debug=0 BEGIN EXEC sp_executesql @v_strExecSQL END IF @p_strRSTDifferential = 1 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='I' and database_name=@p_strDBNameFrom order by backup_start_date desc) SET @v_strExecSQL = 'RESTORE DATABASE [' + @p_strDBNameTo + ']' SET @v_strExecSQL = @v_strExecSQL + ' FROM DISK = ''' + @p_strFQNRestoreFileName + '''' SET @v_strExecSQL = @v_strExecSQL + ' WITH NORECOVERY'

    PRINT '---------------------------' PRINT @v_strExecSQL PRINT '---------------------------'

    IF @p_Debug=0 BEGIN EXEC sp_executesql @v_strExecSQL END END

    SET @v_strExecSQL = 'RESTORE DATABASE [' + @p_strDBNameTo + '] WITH RECOVERY'

    PRINT '---------------------------' PRINT @v_strExecSQL PRINT '---------------------------'

    IF @p_Debug=0 BEGIN EXEC sp_executesql @v_strExecSQL END

    END

    GO

  • Utilisation pour Full + Diff :

    exec [dbo].[sp_RestoreDB]  @p_strDBNameTo = 'SAS_SAP_BACKUP',  @p_strDBNameFrom ='SAS_SAP', @p_Debug =0


    Utilisation pour Full Only :
    exec [dbo].[sp_RestoreDB]  @p_strDBNameTo = 'SAS_SAP_BACKUP',  @p_strDBNameFrom ='SAS_SAP', @p_Debug =0, @p_strRSTDifferential=0