0

I have a .sql dump file 20 gb and I am trying to run it on Mysql workbench using run script and after successful execution, using SSMA I'll migrate the data from Mysql workbench to SQL Server. I have migrated the data this way many times successfully however for 20 gb file it seems very time-consuming. Please let me know if there is any alternate way to achieve this quickly. I have followed the following link:

Steps to migrate mysql tables to sql server using SSMA!

Thom A
  • 88,727
  • 11
  • 45
  • 75
Wadhawan994
  • 49
  • 1
  • 8
  • 1
    Dumps are *always* time consuming, even when you use them to move data from one MySQL database to another. They aren't backups, they are *scripts* that insert lines one by one. That's the slowest possible way to move data – Panagiotis Kanavos Jul 11 '18 at 10:49
  • @PanagiotisKanavos Could you suggest any alternate way? – Wadhawan994 Jul 11 '18 at 10:52
  • On top of the previous comment, there are significant differences in syntax between MySQL and SQL Server. I'd suggest dividing the migration into structure and data imports. – Dylan Brams Jul 11 '18 at 10:52
  • Additionally, you might try SSIS to connect directly from SQL Server to MySQL – Dylan Brams Jul 11 '18 at 10:56
  • @DylanBrams Thank you for the suggestion. Could you please share some link or resource to achieve this using SSIS and is that paid service? – Wadhawan994 Jul 11 '18 at 11:09
  • https://learn.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-2017 - SSIS is the data import / export / transform tool built into SQL Server. – Dylan Brams Jul 11 '18 at 11:10
  • Fast ways require work. You can use a dump *without* data to create the new database, and then transfer the data from one database to the other. One option is to bulk export the data table-by-table using eg `mysqldbexport` and import them using `bcp`. To do that you'd have to find a format that works for both products. You could also use SSIS to read the data as a flat file if `bcp` can't handle the format generated by `mysqldbexport`. Pros: can be done in steps - export from MyQL import in SQL Server. Cons: Text isn't the best data transfer format, eg for floats – Panagiotis Kanavos Jul 11 '18 at 11:30
  • Another option for data transfer is to use SSIS dataflows, one per table, to read the data from MySQL and insert it into SQL Server tables using firehose operations. Pros: you avoid conversion issues, can transform the data "in flight". Cons: source and target must be available. – Panagiotis Kanavos Jul 11 '18 at 11:33
  • In both cases you'll have to drop or disable all constraints in the target database and re-enable them after loading finishes. – Panagiotis Kanavos Jul 11 '18 at 11:34
  • The process can be automated using the Import Data Wizard in SSMS. It allows you to select multiple source tables, can script the target tables, drop and re-enable constraints etc. Essentially it creates an SSIS package that you can modify yourself using the [standalone SQL Server Data Tools](https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017#ssdt-for-vs-2017-standalone-installer) or Visual Studio – Panagiotis Kanavos Jul 11 '18 at 11:38
  • Variation: Install MySQL "locally" from the dump, then transfer the data. Restoring the dump will *still* take a lot of time but it won't affect any production servers – Panagiotis Kanavos Jul 11 '18 at 11:39
  • @PanagiotisKanavos Thank you for the suggestion. I'll let you know if that works for me. – Wadhawan994 Jul 11 '18 at 11:40
  • I'm responding the error the OP has posted on ht answer, as if I remvoe that answer, traceability will be lost: *"Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'IF'. Msg 102, Level 15, State 1, Line 11 Incorrect syntax near '\`'."* The issue here is the backtick (`\``). I'm not a MySQL user, but (if i recall correctly) the backtick is used by MySQL as an object quote. In SQL server you would use `SELECT * FROM [Table];`, MySQL uses `SELECT * FROM \`Table\`;`. My guess, therefore, is your script has backticks (`\``) instead of brackets (`[]`),which will need fixing. – Thom A Jul 11 '18 at 13:54

1 Answers1

1

From your Title "unable to run .sql file in SSMS" and "I have a .sql dump file 20 gb" are you trying to open a 20GB .sql in SSMS? That's never going to work. SSMS is a 32bit application, so the maximum addressable memory is 2GB. If you want to run your .sql file, I suggest using sqlcmd.

Open up Powershell, and then run the command below replacing the appropriate parts:

sqlcmd -S {Server Name/ServerIP} -U {Your Login} -i {Your full path to your script}

You'll be prompted for your password and then you the file will be run. So, as an example, you might run:

sqlcmd -S svSQL2017 -U Larnu -i \\svFileServer\SQLShare\Scripts\BigBatchFile.sql

If you are using integrated security, then don't pass the -U parameter for the command.

Edit: This answer is no relevant to the OPs question, as they were using "SSMS" as a synonym for SQL Server, which it is not. I have left this here for the moment so the OP can review my comments, and I will likely remove this answer at a later point.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Thank you for the answer that was not helpful. Also, I have tried to keep the title specific and something that most people would be interested to provide resolution. I have put down the detailed content in the body and I believe that makes sense. – Wadhawan994 Jul 11 '18 at 11:13
  • 1
    @Wadhawan994 what wasn't helpful about it? Based on what you have, it would seem relevant. You're never going to be able to open a 20GB file in SSMS. Like I said above, it's a 32bit application; it would be the same as trying to open a 20GB csv file in a 32bit version of Excel; the files are way too big for either application. Hence why you need to use `sqlcmd`. – Thom A Jul 11 '18 at 11:15
  • Sir I know that's a big file to run directly on SSMS and I am pretty sure that I am running 64 bit version. Just for your reference I am sharing the result set: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 17134: ). Also I have tried to work the cmd in Powershell and it didn't work that was not helpful :). – Wadhawan994 Jul 11 '18 at 11:47
  • @Wadhawan994 SSMS and SQL Server are not the same thing. That's where the confusion lies. SSMS is SQL Server Management Studio and is an application for interacting with SQL Server. Just like Internet Explorer is not "The internet", it's a tool to interact with it. SSMS is only available as a 32bit application where as SQL Server (Data Engine) is available in 32bit and 64 (apart from 2017,which is 64 bit only). If you're referring to SQL Server, make sure that's what you mention. Stating SSMS when you mean the data engine is confusing, and will mean you get answers based on the wrong info. – Thom A Jul 11 '18 at 11:58
  • Thank you for providing the information. I was aware that SSMS is Sql server management studio :P and SQL server and SSMS are two different things. What I was not aware of was that SSMS is 32 bit and SQL Server (Data Enigne) is 64 bit. However, let's not take the discussion in a different direction. On executing the command I got the error Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'IF'. Msg 102, Level 15, State 1, Line 11 Incorrect syntax near '`'. – Wadhawan994 Jul 11 '18 at 12:28
  • I hope you got my question correctly. If you want I can still brief it for you. – Wadhawan994 Jul 11 '18 at 12:30