FM to MSSQL
Backup FileMaker database to MS-SQL-server using linked server object
Community thread here: community
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.
-- 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