Backup FileMaker database to MS-SQL-server using linked server object

Document created by okramis on Mar 14, 2018Last modified by okramis on Mar 14, 2018
Version 2Show Document
  • View in full screen mode

This is triggered by this thread: Filemaker to SQL Database

 

If you want/need to backup your filemaker-database to a ms-sql-database, you can do this by creating a odbc-connection from the sql-server to your filemaker-server (odbc-sharing has to be activated) and creating a linked server object in "sql server management studio" using the odbc connection. Important: "Describe text fields as long varchar" has to be checked in odbc dsn configuration. The linked server tables can't be queried directly, the queries have to be wrapped in openquery(linkedserver, 'query').

Following script backups all tables with "normal" (stored data) fields except globals into the database where it's executed from, best use a empty database. The script does no error handling, so it might fail on your data, feel free to optimise. I had failures when a time field value was larger then 24 hours i.e.

Script is attached too.

 

best regards

Otmar

 

-- Backup FileMaker database to SQL-Server using linked server object

-- ************************************************************************************************************************

-- Copyright © 2018 by Otmar Kramis

-- This script is free for non-commercial purposes with no warranties.

 

-- the variable @linkedserver has to be set to your linked server object and also in the openquery() in line 34

-- container fields can either be retrieved as binaries (blob) or as filepath, uncomment/comment the appropriate line (lines 50/52)

-- unfortunately it seems it can't handle both in the same query

-- ************************************************************************************************************************

 

--uncomment next line, if you want to clear out an existing backup

--EXEC sp_MSforeachtable 'DROP TABLE ?'

 

-- in-memory tablename table to hold distinct tablenames

DECLARE @linkedserver NVARCHAR(100)

DECLARE @fields VARCHAR(5000)

DECLARE @fields2 VARCHAR(5000)

DECLARE @query VARCHAR(8000)

DECLARE @fieldquery NVARCHAR(MAX)

DECLARE @fieldquery2 NVARCHAR(MAX)

DECLARE @tablename nvarchar(100)

DECLARE @i int

DECLARE @numrows int

DECLARE @tablename_table TABLE (

    idx smallint Primary Key IDENTITY(1,1)

    , tablename nvarchar(100)

)

 

SET @linkedserver = N'PM' --your linked server here and in the next INSERT-statement too

 

-- populate tablename table

-- change to your linked server

INSERT @tablename_table SELECT * FROM openquery(PM, 'SELECT DISTINCT BaseTableName FROM FileMaker_Tables')

 

-- enumerate the table

SET @i = 1

SET @numrows = (SELECT COUNT(*) FROM @tablename_table)

IF @numrows > 0

    WHILE (@i <= (SELECT MAX(idx) FROM @tablename_table))

    BEGIN

 

        -- get the next tablename

        SET @tablename = (SELECT tablename FROM @tablename_table WHERE idx = @i)

 

  --collect "normal" fields except container and globals

  SET @fieldquery = 'SELECT @fields = STUFF((SELECT '', "'' + CONVERT(NVARCHAR(100), FieldName) + ''"'' FROM openquery(' + @linkedserver + ', ''SELECT FieldName FROM FileMaker_Fields WHERE TableName=''''' + @tablename + ''''' AND FieldClass=''''Normal'''' AND FieldType != ''''binary'''' AND FieldType NOT LIKE ''''global%'''''') FOR xml path('''')),1,1,'''')'

  --collect container-fields as binaries

  SET @fieldquery2 = 'SELECT @fields2 = STUFF((SELECT '', GetAs("'' + CONVERT(NVARCHAR(100), FieldName) + ''", DEFAULT) AS "'' + CONVERT(NVARCHAR(100), FieldName) + ''"'' FROM openquery(' + @linkedserver + ', ''SELECT FieldName FROM FileMaker_Fields WHERE TableName=''''' + @tablename + ''''' AND FieldClass=''''Normal'''' AND FieldType = ''''binary'''' AND FieldType NOT LIKE ''''global%'''''') FOR xml path('''')),1,1,'''')'

  --collect container-fields as filepath. Unfortunately it can't handle both, so it's binarie or filepath. Uncomment next line for filepath, comment previous line then

  --SET @fieldquery2 = 'SELECT @fields2 = STUFF((SELECT '', CAST("'' + CONVERT(NVARCHAR(100), FieldName) + ''" AS VARCHAR(1000)) AS "'' + CONVERT(NVARCHAR(100), FieldName) + ''"'' FROM openquery(' + @linkedserver + ', ''SELECT FieldName FROM FileMaker_Fields WHERE TableName=''''' + @tablename + ''''' AND FieldClass=''''Normal'''' AND FieldType = ''''binary'''' AND FieldType NOT LIKE ''''global%'''''') FOR xml path('''')),1,1,'''')'

 

  EXECUTE sp_executesql @fieldquery, N'@fields VARCHAR(5000) out', @fields out

  EXECUTE sp_executesql @fieldquery2, N'@fields2 VARCHAR(5000) out', @fields2 out

 

  --SELECT @fields + isnull(', ' + @fields2,'')

 

  SET @query = REPLACE('SELECT * INTO ' + @tablename + ' FROM openquery(' + @linkedserver + ', ''SELECT ' + @fields + isnull(', ' + @fields2,'') + ' FROM ' + @tablename + ''')', N'SELECT ,' , N'SELECT ')

 

  --SELECT @query

 

  EXEC(@query)  

 

        -- increment counter for next table

        SET @i = @i + 1

    END

2 people found this helpful

Attachments

Outcomes