![]() $db = New-Object .Database($srv, $dbname) $srv = New-Object .Server("YourInstance") Invoke-Sqlcmd -Query "CREATE DATABASE YourDB" -ServerInstance YourInstanceĪlternatively, using SQL Server Management Objects (SMO) objects to do the heavy lifting: #Name your database Run straight SQL against your instance to create the database: # This query could also come from a file Creating a new blank database is accomplished with either of the following commands: Surprisingly, Microsoft does not provide a cmdlet out of the box to create a database, so there are two main routes you can follow to get a database up and running without a prior backup. Create SQL Server database and assign an owner -GrantConnectSql to allow the login to connect to the database engine.įor more information, see the Add-SqlLogin reference.-Enable to ensure the login is available later in your deployment.If you are using this command within an Octopus deployment process, you may find it useful to include the following flags: This cmdlet greatly simplifies the creation of a SQL Server login along with the flexibility of using built-in flags such as -MustChangePasswordAtNextLogin or -ConnectionTimeout. # Create login using the Add-SqlLogin cmdletĪdd-SqlLogin -ServerInstance YourInstance -LoginPSCredential $loginCred -LoginType SqlLogin $loginCred = New-Object ("NewUser",$pass) $pass = ConvertTo-SecureString "Th!sI5Y0urP4ss" -AsPlainText -Force A cmdlet from the SQL Server module will do the trick: # To run in a non-interactive mode, such as through an Octopus deployment, you will most likely need to pass the new login credentials as a PSCredential object. ![]() Create SQL Server loginįor proper segregation of permissions within your server or instance, you may want to create a new login for your application or associated service. With the connection tested at the beginning of your deployment, you can move forward knowing you can make the calls you need against your SQL Server. # Notify there was an error connecting to the database # Notify connection was not in the "open" state $connString = "Data Source=YourInstance Database=YourDB User ID=YourUser Password=YourPassword" # Feel free to substitute "Integrated Security=True" for system logins. # This is a simple user/pass connection string. One of the simplest ways to test a connection to a SQL Server database is to use a connection string and the basic SqlClient class: try If you are using a PowerShell version earlier than 5.0, use these commands: Uninstall-Module -Name SqlServerįor more information on installing the SqlServer module, please see this Microsoft doc: Install SQL Server PowerShell module. The SqlServer module can be installed from the PowerShell Gallery using the following command: Install-Module -Name SqlServerĪdditionally, if this module is already installed, you can update it using the following command if you are using PowerShell 5.0 or later: Update-Module -Name SqlServer This isn’t used in all of the following examples, but it does contain many useful cmdlets for SQL Server administration. Microsoft recommends using the SqlServer module for interacting with SQL Server from PowerShell. ![]() Installing the SQL Server PowerShell module Create SQL Server database and assign an owner.Installing the SQL Server PowerShell module.
0 Comments
Leave a Reply. |