SMO generate database script using PowerShell, so it was same way as when creating script manually from Management Studio

Question

I need to generate script in code using SMO so it was totally same as if I'm creating it manually using Management Studio. I.e. Database -> Tasks -> Generate scripts.. -> choose Schema and Data.

So as a result I would like to have something like:

USE [master]
GO

CREATE DATABASE [MyDatabase]
CONTAINMENT = NONE
ON PRIMARY 
( NAME = N'MyDatabase', FILENAME = N'C:\Databases\Data\MyDatabase.mdf' , SIZE = 100KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'MyDatabase_log', FILENAME = N'C:\Databases\Data\MyDatabase_log.ldf' , SIZE = 100KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [MyDatabase] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
    EXEC [MyDatabase].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO

ALTER DATABASE [MyDatabase] SET ANSI_NULL_DEFAULT OFF 
...
GO

/****** Object:  DatabaseRole [StoreRW]    Script Date: 22.02.2019 10:14:14 ******/
CREATE ROLE [StoreRW]
GO

...
GO

CREATE TABLE [dbo].[MyTabale1](
...

I.e. the result should contain CREATE DATABASE statement.

But currently I'm not succeeding in this. I've got database schema and data, but not CREATE DATABASE .

I'm using this code:

$My="$ms.Management.Smo" #
$s = new-object ("$My.Server") $DataSource

if ($s.Version -eq $null) { Throw "Can't find the instance $Datasource" }

$ver = $s.Version
echo " SQL Server version is $ver"
$db= $s.Databases[$Database]

if ($db.name -ne $Database) { Throw "Can't find the database '$Database' in $Datasource" };

$transfer = new-object ("$My.Transfer") $db

if ($transfer -eq $null) { Throw "Can't create transfer object" }

$transfer.Options.Default = $true;
$transfer.Options.ScriptBatchTerminator = $true # this only goes to the file
$transfer.Options.ToFileOnly = $true # this only goes to the file

$transfer.Options.ExtendedProperties = $true;
$transfer.Options.IncludeDatabaseContext = $true;
$transfer.Options.WithDependencies = $true;
$transfer.Options.IncludeHeaders = $true;
$transfer.Options.SchemaQualify = $true;
$transfer.Options.DriAll = $true;

$transfer.Options.ScriptData = $true;
$transfer.Options.ScriptSchema = $true;
$transfer.Options.FileName = $BackupFile;
$tmp = $transfer.Options;
echo " Transfer options are: $tmp";
$transfer.EnumScriptTransfer();
echo "Scripting database '$dbName' to '$BackupFile' done"

What do I need to add to get CREATE DATABASE in there too?

Answer 1

This line needs to be added once $db is assigned:

$db.script()

If you want that script data to be output to a file, then just add the following instead:

$db.script() | out-file "c:\folder\create-db.sql"