Please help , read the trn files names from a folder

  • hello all ,

    based on the advise here , i made the changes in the script. and than ran it

    **********************************************

    "-- declare variables

    declare @dir varchar(1000), @rowid int, @sqlcmd varchar(1000), @filename varchar(1000)

    declare @trn table (rowid int identity(1,1),file_name varchar(1000))

    -- set variables

    set @dir = 'Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\IT' -- Dir path for the trn folder

    insert into @trn exec xp_cmdshell @dir -- this statement will dump all your .trn files into a memory table

    -- do stuff

    Declare @max_row_id Int

    select @max_row_id = Max(rowid) From @trn

    select @rowid = min(rowid) from @trn

    while @rowid is not null

    begin

    If @rowid = @max_row_id

    Begin

    set @sqlcmd = @sqlcmd + ' With Recovery'

    End

    Else

    Begin

    set @sqlcmd = @sqlcmd + ' With No Recovery'

    End

    select @filename = file_name from @trn where rowid = @rowid

    set @sqlcmd = 'Restore Log IT From Disk = ''Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\IT' + @filename + ''''

    raiserror(@sqlcmd,0,1) with nowait

    select @rowid = min(rowid) from @trn where rowID > 0 and rowid > @rowid

    end

    **********************************************

    when i execute it , i get this message , not an error a message

    "

    3 row(s) affected)

    Restore Log IT From Disk = 'Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\IT'Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\IT' is not recognized as an internal or external command,'

    Restore Log IT From Disk = 'Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\IToperable program or batch file.'

    *********************************************

    i went in the restore history table in msdb to see weather the log files have been restoed , i dont see it in that table ,

    am i doing it right, any help will be so great .

    Thanks

  • Jack C

    since u know the history what i am looking for , ur input

    could be very helpful.

    Thanks

    Bobby

  • 1. The result you are getting is an error returned by xp_cmdshell because you are not passing it a DOS command.

    2. To correct this you need to revisit Oberion's post and lookup the switches he provides for the dir dos command. dir /? in a command window.

  • What is the 'additional' cost of simply using NORECOVERY for all of the log files, and then adding a line at the end (similar to a musical coda) which sets RECOVERY so the database is usable? Would that simplify the scripting without adding a lot of overhead, or am I missing the boat here?

  • Yeah that would work, once he actually gets the commands working correctly to enumerate the files.

  • Hello all

    so in the original oberian script /i386/ is the path and the rest are the switches that i need to add in my folder path??

    Thanks

    Bobby

  • Yes, DIR is the command C:\I386\ is the path and the rest are switches.

  • Thank you Jack

    Bobby

  • hello all ,

    when i run the script after making correction , it is giving me the following message , which i dont get it

    *********************************************

    -- declare variables

    declare @dir varchar(1000), @rowid int, @sqlcmd varchar(1000), @filename varchar(1000)

    declare @trn table (rowid int identity(1,1),file_name varchar(1000))

    -- set variables

    set @dir = 'dir c:\i386\ /TW /OD /B ' -- Dir path for the trn folder

    insert into @trn exec xp_cmdshell @dir -- this statement will dump all your .trn files into a memory table

    -- do stuff

    --Declare @max_row_id Int

    --select @max_row_id = Max(rowid) From @trn

    select @rowid = min(rowid) from @trn

    while @rowid is not null

    begin

    /*If @rowid = @max_row_id

    Begin

    set @sqlcmd = @sqlcmd + ' With Recovery'

    End

    Else

    Begin

    set @sqlcmd = @sqlcmd + ' With No Recovery'

    End"*/

    select @filename = file_name from @trn where rowid = @rowid

    set @sqlcmd = 'restore LOG IT From Disk =''Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\IT' + @filename + ''''

    raiserror(@sqlcmd,0,1) with nowait

    select @rowid = min(rowid) from @trn where rowID > 0 and rowid > @rowid

    end

    **********************************************

    (2 row(s) affected)

    restore LOG IT From Disk ='Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\ITThe system cannot find the file specified.'

    *********************************************

    now the problem is , i am cutting and pasting the path exactly as it is, i dont think i need to add the file name just the path to the folder where these files are sitting since the file names r getting saved in the m table.

    Thanks in advance.

    Bobby

  • The executed command must have a fully qualified file name, either UNC or relative drive (e.g., C:\...) if a local file. You may not be formatting your command correctly to marry the file name to the directory, but at the point of execution you will need to specify explicitly the filename in a way that SQL Server can find it and perform the command.

    The mind reading version of SQL Server is still in the design stage, unfortunately.

  • if you look at the code i posted , i am pinting it to the complete path (drive and all the way to the folder). can some body please help and let me know what i am doing wrong??

    Thanks

    Bobby

  • You need to replace "C:\i386" with the path to your log backups.

  • Hi,

    Thanks, i will try it now

    Bobby

  • Ok I'll bite, but please don't email me outside the forum.

    I think I can see a few problems with the additions you have tried to make to this script. I'm prepared to be corrected.

    First of all, as most other posters have pointed out, you need to change c:\i386\ to the folder name where your trn files live. The error message you posted shows that the script is not finding any trn files.

    That is: restore LOG IT From Disk ='Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\ITThe system cannot find the file specified' shows there is no filename after the IT. Make sure the Y drive is not a mapped drive that the server knows nothing about - fully qualify it.

    This needs to be done in both places that the path is mentioned and i think both need to end with

    The error message should also tell you that the parts of the script that you added in about adding the no recovery are in the wrong place (just noticed they are commented out too). This should be added after you set @sqlcmd = 'restore....blah blah and before you do the raiserror (is this just to list out the sqlcmd string?)

    If in doubt, just remove every thing from --do stuff downwards and add a line to select * from @trn and run that as a query.

    You should get a list of your filenames to show you are on the right track.

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply