How to restore multiple databases

Question

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, @alenzi 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

Answer 1

Hi @Avyayah ,

We can using T-SQL to backup and restore all user databases automatically at once. Suggest you using the T-SQL from below blogs;

How to Backup All Databases at Once in MS SQL Server
How to Restore Multiple Databases at once in MS SQL Server

> 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.

Did you mean restore all the user databases except the databases that configured log shipping? If so, please do not add the log shipping databases in the restore scripts.

Or you mean databases in simple recovery mode? Suggest you do not set the database in simple recovery mode, in this mode, we can not do a log backup. It can’t restore the databases in point time.

If I misunderstood, please let me know.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.