Microsoft Connectors for Oracle and Terradata

Microsoft connector helps to attain better performance than any built-in connectors of SSIS during loading or unloading of the data from Oracle DB. It has similar configuration and UI as SSIS built-in OLEDB connector. It uses Oracle’s OCI client with its Array Binding and Direct Path APIs to deliver high speed data transfer: It is available to download from Microsoft or Attunity web site for SQL Server Enterprise user at no cost. Attunity has three main components Oracle source, Oracle destination and Oracle connection manager.

Please use below link top download 3.0 version for SSIS 2014

https://www.microsoft.com/en-us/download/confirmation.aspx?id=44582


Please use below link top download 2.0 version for SSIS 2012


https://www.microsoft.com/en-us/download/details.aspx?id=29283


Error Message :

The connection type “MSORA” specified for connection manager is not recognized as a valid connection manager type.

The solution for above message is to download the connector and install.

 

Script to restore the databases from backup folder

Below script is to restore the databases from backup folder. Backup folder has subfolder for each database and subfolder contans the backup file.

DECLARE @BackupDirectory SYSNAME = 'G:\SQLBackups\'  -- Backup folder

IF OBJECT_ID('tempdb..#DirTree') IS NOT NULL

DROP TABLE #DirTree

CREATE TABLE #DirTree (

Id int identity(1,1),

SubDirectory nvarchar(255),

Depth smallint,

FileFlag bit,

ParentDirectoryID int

)

INSERT INTO #DirTree (SubDirectory, Depth, FileFlag)

EXEC master..xp_dirtree @BackupDirectory, 10, 1

UPDATE #DirTree

SET ParentDirectoryID = (

SELECT MAX(Id) FROM #DirTree d2

WHERE Depth = d.Depth - 1 AND d2.Id < d.Id

)

FROM #DirTree d

DECLARE

@ID INT,

@BackupFile VARCHAR(MAX),

@Depth TINYINT,

@FileFlag BIT,

@ParentDirectoryID INT,

@wkSubParentDirectoryID INT,

@wkSubDirectory VARCHAR(MAX)

DECLARE @BackupFiles TABLE

(

FileNamePath VARCHAR(MAX),

TransLogFlag BIT,

BackupFile VARCHAR(MAX),

DatabaseName VARCHAR(MAX)

)

DECLARE FileCursor CURSOR LOCAL FORWARD_ONLY FOR

SELECT * FROM #DirTree WHERE FileFlag = 1

OPEN FileCursor

FETCH NEXT FROM FileCursor INTO

@ID,

@BackupFile,

@Depth,

@FileFlag,


@ParentDirectoryID
SET @wkSubParentDirectoryID = @ParentDirectoryID

WHILE @@FETCH_STATUS = 0

BEGIN

--loop to generate path in reverse, starting with backup file then prefixing subfolders in a loop

WHILE @wkSubParentDirectoryID IS NOT NULL

BEGIN

SELECT @wkSubDirectory = SubDirectory, @wkSubParentDirectoryID = ParentDirectoryID

FROM #DirTree

WHERE ID = @wkSubParentDirectoryID

SELECT @BackupFile = @wkSubDirectory + '\' + @BackupFile

END

--no more subfolders in loop so now prefix the root backup folder

SELECT @BackupFile = @BackupDirectory + @BackupFile

--put backupfile into a table and then later work out which ones are log and full backups

INSERT INTO @BackupFiles (FileNamePath,DatabaseName) VALUES(@BackupFile,@wkSubDirectory)



declare @cmd nvarchar(max)

declare @PhysicalDBfolder varchar(100)

declare @LogDBfolder varchar(100)

declare @PhysicalDB varchar(100)

declare @LogDB varchar(100)

set @PhysicalDBfolder='E:\SQLData\'+@wkSubDirectory+'.mdf'

set @LogDBfolder='F:\SQLLogs\'+@wkSubDirectory+'_log.ldf'

set @PhysicalDB=@wkSubDirectory

set @LogDB=@wkSubDirectory+'_log'

--Restore Command

SET @cmd = 'RESTORE DATABASE ['+ @wkSubDirectory +'] FROM DISK = '''+ @BackupFile +''' WITH '

SET @cmd = @cmd + 'MOVE ''' + @PhysicalDB + '''' + ' TO '''+@PhysicalDBfolder+''''

SET @cmd = @cmd + ' ,MOVE ''' + @LogDB + '''' + ' TO '''+@LogDBfolder+''''



--select @cmd
--executing restore commandExec sp_executesql @cmd

FETCH NEXT FROM FileCursor INTO

@ID,

@BackupFile,

@Depth,

@FileFlag,


@ParentDirectoryID
SET @wkSubParentDirectoryID = @ParentDirectoryID

END



--select * from @BackupFiles
  CLOSE FileCursor

DEALLOCATE FileCursor

Query to List of backup's by Database

Below query gives the list of all the backup's taken on the server.


SELECT sysdb.name DBName, bkup.description, physical_device_name , bkup.backup_finish_date,

case

when type='D' then '** FULL **'

when type='I' then 'DIFFERENTIAL'

when type='L' then 'LOG'

end as Backup_Type,

(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',

ceiling(bkup.backup_size /1048576) as 'Size Meg' ,

cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',

server_name, sysdb.crdate

, first_lsn, last_lsn, checkpoint_lsn

FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name

left join msdb.dbo.backupmediafamily Fam on fam.media_set_id=bkup.media_set_id

where backup_finish_date > DATEADD(DAY, -5, (getdate())) -- Last 5 days

--AND sysdb.name = DB_NAME'

-- and bkup.backup_finish_date>='11/02/2017'

ORDER BY sysdb.name, bkup.backup_finish_date desc

Database Script to restore from backup file

Error : Logical Name is not part of the Database.

We get this error when we try to restore the DB

Msg 3234, Level 16, State 1, Line 1
Logical file 'Northwind_Data' is not part of database 'Northwind'. 
Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Please run below script to find the actual names in the backup file.
RESTORE FILELISTONLY 
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\Northwind.bak'

Script to restore the DB

BEGIN
  RESTORE DATABASE [Northwind]
  FILE = N'Northwind_Data'
  FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\Northwind.bak'
  WITH 
    FILE = 1, NOUNLOAD, STATS = 10,
    MOVE N'YOUR logical name of data file as shown by RESTORE FILELISTONLY command'
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind.mdf',
    MOVE N'YOUR logical name of Log file as shown by RESTORE FILELISTONLY command'
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind_0.LDF'
END