Возможно ли создать базу данных на SQL Server с помощью powershell?

Ответ 1

SMO, безусловно, поддерживает метод создания - связанная страница MSDN включает пример powershell:

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($srv, "Test_SMO_Database")
$db.Create()
Write-Host $db.CreateDate

Ответ 2

В моем go.bat script, который устанавливает среду для службы, я проверяю, существует ли db, а если нет, я создаю один.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") .

$dbExists = $FALSE
foreach ($db in $server.databases) {
  if ($db.name -eq "Db") {
    Write-Host "Db already exists."
    $dbExists = $TRUE
  }
}

if ($dbExists -eq $FALSE) {
  $db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -argumentlist $server, "Db"
  $db.Create()

  $user = "NT AUTHORITY\NETWORK SERVICE"
  $usr = New-Object -TypeName Microsoft.SqlServer.Management.Smo.User -argumentlist $db, $user
  $usr.Login = $user
  $usr.Create()

  $role = $db.Roles["db_datareader"]
  $role.AddMember($user)
}

Ответ 3

узнал, как это сделать здесь http://msdn.microsoft.com/en-us/library/ms162577.aspx

Мой код:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$serverName = "localhost"

$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $serverName

$server.ConnectionContext.LoginSecure=$false;
$credential = Get-Credential
$loginName = $credential.UserName -replace("\\","")
$server.ConnectionContext.set_Login($loginName);
$server.ConnectionContext.set_SecurePassword($credential.Password)
$server.ConnectionContext.ApplicationName="SQLDeploymentScript"

#Create a new database
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -argumentlist $server, "Test_SMO_Database"
$db.Create()

#Reference the database and display the date when it was created. 
$db = $server.Databases["Test_SMO_Database"]
$db.CreateDate

Ответ 4

Слишком много хлопот выше, если вы спросите меня, это прекрасно работает:

$s = New-PSSession -ComputerName SQLDB -Credential DOMAIN\USER

Invoke-Command -Session $s -ScriptBlock {Invoke-Sqlcmd -ServerInstance SQLDB\INSTANCE -InputFile C:\Users\USER\Downloads\CREATEDB.sql}

Только то, что вам нужно, чтобы ваша DB script уже сформировалась и вызывается из указанного местоположения. Это проще для меня, хотя из-за этого это небольшая часть более крупного script - я не хочу раздувать мой script, если мне не нужно и может вызывать SQL-скрипты из репо.

Ответ 5

Этот script реплицирует полное создание базы данных T-SQL по умолчанию в SQL 2016:

$Server=[Microsoft.SqlServer.Management.Smo.Server]::new()
$DB=[Microsoft.SqlServer.Management.Smo.Database]::new($Server,"TemporaryDB")
# CONTAINMENT = NONE
$DB.ContainmentType=[Microsoft.SqlServer.Management.Smo.ContainmentType]::None
#ALTER DATABASE [TemporaryDB] SET COMPATIBILITY_LEVEL = 130
$DB.CompatibilityLevel=130
#ALTER DATABASE [TemporaryDB] SET ANSI_NULL_DEFAULT OFF 
$DB.AnsiNullDefault=$false
#ALTER DATABASE [TemporaryDB] SET ANSI_NULLS OFF 
$DB.AnsiNullsEnabled=$false
#ALTER DATABASE [TemporaryDB] SET ANSI_PADDING OFF 
$DB.AnsiPaddingEnabled=$false
#ALTER DATABASE [TemporaryDB] SET ANSI_WARNINGS OFF 
$DB.AnsiWarningsEnabled=$false
#ALTER DATABASE [TemporaryDB] SET ARITHABORT OFF 
$DB.ArithmeticAbortEnabled=$false
#ALTER DATABASE [TemporaryDB] SET AUTO_CLOSE OFF 
$DB.AutoClose=$false
#ALTER DATABASE [TemporaryDB] SET AUTO_SHRINK OFF 
$DB.AutoShrink=$false
#ALTER DATABASE [TemporaryDB] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
$DB.AutoCreateStatisticsEnabled=$true
$DB.AutoCreateIncrementalStatisticsEnabled=$false
#ALTER DATABASE [TemporaryDB] SET AUTO_UPDATE_STATISTICS ON 
$DB.AutoUpdateStatisticsEnabled=$false
#ALTER DATABASE [TemporaryDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
$DB.CloseCursorsOnCommitEnabled=$false
#ALTER DATABASE [TemporaryDB] SET CURSOR_DEFAULT  GLOBAL 
$DB.LocalCursorsDefault=$false
#ALTER DATABASE [TemporaryDB] SET CONCAT_NULL_YIELDS_NULL OFF 
$DB.ConcatenateNullYieldsNull=$false
#ALTER DATABASE [TemporaryDB] SET NUMERIC_ROUNDABORT OFF 
$DB.NumericRoundAbortEnabled=$false
#ALTER DATABASE [TemporaryDB] SET QUOTED_IDENTIFIER OFF 
$DB.QuotedIdentifiersEnabled=$false
#ALTER DATABASE [TemporaryDB] SET RECURSIVE_TRIGGERS OFF 
$DB.RecursiveTriggersEnabled=$false
#ALTER DATABASE [TemporaryDB] SET  DISABLE_BROKER 
$DB.BrokerEnabled=$false
#ALTER DATABASE [TemporaryDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
$DB.AutoUpdateStatisticsAsync=$false
#ALTER DATABASE [TemporaryDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
$DB.DateCorrelationOptimization=$false
#ALTER DATABASE [TemporaryDB] SET PARAMETERIZATION SIMPLE 
$DB.IsParameterizationForced=$false
#ALTER DATABASE [TemporaryDB] SET READ_COMMITTED_SNAPSHOT OFF 
$DB.IsReadCommittedSnapshotOn=$false
#ALTER DATABASE [TemporaryDB] SET  READ_WRITE 
$DB.ReadOnly=$false
#ALTER DATABASE [TemporaryDB] SET RECOVERY FULL 
$DB.RecoveryModel=[Microsoft.SqlServer.Management.Smo.RecoveryModel]::Full
#ALTER DATABASE [TemporaryDB] SET  MULTI_USER 
$DB.UserAccess=[Microsoft.SqlServer.Management.Smo.DatabaseUserAccess]::Multiple
#ALTER DATABASE [TemporaryDB] SET PAGE_VERIFY CHECKSUM  
$DB.PageVerify=[Microsoft.SqlServer.Management.Smo.PageVerify]::Checksum
#ALTER DATABASE [TemporaryDB] SET TARGET_RECOVERY_TIME = 60 SECONDS 
$DB.TargetRecoveryTime=60
#ALTER DATABASE [TemporaryDB] SET DELAYED_DURABILITY = DISABLED 
$DB.DelayedDurability=[Microsoft.SqlServer.Management.Smo.DelayedDurability]::Disabled
#ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
$DB.MaxDop=0
#ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
$DB.MaxDopForSecondary=[Microsoft.SqlServer.Management.Smo.DatabaseScopedConfigurationOnOff]::Primary
#ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
$DB.LegacyCardinalityEstimation=[Microsoft.SqlServer.Management.Smo.DatabaseScopedConfigurationOnOff]::Off
#ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
$DB.LegacyCardinalityEstimationForSecondary=[Microsoft.SqlServer.Management.Smo.DatabaseScopedConfigurationOnOff]::Primary
#ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
$DB.ParameterSniffing=[Microsoft.SqlServer.Management.Smo.DatabaseScopedConfigurationOnOff]::On
#ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
$DB.ParameterSniffingForSecondary=[Microsoft.SqlServer.Management.Smo.DatabaseScopedConfigurationOnOff]::Primary
#ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
$DB.QueryOptimizerHotfixes=[Microsoft.SqlServer.Management.Smo.DatabaseScopedConfigurationOnOff]::Off
#ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
$DB.QueryOptimizerHotfixesForSecondary=[Microsoft.SqlServer.Management.Smo.DatabaseScopedConfigurationOnOff]::Primary

# ON  PRIMARY ( NAME = N'TemporaryDB', FILENAME = N'TemporaryDB.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
$FileGroup=[Microsoft.SqlServer.Management.Smo.FileGroup]::new($DB,"PRIMARY")
$DB.FileGroups.Add($FileGroup)

$DataFile=[Microsoft.SqlServer.Management.Smo.DataFile]::new($FileGroup,"TemporaryDB")
$FileGroup.Files.Add($DataFile)

$DataFile.FileName="TemporaryDB.mdf"
$DataFile.Size=8192
$DataFile.Growth=65536
$DataFile.GrowthType=[Microsoft.SqlServer.Management.Smo.FileGrowthType]::KB
$DataFile.IsPrimaryFile=$true

# LOG ON ( NAME = N'TemporaryDB_log', FILENAME = N'TemporaryDB_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
$LogFile=[Microsoft.SqlServer.Management.Smo.LogFile]::new($DB,"TemporaryDB_log","TemporaryDB_log.ldf")
$LogFile.Size=8192
$LogFile.Growth=65536
$LogFile.GrowthType=[Microsoft.SqlServer.Management.Smo.FileGrowthType]::KB
$DB.LogFiles.Add($LogFile)
#CREATE DATABASE [TemporaryDB]
$DB.Create()
#IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [TemporaryDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
#Not necessary