How can I copy a database schema from my local Sql Server to Azure SQL Database

Question

I figured the way to do this was to go to my existing database (Sql Server 16.0.1105.1 developer edition) in SSMS and run Tasks | Generate Scripts...

This includes a FullTextTable. So I then got the following script (just showing the start):

USE [master]
GO
/****** Object:  Database [LouisHowe]    Script Date: 11/15/2023 5:56:31 PM ******/
CREATE DATABASE [LouisHowe]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'LouisHowe', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\LouisHowe.mdf' , SIZE = 73728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'LouisHowe_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\LouisHowe_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO
ALTER DATABASE [LouisHowe] SET COMPATIBILITY_LEVEL = 160
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [LouisHowe].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [LouisHowe] SET ANSI_NULL_DEFAULT OFF 

And it seems to have created the database ok. But I got close to 200 error messages (showing just the first few):

Msg 40508, Level 16, State 1, Line 1
USE statement is not supported to switch between databases. Use a new connection to connect to a different database.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'ON'.
Msg 319, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 5011, Level 14, State 5, Line 12
User does not have permission to alter database 'LouisHowe', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 12
ALTER DATABASE statement failed.

What do I need to do to fix the script?

Answer 1

As mustaccio mentioned in the comments, it would be easier to just use the tools that Microsoft provides.

The Azure Migrate tool, which can be added from the marketplace, has been getting better and better and makes copying your data to the cloud quite easy, this is quite a big tool and is perhaps a bit much for your needs.

A different but very easy to use tool is the Azure Data Migration Assistant. This tool can do an assessment, which checks if you have features on premise that you cannot migrate to your cloud target (Or any other target/version) afterwards it can also do the final migration for you. For an Azure SQL Database it will basically do what you're trying to do, create a script and run it on the target.

There are more tools/options to migrate to the cloud as well if you want to find more options.