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

 
 	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