restore_databases.sql

declare @databasesToRestore table (BakFile nvarchar(128));
insert @databasesToRestore(BakFile) values 
('S545.bak'),
('S1101546.bak');

---('S1101546.bak'),
---('S01601101547.bak');
---('S7.bak'),
---('S01549.bak'),
---('S.bak'),
---('S1550.bak');
---('S.bak'),
---('S1551.bak');
---('S.bak'),
---('S1551.bak');



DECLARE @FileList TABLE
      (
      LogicalName nvarchar(128) NOT NULL,
      PhysicalName nvarchar(260) NOT NULL,
      Type char(1) NOT NULL,
      FileGroupName nvarchar(120) NULL,
      Size numeric(20, 0) NOT NULL,
      MaxSize numeric(20, 0) NOT NULL,
      FileID bigint NULL,
      CreateLSN numeric(25,0) NULL,
      DropLSN numeric(25,0) NULL,
      UniqueID uniqueidentifier NULL,
      ReadOnlyLSN numeric(25,0) NULL ,
      ReadWriteLSN numeric(25,0) NULL,
      BackupSizeInBytes bigint NULL,
      SourceBlockSize int NULL,
      FileGroupID int NULL,
      LogGroupGUID uniqueidentifier NULL,
      DifferentialBaseLSN numeric(25,0)NULL,
      DifferentialBaseGUID uniqueidentifier NULL,
      IsReadOnly bit NULL,
      IsPresent bit NULL,
      TDEThumbprint varbinary(32) NULL
 );

declare @headers table 
( 
    BackupName varchar(256),
    BackupDescription varchar(256),
    BackupType varchar(256),        
    ExpirationDate varchar(256),
    Compressed varchar(256),
    Position varchar(256),
    DeviceType varchar(256),        
    UserName varchar(256),
    ServerName varchar(256),
    DatabaseName varchar(256),
    DatabaseVersion varchar(256),        
    DatabaseCreationDate varchar(256),
    BackupSize varchar(256),
    FirstLSN varchar(256),
    LastLSN varchar(256),        
    CheckpointLSN varchar(256),
    DatabaseBackupLSN varchar(256),
    BackupStartDate varchar(256),
    BackupFinishDate varchar(256),        
    SortOrder varchar(256),
    CodePage varchar(256),
    UnicodeLocaleId varchar(256),
    UnicodeComparisonStyle varchar(256),        
    CompatibilityLevel varchar(256),
    SoftwareVendorId varchar(256),
    SoftwareVersionMajor varchar(256),        
    SoftwareVersionMinor varchar(256),
    SoftwareVersionBuild varchar(256),
    MachineName varchar(256),
    Flags varchar(256),        
    BindingID varchar(256),
    RecoveryForkID varchar(256),
    Collation varchar(256),
    FamilyGUID varchar(256),        
    HasBulkLoggedData varchar(256),
    IsSnapshot varchar(256),
    IsReadOnly varchar(256),
    IsSingleUser varchar(256),        
    HasBackupChecksums varchar(256),
    IsDamaged varchar(256),
    BeginsLogChain varchar(256),
    HasIncompleteMetaData varchar(256),        
    IsForceOffline varchar(256),
    IsCopyOnly varchar(256),
    FirstRecoveryForkID varchar(256),
    ForkPointLSN varchar(256),        
    RecoveryModel varchar(256),
    DifferentialBaseLSN varchar(256),
    DifferentialBaseGUID varchar(256),        
    BackupTypeDescription varchar(256),
    BackupSetGUID varchar(256),
    CompressedBackupSize varchar(256),        
    Containment varchar(256),
    --
    -- This field added to retain order by
    --
    Seq int NOT NULL identity(1,1)
); 
declare @path  nvarchar(140);
declare @dbname nvarchar(120);
declare @logName nvarchar(120);
declare @dbFile nvarchar(120);
declare @logFile nvarchar(120);
declare @logical_data nvarchar(max), @logical_log nvarchar(max);

DECLARE @file nvarchar(128)
DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT * FROM @databasesToRestore

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @file
WHILE @@FETCH_STATUS = 0
BEGIN 
	set @path = CONCAT('D:/Backup/', @file) --, '.bak')
	PRINT @path

	insert into @headers exec('restore headeronly from disk = '''+ @path +'''');
	select @dbname = DatabaseName from @headers;

	set @logName = CONCAT(@dbname, '_log')
	set @dbFile = CONCAT('D:\MSSQL\Data\',+ @dbname, '.mdf')
	set @logFile = CONCAT('D:\MSSQL\Data\',+ @dbname, '_log.ldf')

	INSERT INTO @FileList
	exec('restore filelistonly from disk = '''+ @path +'''');

	set @logical_data = (select LogicalName from @FileList where Type = 'D' and FileID = 1)
	set @logical_log = (select LogicalName from @FileList where Type = 'L' and FileID = 2)

	--- Do the restore

	RESTORE DATABASE @dbname
	FROM DISK = @path
	WITH MOVE @logical_data TO @dbFile,
	MOVE @logical_log TO @logFile

	delete from @FileList
	delete from @headers

    FETCH NEXT FROM MY_CURSOR INTO @file
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR