question

SahaSaha-5270 avatar image
0 Votes"
SahaSaha-5270 asked SahaSaha-5270 commented

How to restore multiple databases

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

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

restore multiple databases but it is not working

"Not working" means what in detail? Do you get error message (which one) or what is not working?



1 Vote 1 ·

1 Answer

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered SahaSaha-5270 commented

Hi @SahaSaha-5270,

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.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Cathyji-msft , restore all user databases except the one configured for log shipping.

0 Votes 0 ·

If so , please check the T-SQL from the blog that i mentioned above.

0 Votes 0 ·


 SET @PATH = N' F:\MSSQL\Backup\' + @DBName + '.bak'
 SET @mdfPATH = N'F:\MSSQL\Data\' + @DBName + '.mdf'
 SET @logPATH = N'G:\MSSQL\Log\' + @DBName + '_log.ldf'
 SET @log = @DBName + '_log'

RESTORE FILELISTONLY FROM DISK = @PATH

 RESTORE DATABASE @DBName FROM  DISK = @PATH
 WITH FILE = 1
 , MOVE 'LogicalName' TO  @mdfPATH
 , MOVE 'LogicalLogsName' TO @logPATH
 , NOUNLOAD,  STATS = 5
    

 SET @COUNT = @COUNT + 1 
 END
 GO

106324-restorelogicalname.jpg


what should I use after move? Data and logfile are described as Logical Name

0 Votes 0 ·