SET NOCOUNT on
DECLARE 
    @cmd varchar(8000), 
    @sql nvarchar(MAX), 
    @folder_bak varchar(max) = 'D:\Backups', 
    @folder_files varchar(max)= 'D:\SQLData', 
    @bak_name nvarchar(max);

declare @files table (ID int IDENTITY, FileName nvarchar(max) NULL)

declare @header table (
	BackupName nvarchar(128),
	BackupDescription nvarchar(255),
	BackupType tinyint,
	ExpirationDate datetime,
	Compressed tinyint,
	Position smallint,
	DeviceType tinyint,
	UserName nvarchar(128),
	ServerName nvarchar(128),
	DatabaseName nvarchar(128),
	DatabaseVersion int,
	DatabaseCreationDate datetime,
	BackupSize bigint,
	FirstLSN decimal(25,0),
	LastLSN decimal(25,0),
	CheckpointLSN decimal(25,0),
	DatabaseBackupLSN decimal(25,0),
	BackupStartDate datetime,
	BackupFinishDate datetime,
	SortOrder smallint,
	CodePage smallint,
	UnicodeLocaleId int,
	UnicodeComparisonStyle int,
	CompatibilityLevel tinyint,
	SoftwareVendorId int,
	SoftwareVersionMajor int,
	SoftwareVersionMinor int,
	SoftwareVersionBuild int,
	MachineName nvarchar(128),
	Flags int,
	BindingID uniqueidentifier,
	RecoveryForkID uniqueidentifier,
	Collation nvarchar(128),
	FamilyGUID uniqueidentifier,
	HasBulkLoggedData bit,
	IsSnapshot bit,
	IsReadOnly bit,
	IsSingleUser bit,
	HasBackupChecksums bit,
	IsDamaged bit,
	BeginsLogChain bit,
	HasIncompleteMetaData bit,
	IsForceOffline bit,
	IsCopyOnly bit,
	FirstRecoveryForkID uniqueidentifier,
	ForkPointLSN decimal(25,0),
	RecoveryModel nvarchar(60),
	DifferentialBaseLSN decimal(25,0),
	DifferentialBaseGUID uniqueidentifier,
	BackupTypeDescription nvarchar(128),
	BackupSetGUID uniqueidentifier,
	CompressedBackupSize bigint,
	Containment tinyint)

DECLARE @filelist table (
	LogicalName nvarchar(128),
	PhysicalName nvarchar(260),
	Type nchar(1),
	FileGroupName nvarchar(128),
	Size bigint,
	MaxSize bigint,
	FileId bigint,
	CreateLSN decimal(25,0),
	DropLSN decimal(25,0),
	UniqueId uniqueidentifier,
	ReadOnlyLSN decimal(25,0),
	ReadWriteLSN decimal(25,0),
	BackupSizeInBytes bigint,
	SourceBlockSize int,
	FileGroupId int,
	LogGroupGUID uniqueidentifier,
	DifferentialBaseLSN decimal(25,0),
	DifferentialBaseGUID uniqueidentifier,
	IsReadOnly bit,
	IsPresent bit,
	TDEThumbprint varbinary(20))

SET @cmd = 'dir ' + @folder_bak + ' /b | findstr /i "\.bak$"'

insert into @files execute xp_cmdshell @command_string = @cmd

DECLARE cur CURSOR FOR
SELECT FileName
FROM @files
WHERE FileName IS NOT NULL

OPEN cur
FETCH FROM cur INTO @bak_name
        
WHILE @@FETCH_STATUS = 0
BEGIN

    DELETE FROM @header;
    DELETE FROM @filelist;

    BEGIN TRY
        SET @sql = 'RESTORE HEADERONLY FROM DISK=''' + @folder_bak + '\' + @bak_name + ''';'
        INSERT INTO @header EXEC sp_executesql @sql
        SET @sql = 'RESTORE FILELISTONLY FROM DISK=''' + @folder_bak + '\' + @bak_name + ''';'
        INSERT INTO @filelist EXEC sp_executesql @sql
    END TRY
    BEGIN CATCH

        
        PRINT 'Backup ' + @bak_name + ' cannot be restored'

    END CATCH

    IF EXISTS (SELECT * FROM @header) AND EXISTS (SELECT * FROM @filelist)
    BEGIN
    
        SELECT @sql = 'RESTORE DATABASE ' + QUOTENAME(DatabaseName) + ' FROM  DISK = N''' +  @folder_bak + '\' + @bak_name + 
                ''' WITH FILE = 1, NOUNLOAD,  STATS = 5' + 
                (SELECT ', MOVE N''' + QUOTENAME(LogicalName) + 
                        ''' TO N''' + @folder_files + '\' + 
                        SUBSTRING(PhysicalName, LEN(PhysicalName) - CHARINDEX('\', REVERSE(PhysicalName)) + 2, LEN(PhysicalName)) + 
                        '' AS 'data()'
                FROM @filelist
                FOR XML PATH ('')
                )
        FROM @header

        EXEC sp_executesql @sql

    END


    FETCH FROM cur INTO @bak_name
END

CLOSE cur
DEALLOCATE cur