How do I move from running FogBugz on MySQL to SQL Server?

2009-10-08T20:35:28Z

I'm running on MySQL now, but would like to run FogBugz on SQL Server 2008. How do I convert?

Answer by Rich Armstrong for How do I move from running FogBugz on MySQL to SQL Server?

2009-10-08T20:36:39Z

We use ESF Database Convert to convert MySQL databases into SQL Server databases for loading into FogBugz On Demand. You should be able to do so for your local install, too.

Answer by adambox for How do I move from running FogBugz on MySQL to SQL Server?

2010-01-21T19:59:36Z

Contact Fog Creek Support

The instructions below are a bit outdated. Please contact our support team at customer-service@fogcreek.com and we'd be happy to provide more detailed instructions for converting your database.

NOTE: Previous version of this post included detailed instructions, but they've been removed since they were outdated and didn't always work. Please contact us and we'll work with you to provide more complete instructions.

Answer by scoarescoare for How do I move from running FogBugz on MySQL to SQL Server?

2010-11-19T00:30:31Z

We had the following configuration:

MySQL
IIS
LDAP Authentication (Active Directory)

Here's how we just did it:

Run the FogBugz setup and create a brand new database
Use Microsoft SQL Server Migration Assistant 2005 for MySQL or create a sql dump to get the DDL of your plugin tables.
Download the MySQL ODBC and install it on the new SQL Server.
Create a linked server to the MySQL database. http://www.packtpub.com/article/mysql-linked-server-on-sql-server-2008
Run create and run these stored procs:

‌

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[CopyFogBugzData]    
 @TABLE NVARCHAR(MAX)    
AS    
BEGIN    
DECLARE @sqlCommand nvarchar(max)    
DECLARE @columnList nvarchar(max) 

print @Table 

IF (select count(*) from openquery(fogbugz,'SHOW TABLES FROM `fogbugz`')where tables_in_fogbugz = @Table) < 1
BEGIN
PRINT @Table + ' does not exist in source db';
RETURN -1
END

SET @ColumnList = REPLACE(     
        (     
            SELECT     
                Column_Name AS [data()]     
            FROM     
                information_schema.columns    
    WHERE table_name = @table    
            FOR XML PATH ('')     
        ), ' ', ',')     

SET @sqlCommand = N'TRUNCATE TABLE ' + @Table + ' ';    
if OBJECTPROPERTY(object_id(@table), 'TableHasIdentity') = 1    
BEGIN    
SET @sqlCommand = @sqlCommand + N'SET IDENTITY_INSERT ' + @Table + N' on; ' --id and version table is not auto identity    
END    

SET @sqlCommand = @sqlcommand +  N' INSERT INTO ' + @Table + N' (' + @columnList + N') '    

SET @sqlCommand = @sqlcommand +  N' SELECT ' + @columnList + N' FROM openquery(fogbugz,''select * from ' + @Table + N''');'    
if OBJECTPROPERTY(object_id(@table), 'TableHasIdentity') = 1    
BEGIN    
SET @sqlCommand = @sqlcommand +  N' SET IDENTITY_INSERT ' + @Table + N' off; '    
END    

print @sqlcommand;    
EXECUTE (@sqlcommand)    

END  

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CopyALLFogBugzData]
AS

BEGIN

exec sp_MSforeachtable 'DECLARE @Table NVARCHAR(255); Set @Table = (SUBSTRING(''?'',8, LEN(''?'') - 8)); exec CopyFogBugzData @Table'
END
GO

These procs will iterate over every table in your new fogbugz database. If this table exists in the source db in MySQL, it'll truncate the destination table and insert the data.

How do I move from running FogBugz on MySQL to SQL Server? - FogBugz Knowledge Exchange

How do I move from running FogBugz on MySQL to SQL Server?

Answer by Rich Armstrong for How do I move from running FogBugz on MySQL to SQL Server?

Answer by adambox for How do I move from running FogBugz on MySQL to SQL Server?

Contact Fog Creek Support

Answer by scoarescoare for How do I move from running FogBugz on MySQL to SQL Server?