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