Results 1 to 10 of 18

Thread: Creating a backup (of sorts) of the INT files

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,836

    Default Creating a backup (of sorts) of the INT files

    A few times over the past couple of years, I've run into situations where a clients hard drive goes down and they have access to a recent SQL bak backup but not the folder with the INT files.

    In those cases, I had to figure out what version of the software they were running, scoop the INTs from an archived copy that I have in the library and then spend a bit of time working through the differences manually editting the INT files where needed.

    To help make this easier in the future, I decided to save a copy of the INT files in the SQL database so that they would always be with the bak file.

    First, I created a new data file "INIFILES"
    Code:
    NUM  FIELD NAME       TYPE SIZE  OFFST IX   RELATES TO FILE.FIELD
    ---  ---------------  ---- ----- ----- --   ---------------------------------
      1  FILENAME         ASC     25     1  1   
      2  DATAVER          ASC      6    26      
      3  CONTENT          TEX  16000    32      
    
    
    
    
    INDEX# FIELDS          DES U/C    LENGTH LEVELS SEGMENTS MODE
    ------ --------------- --- ---    ------ ------ -------- -------
      1    FILENAME        NO  NO       25     4       1     ON-LINE

    In my DF Startup code, I added this piece to the process that opens up the data files
    Code:
        If ((SYSTEM.INI_DATAVER<SYSFILE.VERSION)) Begin
                If (iCh=0) Begin
                    Move (Seq_New_Channel()) to iCh
                End
                Get psDataPath of hAppWS to sPurePath
                Get fLastBackSlash sPurePath to sPurepath
                For i from 0 to 999
                    Get fLogicalName i to sLogicalName
                    Move (Trim(sLogicalName)) to sLogicalName
                    If (sLogicalName<>'') Begin
                        Get fRootName i to sName
                        If ((Left(sName,9))='MSSQLDRV:') Begin
                            Clear INIFILES
                            Move sLogicalname to INIFILES.FILENAME
                            Find EQ INIFILES by Index.1
                            If (not(Found)) Begin
                                Clear INIFILES
                                Reread
                                  Move sLogicalname to inifiles.filename
                                    SaveRecord INIFILES
                                Unlock
                            End
                            Direct_Input channel iCh (sPurepath+sLogicalName+'.int')
                            If (not(SeqEof)) Begin
                                Move '' to sWorkString
                                Move '' to sTestString
                                Repeat
                                   Readln channel iCh sTestString 
                                   Move (sWorkstring+sTestString+Character(13)+Character(10)) to sWorkString
                                Until (SeqEof)    
                                Reread
                                   Move sWorkString to INIFiles.Content
                                   Move SYSFILE.VERSION to inifiles.dataver
                                      SaveRecord inifiles
                                Unlock
                            End
                            Close_Input channel iCh
                        End
                        
                    End
                Loop
                Reread
                    Move SYSFILE.VERSION to SYSTEM.INI_DATAVER
                    Get psVersion of hAppWS to SYSTEM.INI_VERSION
                       SaveRecord inifiles
                Unlock
    With this, now an up to date copy of the INT files also exists in the SQL Database.

    So, next I wanted a process that I could run directly in SQL to recreate these files, should the originals be damaged or missing. I scooped some code off of the internet and with some minor changes, I did this in three steps:

    STEP 1:
    Code:
    
    CREATE DATABASE TestDB;
    GO
    
    
    USE TestDB;
    GO
    
    
    CREATE TABLE [dbo].[FileContents](
     [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
     [fileText] [text] NOT NULL,
     [fileName] [varchar](50) NULL
    );
    GO

    STEP 2:
    Code:
    INSERT INTO TestDB.dbo.FileContents ([fileText], [fileName])
    SELECT [CONTENT], [FILENAME]  FROM WISH_ROSEWOOD.dbo.INIFILES;

    STEP 3:
    Code:
    --Temporarily enable xp_cmdshell
    sp_configure 'show advanced options',1;
    RECONFIGURE
    GO
    
    
    sp_configure 'xp_cmdshell',1;
    RECONFIGURE
    GO
    
    
    --Declare variables 
    DECLARE @fileText VARCHAR(MAX);
    DECLARE @fileName VARCHAR(500) ;
    DECLARE @command varchar(1000);
    
    
    --
    --Declare parameters
    --
    --Here you need to specify the output directory for the files to be created
    DECLARE @outputDir VARCHAR(250)='c:\tmp\';
    
    
    --Here you need to specify the SQL instance from which the data will be exported
    DECLARE @instanceName VARCHAR(50)='.\SQLDEV2017'
    
    
    --Here you specify whether you are connecting to the SQL instance with a 
    --trusted connection (Windows Authentication) or not
    DECLARE @isTrustedConnection BIT=1
    
    
    --If isTrustedConnection is set to 0 then you will need to 
    --add username and password for connecting to the SQL Server instance
    DECLARE @userName VARCHAR(50)='loginsa'
    DECLARE @password VARCHAR(50)='password'
    
    
    --Create temporary table if it does not exist
    IF OBJECT_ID('TestDB..tmpSource') IS NULL
    CREATE TABLE TestDB..tmpSource(
     [fileText] [TEXT] NULL
    );
    
    
    --Declare cursor and populate with data
    DECLARE db_cursor CURSOR FOR  
    SELECT [fileText], [fileName]
    FROM TestDB..FileContents
    ;
    
    
    --Open cursor
    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @fileText, @fileName
    
    
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        --Clean temporary table
        DELETE FROM TestDB..tmpSource
        
        --Insert record into temporary table
        INSERT INTO TestDB..tmpSource (fileText) VALUES(@fileText);
          
        --Dynamically construct the BCP command
        --If the user connects to the SQL instance using a trusted connection
        IF (@isTrustedConnection=1)
           SET @command = 'bcp "SELECT * FROM TestDB..tmpSource" queryout "'+ @outputDir + trim(@fileName) + '.int" -S ' + @instanceName +' -T -c'
           --SELECT *, @fileName FROM TestDB..tmpSource
        --If the user connects to the SQL instance using username/password
        ELSE
           SET @command = 'bcp "SELECT * FROM TestDB..tmpSource" queryout "'+ @outputDir + @fileName + '" -S ' + @instanceName +' -c -U ' + @userName + ' -P '+ @password
    
    
        --Execute the BCP command
        EXEC xp_cmdshell @command, no_output
    
    
        --Fetch next records
        FETCH NEXT FROM db_cursor INTO @fileText, @fileName
    END
       
    --Close and deallocate cursor
    CLOSE db_cursor   
    DEALLOCATE db_cursor
    GO
    
    
    --Disable xp_cmdshell
    sp_configure 'xp_cmdshell',0
    RECONFIGURE
    GO
    
    
    sp_configure 'show advanced options',0
    RECONFIGURE
    GO
    This doesn't take care of reconstructing the Filelist. With a bit more coding, I suppose that I could deal with that too, but the major headache was the missing or damaged INT files.

    Hope someone else finds this useful to them

    PS... I had a "brainfart" when I did this and called the file INIFILES, but should have named it INTFILES.

    Mike
    Last edited by Mike Cooper; 28-Aug-2020 at 11:27 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •