Derniers Articles
Vous êtes ici : Accueil / Tutoriaux / Bases de données / 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

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

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.

Restore Database
 
 	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

Répondre

Votre adresse email ne sera pas publiée.

shared on wplocker.com