We are moving a local SQL Server Express database into Azure.
The database has been exported via:
SQL Server Management Studio ->
Right Click Database ->
Generate Scripts ->
Select Specific Database Objects ->
Tables ->
All Tables ->
Save as Script File ->
Advanced ->
SCHEMA and DATA ->
GO
After importing the data, All tables are created, and about half of the data is imported successfully before the script reports:
Query completed with errors
The error being reported is:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing 1.
I have been searching Azure and locally for an error log or anything with the ability to troubleshoot this issue to determine which specific query/import is failing and I can't find it.
I imagine there is a quote character or an encoding issue that is breaking the import, and I'd like to avoid exporting and importing the 100+ table database row by row to find the issue.
PS. I've tried querying the sys.event_log for information and it reports:
Reference to database and/or server name in 'sys.event_log' is not supported in this version of SQL Server
Using SQL Server Management Studio to migrate a SQL Server database (on a local computer) to Azure SQL most of the times ends in frustration. My suggestion for you is to use Azure Data Migration Assistant (DMA) to make that migration easy.
First use DMA to perform an assessment just to make sure your SQL Server database does not have any incompatibilities with Azure SQL. If the results of the assessment do not show any blocking point then proceed to migrate your database.