Il blog di Andrea Benedetti

if object_id('dbo.up_verifyBackup') IS NOT NULL
   drop procedure dbo.up_verifyBackup
GO

create procedure dbo.up_verifyBackup
(
    @pathBackupDb nvarchar(200),
    @pathTestRestore nvarchar(200)
)
as
set nocount on

/*
Parametri in ingresso:

@pathTestRestore: il path dove voglio andare a scrivere i file che compongono il db da restorare
@pathBackupDb: il path completo del backup di cui voglio fare test di restore


Esempio di esecuzione:

exec dbo.up_verifyBackup 
        @pathBackupDb = 'C:\myBackupFolder\myBackup.bak',
        @pathTestRestore  = 'C:\myFolder'
*/

/* dichiarazione variabili d'appoggio */
declare @tsql nvarchar(4000)
declare @databaseName varchar(36) 
declare @posizioneVirgola int

print convert(varchar(10), GETDATE(), 103) + ' ' + convert(varchar(8), GETDATE(), 108) + ' Impostazione variabili'

/*
tengo traccia della versione di SQL Server
9 = sql server 2005
10 = sql server 2008

questo perchè il risultato ritornato dalla RESTORE FILELISTONLY
è differente tra le due versioni
*/
declare @sqlServerVersion tinyint
select @sqlServerVersion = 
    LEFT(CAST( SERVERPROPERTY('productversion') as varchar(15)), CHARINDEX ('.', CAST( SERVERPROPERTY('productversion') as varchar(15))) -1) 

print '@sqlServerVersion = ' + cast(@sqlServerVersion as varchar(2))

/* comando per recuperare i nomi logici dei file database */
set @tsql = 'RESTORE FILELISTONLY FROM DISK = ''' + @pathBackupDb + ''''

print '@pathTestRestore = ' + @pathTestRestore
print '@pathBackupDb = ' + @pathBackupDb
print '@tsql = ' + @tsql
print ''

/* 
creo una tabella temporanea che conterrà il risultato della RESTORE FILELISTONLY 
devo interrogarla successivamente per recuperare i nomi file
*/
if object_id('tempdb..#fileListOnly') is not null
    drop table #fileListOnly

create table #fileListOnly
    (
        LogicalName    nvarchar(128),
        PhysicalName nvarchar(260),
        Type char(1),
        FileGroupName nvarchar(128),
        Size numeric(20,0),
        MaxSize numeric(20,0),
        FileID bigint,
        CreateLSN numeric(25,0),
        DropLSN    numeric(25,0) NULL,
        UniqueID uniqueidentifier,
        ReadOnlyLSN    numeric(25,0) NULL,
        ReadWriteLSN numeric(25,0) NULL,
        BackupSizeInBytes bigint,
        SourceBlockSize    int,
        FileGroupID    int,
        LogGroupGUID uniqueidentifier NULL,
        DifferentialBaseLSN    numeric(25,0) NULL,
        DifferentialBaseGUID uniqueidentifier,
        IsReadOnly bit,
        IsPresent bit
    )
    
if (@sqlServerVersion = 10)
begin
    alter table #fileListOnly
    add TDEThumbprint nvarchar(max) /* nuovo in SQL Server 2008 */
end

/* eseguo la RESTORE FILELISTONLY */
print convert(varchar(10), GETDATE(), 103) + ' ' + convert(varchar(8), GETDATE(), 108) + ' Esecuzione @tsql'
print ''
insert #fileListOnly
exec sp_executesql @tsql

/*
se voglio vedere il risultato della RESTORE FILELISTONLY:
select * from #fileListOnly
*/

/* costruisco il comando di restore */
set @databaseName  = (select cast(NEWID() as varchar(36)))
print '@databaseName = ' + @databaseName


set @tsql =
' RESTORE DATABASE [' + @databaseName + '] 
  FROM  DISK = ''' + @pathBackupDb + '''
  WITH '

select @tsql = @tsql + ', MOVE N''' + logicalName + ''' TO N''' + @pathTestRestore + '\testRestore_' +
        RIGHT ( PhysicalName,  CHARINDEX ( '\', REVERSE( PhysicalName ) ) - 1) + ''' '
from #fileListOnly

/* elimino la prima virgola */
set @posizioneVirgola = (select CHARINDEX(',', @tsql))
set @tsql = STUFF(@tsql, @posizioneVirgola, 1,'')

/* vedo il comando di restore */
print '@tsql = ' + @tsql
print ''

/*
se voglio mandare in esecuzione il comando di restore:
*/
print convert(varchar(10), GETDATE(), 103) + ' ' + convert(varchar(8), GETDATE(), 108) + ' Esecuzione RESTORE'
print ''
exec sp_executesql @tsql


/*
se tutto va a buon fine significa che il backup è valido
posso droppare il db appena creato:
*/
print convert(varchar(10), GETDATE(), 103) + ' ' + convert(varchar(8), GETDATE(), 108) + ' Esecuzione DROP DATABASE'
print ''
set @tsql = 'drop database [' + @databaseName + ']'
exec sp_executesql @tsql
print '@tsql = ' + @tsql

print convert(varchar(10), GETDATE(), 103) + ' ' + convert(varchar(8), GETDATE(), 108) + ' Fine operazioni'

go

backup database test
to disk = 'c:\temp\myBackupTest.bak'

exec dbo.up_verifyBackup 
        @pathBackupDb = 'c:\temp\myBackupTest.bak', 
        @pathTestRestore  = 'C:\temp'
go