Voice of the DBA

Tag: SQLNewBlogger

Delete an Azure SQL Database from PowerShell
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the things I’ve been working on is trying to get my DevOps, continuous delivery pipeline working with Azure. Part of that is a test deployment to an Azure SQL Database, which means I need to be able to update an existing database from a backup. Unfortunately, Azure SQL Database doesn’t support a restore over an existing database (yet).

That means one task I have is to remove an existing database, in order to replace it with a new database with the same name. A little work in the PoSh documentation found Remove-AzureRMSQLDatabase, which is just what I need.

To use this cmdlet, I need a connection to the Azure space first. I can do that with a credential that I get with this command. Ultimately I need to store this, but interactively this lets me get started:
```
Login-AzureRMAccount
```
This gets me an interactive login. I enter my account and password. Since I use a Live account, this won’t work in the pipeline, but it gets me going.

From here, I can set a few variables I’ll need. I want the name of a resource group, a server, and a database. In my case, I’ll use a few variables. I call the database the “new” one, since I’ll be using an existing one that I’ll recreate from an “old” one.

From here, it’s just a question of calling the Remove-AzureRmSqlDatabase cmdlet with parameters. I do that, and get results. Here’s the call
```
Remove-AzureRmSqlDatabase -ServerName $server -ResourceGroupName $rgname -DatabaseName $newname
```
Here are the results. I’ve blacked out a few ids.

This clears the database, and after refreshing, I can see it’s gone from my list of Azure SQL Databases.

Not much to this, but it’s part of a larger scheme, which is getting a copy of the production database and restoring it.
March 22, 2017
Create a BACPAC–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the things that you might encounter at some point is the need to create a BACPAC. This is essentially a DACPAC with data included. There are times you might find a BACPAC more convenient than a full backup, especially if you work with Azure.

This post shows how you can create a BACPPAC file (with the extension .bacpac) from SSMS.

I have a small database, the PartsUnlimited database, from the Microsoft PartsUnlimited demo application. I want to create a BACPAC from this, however, when I right click and select Tasks, there is no “Create BACPAC” option.

That’s fine, because the “Data Tier Application” is the DAC, and an export includes the data, which creates the BAC part of the process. Let’s choose “Export Data-tier Application”. Once we do that, we get the expected summary wizard screen to start.

I click next and immediately get prompted for a location in which to save the file. The will include all your data, so choose a location that has sufficient disk space. You have the option to also store this in Azure if needed.

Note the filename above is already filled out by default, but you can change this in the edit box. If you click the “Advanced” tab, you’ll get the chance to select only certain tables if that is required. You can see this dialog below.

Once you’ve chosen a location, you click next and get a summary page.

Click finish, and the process starts. This runs very quickly, extracting the schema and then all the data, noting the results of each object.

That’s it. If I look in the location, I’ll see my BACPAC file.

I can copy this to another machine and import it to recreate a database. We’ll do that in another post.

SQLNewBlogger

A short, quick post. I’ve done this before, but I had to do this for a quick process and took 5 extra minutes to take screen shots, spending 5 minutes later writing this up.

March 15, 2017
Create a DACPAC to Move Databases–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In my last post, I talked about what a DACPAC was. Now let’s create one. This turns out to be about as simple as it could be. First, let’s choose a database. In my case, I’ll use the PartsUnlimited database on my SQL Server 2016 instance. I’ll start by right clicking the database and selecting “Tasks”.

Down near the bottom there is an “Extract Data-tier Application” entry. Choose that. Once you do, you should get a wizard screen. We’ll click past the first screen.

Next we need to set the properties of the DACPAC we are building. The application name is usually the database. The version can be anything, but ideally you are versioning your database in some way. Most people don’t, so they can leave this as 1.0.0.0. If you are actually building your database somehow, you ought to be using a CI process and have some version number. Ideally you’d keep this somewhere in the db and rev it.

We also need to include an optional description and a path to the file. I’ll leave the defaults, but feel free to change these as needed.

The next screen is a summary. If everything looks OK, click Next. This will start the creation of the .DACPAC.

The final screen will show progress, which is fairly short and simple. I think this has always worked for me. If I click the “Finish” button at the bottom (not shown), the dialog disappears.

If I go to the file location, I’ll see my DACPAC in the filesystem. The file is recognized as an SSMS file, and in another post we’ll look at how we unpack this.

I can check this file, however, to be sure there is something inside it. This is a zip file, and if I open it in 7-Zip, I see this:

Opening the model.xml file, I see data that seems like it describes my database. I talked about this in my previous post.

There, a simple DACPAC. This is a format Microsoft uses, and while it’s not perfect, at least I now know how to build one.

SQLNewBlogger

This post really took me about 10 minutes, across a few days. I started it after building a DACPAC, and taking the screenshots, but I didn’t have time to write it. I finally spent the other 6 or so minutes putting these words down the next week.

A quick showcase of something I learned.

March 8, 2017
Implicit Time Conversions – #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was trying to work with times recently and needed to get the current time. I thought, well, Getdate(), or better yet, SysDateTime() will give me a date and time, but what about the time?

A simple experiment showed it’s easy:
```
DECLARE
    @t TIME,
    @t1 TIME;
SELECT @t = SYSDATETIME(), @t1 = GETDATE();
SELECT @t, @t1;
```
I got this:

Quick, easy, and what I suspected would work. If you need to work with times, you can easily cast a datetime value to a TIME to strip the date, or just assign the values to a time.
```
CREATE TABLE TimeTest
(t TIME)
GO
INSERT TimeTest
SELECT top 10
 CreationDate
 FROM dbo.Posts
 GO
 SELECT top 10
  *
  FROM dbo.TimeTest
GO
DROP TABLE TimeTest
```
This code takes a datetime column and just inserts the time into the new table.

SQLNewBlogger

Literally about 3 minutes of my day to write this. When you learn something, write it down.
March 2, 2017