From the course: PowerShell for SQL Server Administration

Creating databases - PowerShell Tutorial

From the course: PowerShell for SQL Server Administration

Start my 1-month free trial

Creating databases

“

- [Instructor] Yeah, so now we're covered some bases of PowerShell. Let's go ahead and start with bases of SQL server. So first thing we need to do, create a database. So when we create a database in SQL server, we have a couple of different options. So we can go with SQLPS, and we can use the New-Object commandlet to create a database, or we can use our native T-SQL and just use create database. So let's go ahead and take a look at those two options so you'll see what I'm talking about. So in this first option, if we use SQLPS, we can use the native various objects in PowerShell to create our new data base. So, I've already loaded up SQLPS, so the first thing we're going to do here is navigate to \sql\localhost. So you can use localhost or you can specify the name of your computer, I'm using localhost. And then you can get the list of databases then go ahead and create a new database from that list. Creating our database. We're going to give that database a name. That name is going to be Test3 in this case. Then we're going to create the database, and then we can go ahead and drop the database from the server. So if we go ahead and run this, we're going to go ahead and create and drop our database from the server. We can see here, it's outputting just the current date and time, which just is the default for these commandlets. So it's a nice, simple, easy way to create a database if you want to use SQLPS. Now the other option, if we look at this other tab, is using the normal create database statement. So here we've got the same SQL authentication, using the password that we saw earlier in another demo. So it's just sa, and password's welcome123345, server name localhost, database is master, the connection strings are set up pretty much like normal, where it's passing in the user name password, we're turning off encryption, because we don't have it turned on, we do not have TrustServerCertificate enabled because we don't need it, and we've got our connection, now we're all set. So here we're creating our connection string, we then create our connection object, which is going to be a New-Object type. We set our connection string for our SQL object to the connection string value, which is just a string. We then open the object, we then create our command, the command text is going to be Create Database, we then executed that command, we see if there's any rows, which there's not. Now you don't have to do this line, on Line 18. I'm just doing it just so we can have some sort of output. We then dispose and close our connection objects and command objects. So if we run that, we'll see here, we get back false, because there are no rows, because that's this line right here that output, giving us our output, and we've now created our database called, again, Test3, up on our SQL server, and we're ready for it to actually do some work and create tables and objects inside it.

Contents