Disaster Recovery

svs1919
svs1919 used Ask the Experts™
on
I have a huge backup file the decomposed into 3 files test.bak1,Bak2 and bak3. How can I restore this DB in Standby mode and apply additional Tlog files?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bharat BhushanSolution Manager

Commented:
How the backup was made?
Bharat BhushanSolution Manager

Commented:
Here is the way to restore Tlog:

--To Restore a Transaction Log:  
RESTORE LOG { database_name | @database_name_var }  -- Does not apply to SQL Database Managed Instance
 [ <file_or_filegroup_or_pages> [ ,...n ] ]  
 [ FROM <backup_device> [ ,...n ] ]  
 [ WITH  
   {  
     [ RECOVERY | NORECOVERY | STANDBY =  
        {standby_file_name | @standby_file_name_var }  
       ]  
    | ,  <general_WITH_options> [ ,...n ]  
    | , <replication_WITH_option>  
    | , \<point_in_time_WITH_options—RESTORE_LOG>  
   } [ ,...n ]  
 ]  
[;]  

Regards,
Bharat
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
First Restore your Full backup followed by all log backups like this, pls make sure all file names and database names are changed accordingly
-- Restore Full backup from Multiple files
RESTORE DATABASE ur_db_name
FROM DISK = N'C:\test.bak1',
     DISK = N'C:\test.bak2',
     DISK = N'C:\test.bak3'
	 WITH NORECOVERY
GO

-- Restore First Log backup
RESTORE LOG ur_db_name FROM DISK = N'C:\test_1_log_backup.bak1' WITH NORECOVERY
GO
-- Restore Second Log backup
RESTORE LOG ur_db_name FROM DISK = N'C:\test_2_log_backup.bak1' WITH NORECOVERY
GO
-- Restore all other log backups in order
..
-- Restore last Log backup with RECOVERY option
RESTORE LOG ur_db_name FROM DISK = N'C:\test_last_log_backup.bak1' WITH RECOVERY
GO

Open in new window

Bharat BhushanSolution Manager

Commented:
I agree with Raja Jegan, just take care of that, the last file should be with recovery as mentioned above.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Actually I restore all log files "WITH NORECOVERY".  It's much simpler that way.

After all the log files are restored, verify that you got them all and everything is right, then issue this final command:

RESTORE DATABASE database_name WITH RECOVERY;

to make the db usable again.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial