SQL Server- Restore database scripts to handle multiple logical files..primary files, data files and index files

When our database backup has multiple logical files and physical file names like mdf,ndf  and ldf files, our normal restore backup query will not work..to restore files from those backups we need to write restore query to handle the move logic dynamically, this code below works magically.



DECLARE @drivelocation varchar(25)
DECLARE @directoryname varchar(2000)
DECLARE @databasename as varchar(128)

SET @drivelocation= "D:/Test"
SET @directoryname = @drivelocation  + '\New' +  '.bak';

SET @databasename = 'NewDatabase'

IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @databasename))
BEGIN
EXEC('DROP DATABASE ' + @databasename)
END
 -- Create a table to hold the logical names of the source database we are restoring from
  CREATE TABLE #t
 (id int identity,
 LogicalName VARCHAR(128)
,PhysicalName VARCHAR(260)
,Type CHAR(1)
 ,FileGroupName NVARCHAR(128)
 ,Size NUMERIC(20,0)
 ,MaxSize NUMERIC(20,0)
 ,FileID BIGINT
 ,CreateLSN NUMERIC(25,0)
 ,DropLSN NUMERIC(25,0) NULL
 ,UniqueID UNIQUEIDENTIFIER
 ,ReadOnlyLSN NUMERIC(25,0) NULL
 ,ReadWriteLSN NUMERIC(25,0) NULL
 ,BackupSizeInBytes BIGINT
 ,SourceBlockSize INT
 ,FileGroupID INT
 ,LogGroupGUID UNIQUEIDENTIFIER NULL
 ,DifferentialBaseLSN NUMERIC(25,0) NULL
 ,DifferentialBaseGUID UNIQUEIDENTIFIER
 ,IsReadOnly BIT
 ,IsPresent BIT
 ,TDEThumbprint CHAR(1)
 )

 DECLARE @stmt nVARCHAR(1024)
 SET @stmt = 'RESTORE FILELISTONLY FROM DISK=' + '''' + @directoryname + ''''
 INSERT INTO #t EXEC(@stmt)

print @stmt

DECLARE @id int;
DECLARE @Max int;
DECLARE @string1 varchar(max);
DECLARE @logicalname as varchar(128);
DECLARE @physicalname as varchar(128);


SET @id = 0
SET @string1=''
SELECT @Max = max(id) from #t

WHILE @id < @Max
   BEGIN
     SET @id = @id + 1   
     IF @id =1 and @id <@max
SET @logicalname='';
SET @physicalname='';

SELECT @logicalname=logicalname,@physicalname=physicalname FROM #t WHERE id=@id
IF CHARINDEX('mdf',@physicalname,1)> 0
SET @physicalname = @drivelocation+'EHI_ExportDir\Data_dev\' +@databasename + '.mdf'
ELSE IF CHARINDEX('ndf',@physicalname,1)> 0
SET @physicalname = @drivelocation+'EHI_ExportDir\Data_dev\' +@databasename +  '.ndf'
ELSE IF CHARINDEX('ldf',@physicalname,1)> 0
SET @physicalname = @drivelocation+'EHI_ExportDir\Log_dev\' +@databasename +  '.ldf'

IF @id =1
SET @string1 = @string1 + ' WITH MOVE ' + '''' + @logicalname + '''' + ' TO ' + '''' +  @physicalname + '''' + ','
ELSE
SET @string1 = @string1 + '  MOVE ' + '''' + @logicalname + '''' + ' TO ' + '''' +  @physicalname + '''' + ','

END

print @string1

 SET @stmt = 'USE MASTER;' + 'RESTORE DATABASE ' + @databasename  + 
  ' FROM DISK = N''' + @directoryname + ''''  + @string1 +
 ' REPLACE, FILE = 1,  NOUNLOAD'
 print (@stmt)
 drop table #t
 print (@stmt)

VB6 Program to read and print into a text file using freefile

Program to read and print into a text file

Dim sFileText as String
Dim iFileNo as Integer

iFileNo = FreeFile

'open the file for writing
  Open "C:\Test.txt" For Output As #iFileNo
'please note, if this file already exists it will be overwritten!

   'write some example text to the file
  Print #iFileNo, "first line of text"
  Print #iFileNo, "   second line of text"
  Print #iFileNo, ""  'blank line
  Print #iFileNo, "some more text!"

   'close the file (if you dont do this, you wont be able to open it again!)

  Close #iFileNo

VB6 Program to read/write text file using filesystemobject

Program to read/write text file using filesystemobject

Private Sub Command2_Click()
'read text file and print

Dim fs As FileSystemObject
Dim ts As TextStream

Set fs = New FileSystemObject
If fs.FileExists("C:\Users\Desktop\text1.txt") Then
    Set ts = fs.OpenTextFile("C:\Users\Desktop\text1.txt", ForReading, True)
    Do While Not ts.AtEndOfStream
        strnewlines = strnewlines & ts.ReadLine & vbCrLf

    Loop
    ts.Close
End If

'for writing into text file
Set ts = fs.OpenTextFile("C:\Users\Desktop\text2.txt", ForWriting, True)
ts.Write strnewlines

End Sub

VB6 Program to connect to access database

VB6 Code to connect to access database

Dim connection1 As ADODB.Connection
Set connection1 = CreateObject("ADODB.Connection")

'connection string
constring = "provider=microsoft.jet.oledb.4.0;data source=C:\Users\Desktop\vb pgms\Database1.mdb"

connection1.Open constring

MsgBox connection1.State