Voice of the DBA

Category: Blog

Creating a Books Online Pull Request

One of the neat things at Microsoft did last year was put Books Online in GitHub. This is in the MicrosoftDocs org under the sql-docs repo. The organization is a bit funny, but once you get used to it, you can find the docs.

One of the neat things that is available now is that anyone can edit Books Online. You can’t edit the live versions in GitHub, nor the versions published on Microsoft’s site (or downloaded), but you can submit your changes as a pull request, which Microsoft will review the changes, and if they like them, the changes can get added to the official docs.

This post shows how you can do this.

Requirements

First, you need a Github account. This is free, and you will end up with a fork (copy) of the official repo in your account when you edit.

Second, you need to learn a little markdown. Not much, but enough to make formatted changes. I like this cheatsheet, but there are plenty out there (kidding).

Editing BOL

When you go to a page in the online BOL, you should notice a short menu in the upper right. It has Feedback and Share links, but also an “Edit” link. You can see this in the image below.

If you click Edit, you’ll be taken to the page in Github. Below, I have the Backup page shown. Note the path near the top (by Branch:live) of sql-docs/docs/t-sql/statements/backup-transact-sql.md. This is the file in the repo. You’ll need this.

This is the real repo from Microsoft, and you won’t be able to edit it. If you click edit (a pencil icon on the right side, just above the doc and to the right of the Raw|Blame|History buttons, you’ll get this:

That’s fine. You can edit the page and then save this in your copy of the repo. In this case, I actually wanted to edit this page to add the NUL device. I scrolled down and found the area I wanted to edit. For ease of viewing, I’ve highlighted the place I edited the file.

Commit

All of you reading this should be familiar with version control. If you’re not, learn that.

To commit my change, I scroll to the bottom of the file and I’ll see the propose file change dialog. I can enter a title (meaningful for the change) and a comment that will let someone know what I’ve done.

Once I do this, a new branch is created in my repo. If I look at the result page, I’ll see this:

Reading this, I see that in my fork (way0utwest/sql-docs), I have a new branch (patch-4) that was created. Below this are the details of the commit, but essentially I have a copy of the official Microsoft repo in my area, with one new commit.

The Pull Request

A pull request (PR) is a notification to a repo that there are changes that someone wants to merge into that repo. In this case, a pull request will let MS know that my change is ready to merge, and they can review it.

To start a PR, click the green button in the image above. By default, I’ll get the same commit comments I had listed above. In this case, I see this:

Note, there are a couple important things here. First, this change is “able to merge”. We see that near the top. If you’ve made substantial changes that don’t necessarily merge cleanly, or you have an old copy of the repo, this won’t work. Go back and create a PR to update your repo from MS (you can approve this) and then make your changes.

Second, make sure that your comments make sense to the person that will review this. In my case, I wanted to have more complete docs with NUL as an option (since it is) and I noted this is a target. I could expound why here or add more details. I try to ensure each change I’ve made has a sentence, so if I added a note to a different place in the docs, I’d have a second sentence here.

Click “Create pull request” to complete this. Someone at MS will be notified, and you’ll get an email. In my case, I got this:

Waiting

The next step is to wait. I’ve submitted four or five minor PRs that helped complete or clarify docs. In some cases I got a note from the repo maintainer the same day, some took 3 or 4 days.

In any case, you may get a message that your change is accepted and it’s been merged. In this case, you’re done and I thank you for improving BOL.

You also may get a message back in your PR that there are other changes that need to be made, or on rare occasions, an conflict has occurred. In that case, you may need to edit your change, make a new commit, and a new PR.

This is a great model that Microsoft has given us to allow the community to issue corrections. Prior to this all feedback would get emailed to someone at MS, without any real organization. Or MVPs would send in feedback, but someone would need to interpret that and make a change. Now the community can easily make a change and help improve and correct our docs.

January 3, 2018
Starting from Scratch

As the new year starts today, I find myself looking at my blog and seeing no posts in queue. My last editorial published today and no blogs scheduled for this week. In other words, the same state that many blogs are in.

I’m a big fan of writing regularly, and if inspiration strikes and lets you get more than one post done, schedule it out. Usually I’ve got 5-10 posts scheduled at any one time, but not today. With two weeks of travel and then holidays to close out 2017, I didn’t do much writing.

So today the focus is getting back to work, while also keeping an eye out for those things to write about. As I work with T-SQL, I’ll make notes of things that are interesting, or that I learn, and start adding blog posts.

I don’t have a goal for 2018, but I did end up with 219 blogs (excluding editorials) with an average of 18 posts a month. We’ll see how I do this year.

January 2, 2018
Speaking in 2017
I’m done. No more trips (and no more time) to hit any events this year. As I wrote recently, I’m doing less in 2018, so this review might be quite short next year.

In any case, I thought I’d look back at how speaking went in 2017. I have a speaking CV where I track all my events, so I can get the numbers easily from there. By the numbers, 2017 was:
- 18 total events
- 34 total talks
- 15 unique talks
- 5 SQL Saturdays (not a lot)
- 5 virtual events (3 from Redgate)
- 4 new events for me
- 4 countries in which I delivered talks (1 new)
Overall a long year for me. Those 18 events resulted in a lot of trips and about 80 nights in hotels. I had some work engagements as well, but most trips involved speaking somewhere.

I am saddened that I didn’t present at any user groups this year, which is something that hasn’t happened in awhile. Hopefully I’ll do better next year at getting to a few user groups.

One thing here is that I discounted some of the Redgate DevOps webinars that we do monthly. I probably did 5 or 6 of those, but I’m not really presenting in most of these, mostly hosting, so I left those out.

Oh, and 1 Habitat for Humanity talk to add to this list
December 28, 2017
Restoring a Copy Only Backup–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

There was a question posted recently at SQLServerCentral about whether a copy only backup could be restore with a transaction log backup from a database. I was positive this could, but decided I needed to repro and test for someone as there wasn’t a good BOL reference.

The Tests

Here’s what I did. First, I created a table in a database. I often do this and drop in messages to allow me to track the progress of backups and restores. This post follows my progress.

The Backups

Here’s my basic script:
```
CREATE TABLE logger(msg VARCHAR(200), msgdate DATETIME DEFAULT GETDATE())

INSERT logger (msg) SELECT 'pre full backup'
```
Next, I made a backup and added a message.
```
INSERT logger (msg) SELECT 'pre-log backup 1'
BACKUP LOG nba TO disk = 'nba_1.trn'
INSERT logger (msg) SELECT 'log backup 1 complete'
```
Once this is done, I’m in a state that I expect. A normal full backup, a normal log backup, and some data to help me track where I am.

Now let’s make a copy only backup.
```
INSERT logger (msg) SELECT 'pre copy-only backup '
BACKUP DATABASE nba TO DISK  = 'nba_copy.bak' WITH COPY_ONLY
INSERT logger (msg) SELECT 'copy-only backup complete'
```
This now means I have an open log sequence in the first log backup (post full backup) and a few log records since then. Some of these are inside the copy only backup.

Now let’s add more data and make a new, regular, log backup.
```
INSERT logger (msg) SELECT 'pre-log backup 2'
BACKUP LOG nba TO disk = 'nba_2.trn'
INSERT logger (msg) SELECT 'log backup 2 complete'
```
It’s at this point that I have this sequence:
- Full backup
- Log backup
- Copy-Only Full backup
- Log backup
The Restore

What I want to test is can I restore the Copy-Only backup and a log backup? I think I can, so let’s do that. First, restore from the copy-only backup, using the MOVE option.
```
USE [master]
RESTORE DATABASE [NBA2] 
FROM  DISK = N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup\nba_copy.bak' 
WITH  FILE = 1,  
      MOVE N'NBA' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2.mdf',  
      MOVE N'NBA_log' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_log.ldf',  
      MOVE N'nba_mo_file1' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_mo',  
      MOVE N'nba_mo_file2' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_mo2'
,  NOUNLOAD,  STATS = 5
, NORECOVERY
```
Tip: Always use NORECOVERY

Now let’s try to restore the log.
```
RESTORE LOG NBA2 FROM DISK = 'nba_2.trn' WITH NORECOVERY

RESTORE DATABASE nba2 WITH RECOVERY
```
This works:

That should prove things. Let’s check the logger table.

That’s what we expect. The final message after log backup2 wasn’t captured in our backup files.

Copy Only Backups

What is a copy only backup? If we check the Copy-Only Backups page, we find that this is a regular backup in and of-itself, but it has the restriction that it cannot be used with differential backups. This also doesn’t change the differential bitmap, so that any differentials that are made ignore this backup and go back to include data changed since the last “normal” full backup.

SQLNewBlogger

Understanding backup and recovery is critical for a data professional. I’d say this is the most important skill, and it’s always worth writing about. Spend a few minutes reviewing scenarios and creating some posts like this to show you understand how the system works.
December 27, 2017