Apr 21, 2012

Restore all of the log file from a folder

DECLARE @Path nvarchar(260)
 SET @Path = 'D:/backup/back_data/LOG/TEST'

 IF RIGHT(@Path, 1) <> '/'
 SET @Path = @Path + '/'
 IF OBJECT_ID('tempdb..#') IS NOT NULL
 DROP TABLE #
 CREATE TABLE #(
 id int IDENTITY,
 directory nvarchar(260),
 depth int,
 IsFile bit)
 INSERT # EXEC master.dbo.xp_dirtree
 @path = @path,
 @depth = 0,
 @file = 1

 DECLARE @depth int, @depthMax int
 UPDATE # SET directory = @Path + directory WHERE depth = 1

 DECLARE @RestorePath nvarchar(4000)
 DECLARE C CURSOR FOR SELECT directory FROM #
 OPEN C
 FETCH NEXT FROM C INTO @RestorePath
 WHILE @@FETCH_STATUS=0
 BEGIN
 RESTORE LOG back_data FROM DISK=@RestorePath
 WITH NORECOVERY
 FETCH NEXT FROM C INTO @RestorePath
 END
 CLOSE C
 DEALLOCATE C

No comments:

Post a Comment

Hi,

Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.