Found this script to restore multiple databases but it is not working. I have the databases that are in full recovery mode as logshipping and simple databases I would like to restore from .bak file where I have multiple backup files.
if object_id('tempdb.dbo.#database') is not null
drop TABLE #database
go
create TABLE #database(id INT identity ,
name sysname,
logicalfileName Varchar(50),
DataFileName Varchar(50),
logicalfilePath Varchar(150),
DatafilePath Varchar(150)
)
go
set nocount on
declare @dbname sysname, @LogicalDataFile sysname , @LogicalLogFile sysname, @PhysicalDataFile nvarchar(260) , @PhysicalLogFile nvarchar(260)
insert into #database(name)
select name
from sys.databases
where name not in ('master','msdb','model', 'tempdb',*.........)
order by name
declare @id INT, @cnt INT, @sql NVARCHAR(max), @currentDb sysname;
select @id = 1, @cnt = max(id)
from #database
while @id <= @cnt
BEGIN
select @dbname=name from #database where id=@id
Data file
select @LogicalDataFile = name
, @PhysicalDataFile = physical_name
from sys.master_files
where database_id = db_id(@DBName)
and type_desc = 'ROWS'
Log file
select @LogicalLogFile = name
, @PhysicalLogFile = physical_name
from sys.master_files
where database_id = db_id(@DBName)
and type_desc = 'LOG'
update #database
set DataFileName= @LogicalDataFile
,logicalfileName= @LogicalLogFile
, Datafilepath =@PhysicalDataFile
, logicalfilePath = @PhysicalLogFile
where id=@id
set @id = @id + 1;
END
--Change the destination path in below syntax as per your envrionment
select 'RESTORE DATABASE ['+Name+'] FROM DISK = N''F:\MSSQL\Backup\'+name+'.bak'' WITH FILE = 1, MOVE N'''+logicalfilename+''' TO N''G:\MSSQL\Log\'+name+'.log'' ,MOVE N'''+DataFileName+''' TO N''F:\MSSQL\Data\'+name+'.mdf'''
from #database
drop table #database
go
