You are here:Home » tsql » Restore Full Database from multiple backup files - SQL Server

Restore Full Database from multiple backup files - SQL Server

As I know database restoration is a best practice on periodically to check and verify database backup copies and issues while restoration due to any stuffs. I have written for the database backups as how can we perform a full database backup completely and split into multiple files to reduce IO and time. As we have performed full database backups into split multiple files, Same way we will perform restoration from those complete and split multiple files. First we will see to restore full database complete backup and tried for from multiple files.

#1. Using TSQL : Let us run the below script first and get the logical names for database data and log files
RESTORE FILELISTONLY FROM 
DISK =  'D:\DBBackups\ReportServer\ReportServer.bak'
GO

After collection and putting logical file names in script below, it will restore the database from complete backup.
RESTORE DATABASE ReportServerComplteCopy FROM 
DISK = 'D:\DBBackups\ReportServer\ReportServer.bak'
WITH REPLACE ,
MOVE 'ReportServer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerComplteCopy.mdf',
MOVE 'ReportServer_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerComplteCopy_log.ldf'
GO


As above, we can restore database from multiple files as following,
RESTORE DATABASE ReportServerSplitCopy FROM 
 DISK = 'D:\DBBackups\ReportServer\ReportServer_Split1.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split2.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split3.bak'
WITH REPLACE ,
MOVE 'ReportServer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerSplitCopy.mdf',
MOVE 'ReportServer_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerSplitCopy_log.ldf'
GO


#2. From Management Studio : We can also perform restoration from SSMS by adding those multiple backups as per shot.


You can see over here after adding those files it will show as a single backup set.


Hope this help you.