SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Transactional Logs Restore

By Harsha Bhagat,

As a DBA, we have to test our backups with restore testing. 
To test the restore manually it does becomes a bit of lengthy process. 
To ease out the process of copy and pasting all the files what we want to test the restore the procedure will help.
Requirments:.
1. Please restore the database and leave the database in non-recovery mode. 
2. Copy all the transaction log files into one folder location and make sure that the service account has correct permission to read and write the files. 
3. execute the procedure as below.
    DBA_Restore_TransactionLog 'DatabaseName', 'S:\backup'
If error happens like below 
Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 2413977000000004100001, which is too late to apply to the database. An earlier log backup that includes LSN 2413630000000004100001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
To know what files were picked up use the table Temp_FileList
Id          Dates                Times                Filenames                                                                                                                                                                                                                                                        
----------- -------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1           23/06/2018           08:00                DBName_20180623_080002.trn
2           23/06/2018           07:19                DBName_20180623_071904.trn
3           23/06/2018           07:18                DBName_20180623_071442.trn
4           23/06/2018           07:06                DBName_20180623_070609.trn
5           23/06/2018           07:06                DBName_20180623_070613.trn
6           23/06/2018           07:01                DBName_20180623_070007.trn
7           23/06/2018           06:00                DBName_20180623_060003.trn
8           23/06/2018           05:00                DBName_20180623_050002.trn
9           23/06/2018           04:00                DBName_20180623_040003.trn
To see what the procedure has executed, use the table Temp_ExecuteFileList
select * from Temp_ExecuteFileList
Exec_Script                                                                                                                                                                                                                                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_000002.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_010003.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_020002.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_030002.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_040003.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_050002.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_060003.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_070007.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_070613.trn'    WITH NORECOVERY;
 

Total article views: 233 | Views in the last 30 days: 6
 
Related Articles
FORUM

Restore DB with Norecovery

Restore DB with Norecovery

BLOG

Always restore with NORECOVERY

Always, always, ALWAYS restore a SQL Server database with the NORECOVERY option. It’s trivial to s...

FORUM

recovery and norecovery

recovery and norecovery

FORUM

The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY

The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY

FORUM

Backup/restore

Backup/restore

Tags
restore    
 
Contribute