0

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...

enter image description here

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?

5
  • 1
    What do I need to do to fix the script? -- Remove or modify all statements that are not supported Azure SQL Database. Better yet, use the tool Microsoft provides for your use case.
    – mustaccio
    Commented Nov 16, 2023 at 2:29
  • @mustaccio Is there no simple way to get a generic works on any SQL Server script? I like the idea of having a script as a backup. TIA Commented Nov 16, 2023 at 3:43
  • @DavidThielen "Is there no simple way to get a generic works on any SQL Server script?" - Azure SQL Database is a completely different database than SQL Server. Not all features that SQL Server has are available in other database systems, such as Azure SQL Database. It's not SQL Server's job to keep track of which of its own features are not available in other database systems. So it's not possible to do what you want via the Generate Scripts methodology in SSMS, which is just using SQL Server's system objects to get the scripts from.
    – J.D.
    Commented Nov 16, 2023 at 11:17
  • @J.D. Ok, thanks. What would be optimum is if Entity Framework would generate scripts for a specified database. Unfortunately it doesn't. Commented Nov 16, 2023 at 17:24
  • @DavidThielen Best of luck!
    – J.D.
    Commented Nov 16, 2023 at 19:29

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.