Return to Question

added 156 characters in body

Source Link

edited Jul 21, 2023 at 19:04

47.6k
3
51
101

My Query to auto restore all the log files from the file explorer: USE Master; GO SET NOCOUNT ON -- 1 - Variable declaration DECLARE @dbName sysname DECLARE @backupPath NVARCHAR(500) DECLARE @cmd NVARCHAR(500) DECLARE @fileList TABLE (backupFile NVARCHAR(255)) DECLARE @backupFile NVARCHAR(500) alter database [DB_NAME] set offline with rollback immediate alter database [DB_NAME] set online -- 2 - Initialize variables SET @dbName = 'DB_NAME' SET @backupPath = 'F:\ECW LOG FILES\05.22 SQL Backup full\Logbackup\db_20230522' -- backup path is the location in the file explorer where all the TRN files are located. -- 3 - get list of files SET @cmd = 'DIR /b "' + @backupPath + '"' INSERT INTO @fileList(backupFile) EXEC master.sys.xp_cmdshell @cmd select * from @fileList DECLARE backupFiles CURSOR FOR SELECT backupFile FROM @fileList ORDER BY backupFile OPEN backupFiles -- Loop through all the files for the database FETCH NEXT FROM backupFiles INTO @backupFile WHILE @@FETCH_STATUS = 0 BEGIN IF @backupFile LIKE '%.TRN' AND @backupFile LIKE '%ECW44Z1CLSDB07A$ASP44POD07A_AG01_db_LOG%' BEGIN SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = ''' + @backupPath + @backupFile + ''' WITH STANDBY =N''F:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_DBNAME.BAK''' EXEC (@cmd) END**

USE Master; 
GO 
SET NOCOUNT ON
-- 1 - Variable declaration 
DECLARE @dbName sysname 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @backupFile NVARCHAR(500)
alter database [DB_NAME] 
set offline with rollback immediate
 alter database [DB_NAME] 
 set online
-- 2 - Initialize variables 
SET @dbName = 'DB_NAME' 
SET @backupPath = 'F:\ECW LOG FILES\05.22 SQL Backup full\Logbackup\db_20230522\'
-- backup path is the location in the file explorer where all the TRN files are located. 
-- 3 - get list of files 
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd
select * from @fileList
DECLARE backupFiles CURSOR FOR 
SELECT backupFile 
FROM @fileList 
ORDER BY backupFile
OPEN backupFiles
-- Loop through all the files for the database 
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0 
BEGIN 
    IF @backupFile LIKE '%.TRN' AND @backupFile LIKE '%ECW44Z1CLSDB07A$ASP44POD07A_AG01_db_LOG%' 
    BEGIN
        SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = ''' 
        + @backupPath + @backupFile + ''' WITH STANDBY =N''F:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_DBNAME.BAK'''
        EXEC (@cmd)
    END

My Query to auto restore all the log files from the file explorer: **

USE Master; 
GO 
SET NOCOUNT ON
-- 1 - Variable declaration 
DECLARE @dbName sysname 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @backupFile NVARCHAR(500)
alter database [DB_NAME] 
set offline with rollback immediate
 alter database [DB_NAME] 
 set online
-- 2 - Initialize variables 
SET @dbName = 'DB_NAME' 
SET @backupPath = 'F:\ECW LOG FILES\05.22 SQL Backup full\Logbackup\db_20230522\'
-- backup path is the location in the file explorer where all the TRN files are located. 
-- 3 - get list of files 
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd
select * from @fileList
DECLARE backupFiles CURSOR FOR 
SELECT backupFile 
FROM @fileList 
ORDER BY backupFile
OPEN backupFiles
-- Loop through all the files for the database 
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0 
BEGIN 
    IF @backupFile LIKE '%.TRN' AND @backupFile LIKE '%ECW44Z1CLSDB07A$ASP44POD07A_AG01_db_LOG%' 
    BEGIN
        SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = ''' 
        + @backupPath + @backupFile + ''' WITH STANDBY =N''F:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_DBNAME.BAK'''
        EXEC (@cmd)
    END

Source Link

asked Jul 21, 2023 at 16:28

Prithvi

TRN file Backup using LSN

I receive the TRN files on a daily basis from the client and I back it up on a regular basis, i have usually backed it up based on the timestamp, but they recommend using LSN. I download the files from azure and back it up in the SQL Db. Currently the first file for todays back up failed with error Msg 4305, Level 16, State 1, Line 3 The log in this backup set begins at LSN 41217000000286400001, which is too recent to apply to the database. An earlier log backup that includes LSN 41216000010994400001 can be restored. Msg 3013, Level 16, State 1, Line 3 RESTORE LOG is terminating abnormally.

How am I able to view the LSN no for all the files in the file explorer and find the right TRN file to restore first.