Script de restore sql server : récupération de la dernière sauvegarde et publication vers une base de données avec un autre nom

Détails
Affichages : 7060

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

Commentaires (2)

  • 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

    0 URL: Répondre

  • 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

    0 URL: Répondre